Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.co...r.blogspot.com/|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source system
s.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story o
f
lots of Access and Excel apps extracting and manipulating data for reporting
.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

Correcting old data

Hi,
What's the best way to handle data reloads when you have implemented Type 2
slowly changing dimension?
For example, you might have an Account dimension, with a status of open or
closed. Someone accidentally closes the account, which we then extract and
update, creating a new record. They then realise the mistake and re-open the
account. The next extract would be create a third record, with the original
value.
On top of this, fact data may have been loaded against the second (invalid)
record.
If the source system doesn't keep a history of these changes, then we can't
redo the extract because it will contain a mix of valid and invalid changes.
Thanks,
Wreck.
Hello Wreck,
Have you considered type 1 for dimensions that are affected by this
issue? This way the transactions are not effected. You can have a
mixture of type 1 and 2 in your stars.
Check out:
http://bi-on-sql-server.blogspot.com...-changing.html
I wrote a blog on type 2 and type 1.
Type 2 only works at the point of time for the extracts as you
mentioned. One way of resolving this problem with dirty changes is to
take more frequent extracts to capture more of the changes.
Another option is to remove dirty changes from the dimension if there
are no corresponding fact transactions.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hi Wreck,
you are entering 'religious territory' ground.....
There are two religions here:
1. Go back and correct data 'known to be incorrect'.
2. Retain data known to be incorrect and enter new transactions which
update the DW such that it now represents the data 'known' to be
correct. (Which is really just 'thought to be correct now'.)
I come down in the camp of retain the incorrect data and enter new
transactions such the data is now set to the 'thought to be correct
value'. In your case, the account would be 're-opened' and it would
occur as 're-opened'.
Why?
Because going back and updating data is a very slippery slope. Where
does it end? And if decisions were made against data that was presented
at a certain point in time and then the underlying data is 'corrected'
and people come along to review those decisions and the data says
'these decisions are not well founded on the data' what does one do?
One must remember that data is never, ever 'true' or 'correct' it is
only ever 'thought to be correct/true' which means that data that we
have realised is incorrect and we now believe to be true has the same
likelyhood of being incorrect as the data we just changed....
Whereas, if we retain the fact that we found data to be incorrect and
we enter transactions to place new transactions to correct that data we
can see the information on which decisions were taken at the time. We
can also see how often data is being corrected....perhaps there are
underlying issues for a high rate of data corrections occurring such as
lack of proper training in the call centers taking calls and entering
data? It happens...
In some cases it is a legal requirement not to correct historical data.
For example, in most countries it is illegal to update the financial
statements of a company after the close of the annual accounts...yet
clearly there have been some very 'incorrect' entries in the financials
of some very large companies over the last few years.
Best Regards
Peter Nolan
www.peternolan.com
|||Thanks for your feedback guys.
Myles, the system currently has Type I changes across the board, and
naturally doesn't track history very well. I want to implement Type II, but
the data in the source systems has its issues, hence my concerns.
There are steps being taken to improve the data quality in the source systems.
Peter, I'm inclined to agree with you that we don't correct anything,
especially once it's been reported on. However, the business is still fairly
new to the whole warehouse concept - prior to this, it was the usual story of
lots of Access and Excel apps extracting and manipulating data for reporting.
When these data errors are found, they don't really understand why it's so
difficult to make a correction.
Thanks,
Wreck.
"Peter Nolan" wrote:

> Hi Wreck,
> you are entering 'religious territory' ground.....
> There are two religions here:
> 1. Go back and correct data 'known to be incorrect'.
> 2. Retain data known to be incorrect and enter new transactions which
> update the DW such that it now represents the data 'known' to be
> correct. (Which is really just 'thought to be correct now'.)
> I come down in the camp of retain the incorrect data and enter new
> transactions such the data is now set to the 'thought to be correct
> value'. In your case, the account would be 're-opened' and it would
> occur as 're-opened'.
> Why?
> Because going back and updating data is a very slippery slope. Where
> does it end? And if decisions were made against data that was presented
> at a certain point in time and then the underlying data is 'corrected'
> and people come along to review those decisions and the data says
> 'these decisions are not well founded on the data' what does one do?
> One must remember that data is never, ever 'true' or 'correct' it is
> only ever 'thought to be correct/true' which means that data that we
> have realised is incorrect and we now believe to be true has the same
> likelyhood of being incorrect as the data we just changed....
> Whereas, if we retain the fact that we found data to be incorrect and
> we enter transactions to place new transactions to correct that data we
> can see the information on which decisions were taken at the time. We
> can also see how often data is being corrected....perhaps there are
> underlying issues for a high rate of data corrections occurring such as
> lack of proper training in the call centers taking calls and entering
> data? It happens...
> In some cases it is a legal requirement not to correct historical data.
> For example, in most countries it is illegal to update the financial
> statements of a company after the close of the annual accounts...yet
> clearly there have been some very 'incorrect' entries in the financials
> of some very large companies over the last few years.
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||Hi Wreck,
I have been doing DWing for 15 years...and I have seen a great many
cases of significant destruction of value within a company on the basis
that 'the business users do not understand'.......and I have seen
even more because 'the IT people do not understand'.!!!... ;-)
Indeed, it seems that those who 'do not understand' seem to think the
onus is on those who 'do understand' to explain to them what it is that
is not understood to persuade them not to destroy value in their
company. Those who 'do not understand' do not seem to think the onus
is on them to make an effort to understand to assist them create value
in their company...and so value is commonly destroyed....it would be
funnier if it was not so common...(LOL)!!
You are lucky in that today there are tons of materials available on
reasonable practices for DW development.
However, what is published is never the 'leading edge' and is rarely
'best practice'.
'Leading edge' and 'Best Practice' are retained within companies for
competitive advantage...;-)
Best Regards
Peter Nolan
www.peternolan.com

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegroups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying users to new server

I'm new to SQL server and am installing a new server that I will be copying
from a old server to new. I've restored the databases but am trying to cop
y
over the users and security.
Is there a simple way to copy over the users and security to the new server?> I'm new to SQL server and am installing a new server that I will be
copying
> from a old server to new. I've restored the databases but am trying to
copy
> over the users and security.
> Is there a simple way to copy over the users and security to the new
server?
Yes, script them. In EM, right-click on your database, select All Tasks -
Generate SQL Script; on the Options tab, select
Script database users and database roles and
Script object-level permissions.
Generate the script and execute it in your new db.
BTW, I suppose you have the same logins.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Dejan
I think although he/she has the same logins moving users to the new server
may cause the problem due to the different SID of the logins.
I'd go with two stored procedures provided by Microsoft to move logins.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eeySs4qLFHA.2988@.TK2MSFTNGP14.phx.gbl...
> copying
> copy
> server?
> Yes, script them. In EM, right-click on your database, select All Tasks -
> Generate SQL Script; on the Options tab, select
> Script database users and database roles and
> Script object-level permissions.
> Generate the script and execute it in your new db.
> BTW, I suppose you have the same logins.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>|||http://support.microsoft.com/defaul...kb;en-us;246133
"kcsheff" wrote:
[vbcol=seagreen]
> I'm new to SQL server and am installing a new server that I will be copyin
g
> from a old server to new. I've restored the databases but am trying to c
opy
> over the users and security.
> Is there a simple way to copy over the users and security to the new server?[/vbco
l]

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.
did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:

> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:

> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.
|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:

> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>
|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "examnotes"
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:

> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY i
f
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easies
t
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:

> Hi,
> I am a newbie. I want to copy a table from the production database and pas
te
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:

> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See t
he
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
[vbcol=seagreen]
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
>sql

copying tables in SQL Server

Hi,
I am a newbie. I want to copy a table from the production database and paste
it in the test database thus replacing the old copy in the test database.
What's the best way to do it? Thanks in advance.did you hear "=?Utf-8?B?c2hhcm1hbg==?="
<sharman@.discussions.microsoft.com> say in
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com:
> I am a newbie. I want to copy a table from the production database and
> paste it in the test database thus replacing the old copy in the test
> database. What's the best way to do it? Thanks in advance.
>
check books online for the Import/Export wizard.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Sharman,
Try the Import/Export Wizard in DTS (Data Transformation Services). See the
SQL Books Online for more information.
HTH
Jerry
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> Hi,
> I am a newbie. I want to copy a table from the production database and
> paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Unfortunately, you cannot copy and paste a table from one database to
another. The easiest way to do this is using the DTS Export/Import Wizard.
From the SOURCE database, if you right click the table name, go to ALL
TASKS, then Export Data, it will bring up the wizard.
1) Hit next on the welcome screen.
2) Hit next on the source screen. By starting from the source, you won't
have to enter this information.
3) On the destination screen, enter the server name and choose the
database. You won't have to do anything with the table name at this point.
You just have to tell it where to send the table.
4) The next screen actually has a direct "copy tables..." option, but I
prefer the copy objects and data method, which is the last choice on the
screen. Choose that and click next.
5) You should now be at the "Select Objects to Copy" screen. Here it's
safe to leave the top options, but you MUST uncheck a few and make some
changes at the bottom of the screen. First, UNCHECK BOTH Copy all objects
and Use default options.
6) Hit the "Select Objects" button and mark the table you want to copy.
Then click OK.
7) Now, hit the "Options" button and turn off Copy database users and roles
as well as object-level permissions. You can copy object permissions ONLY if
you have the same database users and roles in the source and destination
databases. If you don't, the copy users and roles choice could help, unless
some of the users or roles already exist.. where it would fail. The easiest
method is to uncheck both of these boxes and reconfigure permissions for the
table once it's copied over.
8) Now hit OK, then next and finish all the way through until your table is
done and copied.
Hope this helps!
Bryan
"sharman" wrote:
> Hi,
> I am a newbie. I want to copy a table from the production database and paste
> it in the test database thus replacing the old copy in the test database.
> What's the best way to do it? Thanks in advance.|||Thanks a lot. I thought there was some easy way like copying and pasting.
"Jerry Spivey" wrote:
> Sharman,
> Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> SQL Books Online for more information.
> HTH
> Jerry
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > Hi,
> >
> > I am a newbie. I want to copy a table from the production database and
> > paste
> > it in the test database thus replacing the old copy in the test database.
> > What's the best way to do it? Thanks in advance.
>
>|||Sharman,
Instead of using DTS you can restore the production backup file on your
test environment.
Erwin
sharman schreef:
> Thanks a lot. I thought there was some easy way like copying and pasting.
> "Jerry Spivey" wrote:
> > Sharman,
> >
> > Try the Import/Export Wizard in DTS (Data Transformation Services). See the
> > SQL Books Online for more information.
> >
> > HTH
> >
> > Jerry
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:1BFF4909-65DF-41E8-A37C-518BE50D44EC@.microsoft.com...
> > > Hi,
> > >
> > > I am a newbie. I want to copy a table from the production database and
> > > paste
> > > it in the test database thus replacing the old copy in the test database.
> > > What's the best way to do it? Thanks in advance.
> >
> >
> >

copying table to new schema not working

SQL 7.0
Trying to copy a table to a table with different schema
and getting an error.
Old Schema example
Name, char, 30 NOT NULL
address, char, 30 NOT NULL
New Schema
Name,char, 30 NOT NULL
id,number,10 NULL ..........new field
address, char, 30 NOT NULL
I had imagined that the fields that were common would
copy and the new field "ID" would not fill in. I'm pretty
sure my test worked yesterday, but today, it fails with
the error that old.address is trying to copy to
new.number and it fails.
There are roughly 100 million rows and if I try to modify
the old table directly by adding the ID field, SQL
eventually gives some kind of LOG error.
Any suggestions on how to convert this table to the new
schema?
Thanks,
Don
First of all, there is no datatype in SQL Server called 'number'. I'm not
sure why you're not getting an error as a result of that. Second, how are
you doing this copy? Can you post code and/or what method you're using?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
How are you trying to copy it?
Are you using SEM to try to add the new field?
How about running:
ALTER TABLE ADD id numeric(x,y) NULL
Other choices that can avoid the LOG error issues..
BCP the data out.
TRUNCATE the table.
ALTER TABLE to add the column.
Remove indexes
BULK INSERT the data back in.
Recreate your indexes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||I'd been using DTS to copy. Are you saying BULK copy will
not have the same LOG filling up problems?

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify
>How are you trying to copy it?
>Are you using SEM to try to add the new field?
>How about running:
>ALTER TABLE ADD id numeric(x,y) NULL
>Other choices that can avoid the LOG error issues..
>BCP the data out.
>TRUNCATE the table.
>ALTER TABLE to add the column.
>Remove indexes
>BULK INSERT the data back in.
>Recreate your indexes.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>.
>
|||"number" was just an example of a field type and not to
be taken literally.
Using DTS to do the copy. Just exporting from Source and
Destination, and then choosing the table to copy from and
the new table to copy to which has the new schema.

>--Original Message--
>First of all, there is no datatype in SQL Server
called 'number'. I'm not
>sure why you're not getting an error as a result of
that. Second, how are
>you doing this copy? Can you post code and/or what
method you're using?
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify
>
>.
>
|||<anonymous@.discussions.microsoft.com> wrote in message
news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'd been using DTS to copy. Are you saying BULK copy will
> not have the same LOG filling up problems?
>
> message
> pretty
> modify
It depends on how you do the package.
DTS (if you look closely) will use BCP in a lot of cases.
Take a look at "Logged Bulk Copy" in the BOL. It should show you how to do
a minimally logged BCP command. BULK INSERT is generally faster however, so
keep that option in mind as well.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||The BULK COPY is nice and all, but why add an unnecessary field to an old
schema?
The problem with your DTS is that you haven't properly mapped the source
columns to the destination columns.
You could just use a straight INSERT statement.
INSERT INTO newschema.MyTable(name, address)
SELECT name, address
FROM oldschema.MyTable
Sincerely,
Anthony Thomas

"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
|||This might be a rediculous question, but if I detach and
reattach the log file to a larger hard drive with a lot
of space...i shouldn't get the out of LOG space message?
Is this a "duh" question?
Don
[vbcol=seagreen]
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
will[vbcol=seagreen]
schema[vbcol=seagreen]
with[vbcol=seagreen]
new
>
>It depends on how you do the package.
>DTS (if you look closely) will use BCP in a lot of cases.
>Take a look at "Logged Bulk Copy" in the BOL. It
should show you how to do
>a minimally logged BCP command. BULK INSERT is
generally faster however, so
>keep that option in mind as well.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

copying table to new schema not working

SQL 7.0
Trying to copy a table to a table with different schema
and getting an error.
Old Schema example
Name, char, 30 NOT NULL
address, char, 30 NOT NULL
New Schema
Name,char, 30 NOT NULL
id,number,10 NULL ..........new field
address, char, 30 NOT NULL
I had imagined that the fields that were common would
copy and the new field "ID" would not fill in. I'm pretty
sure my test worked yesterday, but today, it fails with
the error that old.address is trying to copy to
new.number and it fails.
There are roughly 100 million rows and if I try to modify
the old table directly by adding the ID field, SQL
eventually gives some kind of LOG error.
Any suggestions on how to convert this table to the new
schema?
Thanks,
DonFirst of all, there is no datatype in SQL Server called 'number'. I'm not
sure why you're not getting an error as a result of that. Second, how are
you doing this copy? Can you post code and/or what method you're using?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
How are you trying to copy it?
Are you using SEM to try to add the new field?
How about running:
ALTER TABLE ADD id numeric(x,y) NULL
Other choices that can avoid the LOG error issues..
BCP the data out.
TRUNCATE the table.
ALTER TABLE to add the column.
Remove indexes
BULK INSERT the data back in.
Recreate your indexes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||I'd been using DTS to copy. Are you saying BULK copy will
not have the same LOG filling up problems?
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
>> SQL 7.0
>> Trying to copy a table to a table with different schema
>> and getting an error.
>> Old Schema example
>> Name, char, 30 NOT NULL
>> address, char, 30 NOT NULL
>> New Schema
>> Name,char, 30 NOT NULL
>> id,number,10 NULL ..........new field
>> address, char, 30 NOT NULL
>> I had imagined that the fields that were common would
>> copy and the new field "ID" would not fill in. I'm
pretty
>> sure my test worked yesterday, but today, it fails with
>> the error that old.address is trying to copy to
>> new.number and it fails.
>> There are roughly 100 million rows and if I try to
modify
>> the old table directly by adding the ID field, SQL
>> eventually gives some kind of LOG error.
>> Any suggestions on how to convert this table to the new
>> schema?
>> Thanks,
>> Don
>>
>How are you trying to copy it?
>Are you using SEM to try to add the new field?
>How about running:
>ALTER TABLE ADD id numeric(x,y) NULL
>Other choices that can avoid the LOG error issues..
>BCP the data out.
>TRUNCATE the table.
>ALTER TABLE to add the column.
>Remove indexes
>BULK INSERT the data back in.
>Recreate your indexes.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>.
>|||"number" was just an example of a field type and not to
be taken literally.
Using DTS to do the copy. Just exporting from Source and
Destination, and then choosing the table to copy from and
the new table to copy to which has the new schema.
>--Original Message--
>First of all, there is no datatype in SQL Server
called 'number'. I'm not
>sure why you're not getting an error as a result of
that. Second, how are
>you doing this copy? Can you post code and/or what
method you're using?
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
>> SQL 7.0
>> Trying to copy a table to a table with different schema
>> and getting an error.
>> Old Schema example
>> Name, char, 30 NOT NULL
>> address, char, 30 NOT NULL
>> New Schema
>> Name,char, 30 NOT NULL
>> id,number,10 NULL ..........new field
>> address, char, 30 NOT NULL
>> I had imagined that the fields that were common would
>> copy and the new field "ID" would not fill in. I'm
pretty
>> sure my test worked yesterday, but today, it fails with
>> the error that old.address is trying to copy to
>> new.number and it fails.
>> There are roughly 100 million rows and if I try to
modify
>> the old table directly by adding the ID field, SQL
>> eventually gives some kind of LOG error.
>> Any suggestions on how to convert this table to the new
>> schema?
>> Thanks,
>> Don
>>
>
>.
>|||<anonymous@.discussions.microsoft.com> wrote in message
news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
> I'd been using DTS to copy. Are you saying BULK copy will
> not have the same LOG filling up problems?
>
>>--Original Message--
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
>> SQL 7.0
>> Trying to copy a table to a table with different schema
>> and getting an error.
>> Old Schema example
>> Name, char, 30 NOT NULL
>> address, char, 30 NOT NULL
>> New Schema
>> Name,char, 30 NOT NULL
>> id,number,10 NULL ..........new field
>> address, char, 30 NOT NULL
>> I had imagined that the fields that were common would
>> copy and the new field "ID" would not fill in. I'm
> pretty
>> sure my test worked yesterday, but today, it fails with
>> the error that old.address is trying to copy to
>> new.number and it fails.
>> There are roughly 100 million rows and if I try to
> modify
>> the old table directly by adding the ID field, SQL
>> eventually gives some kind of LOG error.
>> Any suggestions on how to convert this table to the new
>> schema?
>> Thanks,
>> Don
It depends on how you do the package.
DTS (if you look closely) will use BCP in a lot of cases.
Take a look at "Logged Bulk Copy" in the BOL. It should show you how to do
a minimally logged BCP command. BULK INSERT is generally faster however, so
keep that option in mind as well.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||The BULK COPY is nice and all, but why add an unnecessary field to an old
schema?
The problem with your DTS is that you haven't properly mapped the source
columns to the destination columns.
You could just use a straight INSERT statement.
INSERT INTO newschema.MyTable(name, address)
SELECT name, address
FROM oldschema.MyTable
Sincerely,
Anthony Thomas
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>|||This might be a rediculous question, but if I detach and
reattach the log file to a larger hard drive with a lot
of space...i shouldn't get the out of LOG space message?
Is this a "duh" question?
Don
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
>> I'd been using DTS to copy. Are you saying BULK copy
will
>> not have the same LOG filling up problems?
>>
>>--Original Message--
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
>> SQL 7.0
>> Trying to copy a table to a table with different
schema
>> and getting an error.
>> Old Schema example
>> Name, char, 30 NOT NULL
>> address, char, 30 NOT NULL
>> New Schema
>> Name,char, 30 NOT NULL
>> id,number,10 NULL ..........new field
>> address, char, 30 NOT NULL
>> I had imagined that the fields that were common would
>> copy and the new field "ID" would not fill in. I'm
>> pretty
>> sure my test worked yesterday, but today, it fails
with
>> the error that old.address is trying to copy to
>> new.number and it fails.
>> There are roughly 100 million rows and if I try to
>> modify
>> the old table directly by adding the ID field, SQL
>> eventually gives some kind of LOG error.
>> Any suggestions on how to convert this table to the
new
>> schema?
>> Thanks,
>> Don
>
>It depends on how you do the package.
>DTS (if you look closely) will use BCP in a lot of cases.
>Take a look at "Logged Bulk Copy" in the BOL. It
should show you how to do
>a minimally logged BCP command. BULK INSERT is
generally faster however, so
>keep that option in mind as well.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

copying table to new schema not working

SQL 7.0
Trying to copy a table to a table with different schema
and getting an error.
Old Schema example
Name, char, 30 NOT NULL
address, char, 30 NOT NULL
New Schema
Name,char, 30 NOT NULL
id,number,10 NULL ..........new field
address, char, 30 NOT NULL
I had imagined that the fields that were common would
copy and the new field "ID" would not fill in. I'm pretty
sure my test worked yesterday, but today, it fails with
the error that old.address is trying to copy to
new.number and it fails.
There are roughly 100 million rows and if I try to modify
the old table directly by adding the ID field, SQL
eventually gives some kind of LOG error.
Any suggestions on how to convert this table to the new
schema?
Thanks,
DonFirst of all, there is no datatype in SQL Server called 'number'. I'm not
sure why you're not getting an error as a result of that. Second, how are
you doing this copy? Can you post code and/or what method you're using?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
How are you trying to copy it?
Are you using SEM to try to add the new field?
How about running:
ALTER TABLE ADD id numeric(x,y) NULL
Other choices that can avoid the LOG error issues..
BCP the data out.
TRUNCATE the table.
ALTER TABLE to add the column.
Remove indexes
BULK INSERT the data back in.
Recreate your indexes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||I'd been using DTS to copy. Are you saying BULK copy will
not have the same LOG filling up problems?

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify[vbcol=seagreen]
>How are you trying to copy it?
>Are you using SEM to try to add the new field?
>How about running:
>ALTER TABLE ADD id numeric(x,y) NULL
>Other choices that can avoid the LOG error issues..
>BCP the data out.
>TRUNCATE the table.
>ALTER TABLE to add the column.
>Remove indexes
>BULK INSERT the data back in.
>Recreate your indexes.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>.
>|||"number" was just an example of a field type and not to
be taken literally.
Using DTS to do the copy. Just exporting from Source and
Destination, and then choosing the table to copy from and
the new table to copy to which has the new schema.

>--Original Message--
>First of all, there is no datatype in SQL Server
called 'number'. I'm not
>sure why you're not getting an error as a result of
that. Second, how are
>you doing this copy? Can you post code and/or what
method you're using?
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify[vbcol=seagreen]
>
>.
>|||<anonymous@.discussions.microsoft.com> wrote in message
news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'd been using DTS to copy. Are you saying BULK copy will
> not have the same LOG filling up problems?
>
> message
> pretty
> modify
It depends on how you do the package.
DTS (if you look closely) will use BCP in a lot of cases.
Take a look at "Logged Bulk Copy" in the BOL. It should show you how to do
a minimally logged BCP command. BULK INSERT is generally faster however, so
keep that option in mind as well.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||The BULK COPY is nice and all, but why add an unnecessary field to an old
schema?
The problem with your DTS is that you haven't properly mapped the source
columns to the destination columns.
You could just use a straight INSERT statement.
INSERT INTO newschema.MyTable(name, address)
SELECT name, address
FROM oldschema.MyTable
Sincerely,
Anthony Thomas
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>|||This might be a rediculous question, but if I detach and
reattach the log file to a larger hard drive with a lot
of space...i shouldn't get the out of LOG space message?
Is this a "duh" question?
Don

>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
will[vbcol=seagreen]
schema[vbcol=seagreen]
with[vbcol=seagreen]
new[vbcol=seagreen]
>
>It depends on how you do the package.
>DTS (if you look closely) will use BCP in a lot of cases.
>Take a look at "Logged Bulk Copy" in the BOL. It
should show you how to do
>a minimally logged BCP command. BULK INSERT is
generally faster however, so
>keep that option in mind as well.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Copying records from 7.0 2000 with problems

Hi.

I need to copy data from one database to another, on separate servers. I've got an old production database on "server1" and its SQL Server 7.0 on NT4. This data needs to move from there over to a database on "server2" which is SQL2000/Windows2000server.

Im trying to just run test queries from "server2" to read tables on "server1" and all I can get is 'invalid object name.'

Server1 is a linked server under Server2 and is being logged into as sa.

The computername for server1 happens to be "DATABASE"
Is this a problem? From what I understand I should be able to select from <server name>.<dbname>.dbo.<tablename>

I have tried typing <server name> as [DATABASE] and "DATABASE" and any other delimiting under the sun but all I get is 'invalid object name'

I have also tried just trying to select max(helptext) from "DATABASE".master.dbo.helpsql and I still get 'invalid object'

All of my queries will work if run from query analyzer on "server1" but not from query analyzer on "server2"

thanks in advance,

-jakedo you intend to move all the data in the database??
if you are maybe you can use the dts tools or if possible restore the full backup to the server2.
I can't really remember if sql 7's backup file can be restore to sql 2k version as haven't been using for quite a long time but you can try.|||I am only moving the data from select tables. The database names are different too.

I've pretty much ruled out the possibility of a backup/restore senario.|||have u tried import/export ??|||It was a communication issue.

I Turned Mixed Protocol OFF on the old 7.0 box and turned it ON the 2K box. Go Figure. The most confusing part of this ordeal was that the login for linked server was working from both servers and you could see the master tables/views under linked server.|||sorry in that case can't help you much
hopefully somebody else can help you on this|||Wow. That was like phone tag in a message board. Don't think I've ever experienced that.

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
Ellis
Ellis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another
?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan user
s.
> How can I avoid this? or anyone know article teach people to do this step
by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

Copying existing sqlserver to a new server

Hi all,
Anyone know how to copy whole sqlserver from one server to another?
Since our old server has limitation of its harddisk space, we need to
replace it to a new one. I read some articles about this by using
attach/detach or backup/restore. But I heard that it may cause orphan users.
How can I avoid this? or anyone know article teach people to do this step by
step? Please help. Thanks
Best Rdgs
EllisEllis,
This topic is covered almost daily here, you might want to search google
groups for the answer.
Anyway, I will cover it briefly.
1) Build your new server with the same directory structure as the old.
2) Install SQL Server to the same location on the new server as the old.
3) Install same service pack level, stop the MSSQLServer service on new
4) Stop MSSQLServer on the old server
5) Copy over the data files directory to the new server
6) Start the MSSQLServer service on new server
7) exec sp_dropserver '<oldname>'
8) exec sp_addserver '<newserver>','local'
9) Bounce MSSSQLServer service
http://vyaskn.tripod.com/moving_sql_server.htm
Here's some info compiled by Andrew Kelly (sorry about formatting):
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Ellis Yu wrote:
> Hi all,
> Anyone know how to copy whole sqlserver from one server to another?
> Since our old server has limitation of its harddisk space, we need to
> replace it to a new one. I read some articles about this by using
> attach/detach or backup/restore. But I heard that it may cause orphan users.
> How can I avoid this? or anyone know article teach people to do this step by
> step? Please help. Thanks
> Best Rdgs
> Ellis
>

copying databases to server with same name/IP

I need to copy databases with transactional replication to another box and the new server will be have the exact same name and IP address as the old server. I am planning on copying over complete backups of the master, msdb, model, distribution and userDB
databases to the new box and I was going to do a restore of each, then try to set up the replication again. Is there any way to keep the replication intact so I don't have to set it up again? Is there any particular order in which the databases have to b
e retored?
Try this order
master, msdb, distribution, publication databases. Restore the publication databases with the keep replication switch.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks for the response. I am not so hot with replication - just to clarify - by "publication databases" you mean the user databases right? The ones being replicated? Thanks again.
|||Yes
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Copy tables between servers using SQL Server 2005 Enterprise Edition

Hi,

I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now!

Please can anybody guide me in the right direction?

Thanks a lot.

Now you can use SSIS package instead of DTS package, which can also be generated by using Import/Export Wizard as DTS Yes it is more complex than DTS, but much more powerful, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms141134(d=ide).aspx

When you talking about copying table between servers, do you mean to copy data as well or just copy table schema? If you only want to copy table schema, you can easily do this by generating scripts for the tables on the source server: simply right click a database->choose Tasks->Generate Scripts.

If you want to copy data as well, Import/Export Wizard is your good friend; or you can backup the databases on the source server then restore from the backups on the destination; or you can even use Copy Database Wizard.

|||

Thanks a lot lori_Jay, it's much easier when you know what you're looking for! It turned out I hadn't installed all the necessary components. Best to always do a full install if you have the space I guess.

Thanks again.

Copy table to a new database

Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
I need som data in the "TrackOLD" DB
How do I copy a table from "TrackOLD" to "TrackNEW"?
Both DB are on the same server.
Thanksmsnews.microsoft.com wrote:
> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
> I need som data in the "TrackOLD" DB
> How do I copy a table from "TrackOLD" to "TrackNEW"?
> Both DB are on the same server.
> Thanks
>
To insert the "old" data into a new table in the "new" DB, do this:
SELECT *
INTO NewDB.dbo.NewTable
FROM OldDB.dbo.OldTable
(note that this WILL NOT bring over indexes, defaults, triggers,
constraints, or anything else related to the table, JUST the data)
To insert the "old" data into an existing table in the "new" DB, do this:
INSERT INTO NewDB.dbo.NewTable
SELECT *
FROM OldDB.dbo.OldTable
(you may encounter problems due to trying to insert IDENTITY values - if
so, replace the SELECT * with the explicit list of columns that you want
to copy over, excluding the IDENTITY column)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I have copy all the data I need.
Gjerde
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A4E831.7020904@.realsqlguy.com...
> msnews.microsoft.com wrote:
> To insert the "old" data into a new table in the "new" DB, do this:
> SELECT *
> INTO NewDB.dbo.NewTable
> FROM OldDB.dbo.OldTable
> (note that this WILL NOT bring over indexes, defaults, triggers,
> constraints, or anything else related to the table, JUST the data)
> To insert the "old" data into an existing table in the "new" DB, do this:
> INSERT INTO NewDB.dbo.NewTable
> SELECT *
> FROM OldDB.dbo.OldTable
> (you may encounter problems due to trying to insert IDENTITY values - if
> so, replace the SELECT * with the explicit list of columns that you want
> to copy over, excluding the IDENTITY column)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copy table to a new database

Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
I need som data in the "TrackOLD" DB
How do I copy a table from "TrackOLD" to "TrackNEW"?
Both DB are on the same server.
Thanksmsnews.microsoft.com wrote:
> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
> I need som data in the "TrackOLD" DB
> How do I copy a table from "TrackOLD" to "TrackNEW"?
> Both DB are on the same server.
> Thanks
>
To insert the "old" data into a new table in the "new" DB, do this:
SELECT *
INTO NewDB.dbo.NewTable
FROM OldDB.dbo.OldTable
(note that this WILL NOT bring over indexes, defaults, triggers,
constraints, or anything else related to the table, JUST the data)
To insert the "old" data into an existing table in the "new" DB, do this:
INSERT INTO NewDB.dbo.NewTable
SELECT *
FROM OldDB.dbo.OldTable
(you may encounter problems due to trying to insert IDENTITY values - if
so, replace the SELECT * with the explicit list of columns that you want
to copy over, excluding the IDENTITY column)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I have copy all the data I need.
Gjerde
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A4E831.7020904@.realsqlguy.com...
> msnews.microsoft.com wrote:
>> Hi, I have a old DB "TrackOLD" and have instald a new "TrackNEW"
>> I need som data in the "TrackOLD" DB
>> How do I copy a table from "TrackOLD" to "TrackNEW"?
>> Both DB are on the same server.
>> Thanks
> To insert the "old" data into a new table in the "new" DB, do this:
> SELECT *
> INTO NewDB.dbo.NewTable
> FROM OldDB.dbo.OldTable
> (note that this WILL NOT bring over indexes, defaults, triggers,
> constraints, or anything else related to the table, JUST the data)
> To insert the "old" data into an existing table in the "new" DB, do this:
> INSERT INTO NewDB.dbo.NewTable
> SELECT *
> FROM OldDB.dbo.OldTable
> (you may encounter problems due to trying to insert IDENTITY values - if
> so, replace the SELECT * with the explicit list of columns that you want
> to copy over, excluding the IDENTITY column)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

copy som data from one field split it up into 3 new fields

Hey there
I have to copy som data from one field and split it up into 3 seperat new
fields in the same table...
Eks.:
---
Data column Artis (Old field)
---
Artist
Larsen, Kim & Kjukken
Larsen, Kim
Larsen, Kim - Kjukken
---
Three new data columns
---
Firstname Lastname Band
Kim Larsen & Kjukken
Kim Larsen
Kim Larsen - Kjukken
My SQL Statement looks like this now, I just have to add the Band column and
the other - and & checks:
UPDATE Test_Products
SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
LEFT(artist, CHARINDEX(' ',artist) - 1)
ELSE
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
END,
Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
ELSE
LEFT(artist, CHARINDEX(',',artist) - 1)
END
I hope hearing from you soon, because my project is totally jamed up until I
get this working and done.
Regards,
Lucien
Mvh
DC
--
Greetings ecoderHi
I'd prefer doing such thing on the client
create table #test
(
col varchar (50)
)
insert into #test values ('Larsen, Kim & Kjukken')
insert into #test values ('Larsen, Kim')
insert into #test values ('Larsen, Kim - Kjukken')
select LastName, FirstName, coalesce(Band,FirstName) Band
from (
select
col,
substring(col,1,Comma-1) LastName,
substring(col,Comma+1,Spce-4) FirstName,
nullif(substring(col,Spce+4,40),'') Band
from (
select
col,
charindex(',',col) Comma,
charindex(' ',col+space(1),charindex(',',col)) Spce
from #test
) D
) SplitNames
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column
> and
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
> I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||See if this helps:
select
c1,
left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
left(c1, charindex(',', c1) - 1) as lastname,
parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
from
(
select 'Larsen, Kim & Kjukken'
union all
select 'Larsen, Kim'
union all
select 'Larsen, Kim - Kjukken'
) as t1(c1)
go
AMB
"ecoder" wrote:

> Hey there
> I have to copy som data from one field and split it up into 3 seperat new
> fields in the same table...
> Eks.:
> ---
> Data column Artis (Old field)
> ---
> Artist
> Larsen, Kim & Kjukken
> Larsen, Kim
> Larsen, Kim - Kjukken
> ---
> Three new data columns
> ---
> Firstname Lastname Band
> Kim Larsen & Kjukken
> Kim Larsen
> Kim Larsen - Kjukken
> --
> My SQL Statement looks like this now, I just have to add the Band column a
nd
> the other - and & checks:
> UPDATE Test_Products
> SET Firstname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> LEFT(artist, CHARINDEX(' ',artist) - 1)
> ELSE
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> END,
> Lastname = CASE WHEN CHARINDEX(',',artist) = 0 THEN
> RIGHT(artist, LEN(artist) - CHARINDEX(' ',artist))
> ELSE
> LEFT(artist, CHARINDEX(',',artist) - 1)
> END
> I hope hearing from you soon, because my project is totally jamed up until
I
> get this working and done.
> Regards,
> Lucien
> Mvh
> DC
> --
> Greetings ecoder|||Hi ,Alejandro
Hehe, I have faced the same "problem". A Band column for Kim is NULL.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
> See if this helps:
> select
> c1,
> left(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex(' ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' ') - 1) as firstname,
> left(c1, charindex(',', c1) - 1) as lastname,
> parsename(stuff(ltrim(stuff(c1, 1, charindex(',', c1), '')), charindex('
> ',
> ltrim(stuff(c1, 1, charindex(',', c1), '')) + ' '), 1, '.'), 1) as band
> from
> (
> select 'Larsen, Kim & Kjukken'
> union all
> select 'Larsen, Kim'
> union all
> select 'Larsen, Kim - Kjukken'
> ) as t1(c1)
> go
>
> AMB
> "ecoder" wrote:
>|||Hi Uri
When I run it in query analyzer the field without a band looks like this:
Firstname Lastname Band
Kim Larsen Kim
Why is that and what should I change to correct this?
Regards,
ecoder
"Uri Dimant" wrote:

> Hi
> I'd prefer doing such thing on the client
>
> create table #test
> (
> col varchar (50)
> )
> insert into #test values ('Larsen, Kim & Kjukken')
> insert into #test values ('Larsen, Kim')
> insert into #test values ('Larsen, Kim - Kjukken')
>
> select LastName, FirstName, coalesce(Band,FirstName) Band
> from (
> select
> col,
> substring(col,1,Comma-1) LastName,
> substring(col,Comma+1,Spce-4) FirstName,
> nullif(substring(col,Spce+4,40),'') Band
> from (
> select
> col,
> charindex(',',col) Comma,
> charindex(' ',col+space(1),charindex(',',col)) Spce
> from #test
> ) D
> ) SplitNames
>
> "ecoder" <ecoder@.discussions.microsoft.com> wrote in message
> news:1B244528-6EBE-42DF-B1DB-3194BB9A2D7E@.microsoft.com...
>
>|||Uri,
Let him / her to decide what to do with null values. The help you provided
is a good start, the rest is up to him / her.
AMB
"Uri Dimant" wrote:

> Hi ,Alejandro
> Hehe, I have faced the same "problem". A Band column for Kim is NULL.
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:22AF2E13-05F9-4DCA-BA56-D50D2A2A1031@.microsoft.com...
>
>|||Lookup LTRIM(),RTRIM() functions in the BOL
"ecoder" <ecoder@.discussions.microsoft.com> wrote in message
news:EABB8DE8-1DD3-44E0-875E-51B775A85038@.microsoft.com...
> Hi Uri
> When I run it in query analyzer the field without a band looks like this:
> Firstname Lastname Band
> Kim Larsen Kim
> Why is that and what should I change to correct this?
> Regards,
> ecoder
>
> "Uri Dimant" wrote:
>|||It's all working well becides when to band column is null
This is a urgent one...
If the band column is empty it copies the firstname Kim into the band column
.
What should I change....?
And thanks Uri and Alejandro for your help.
Greetings ecoder
"Alejandro Mesa" wrote:
> Uri,
> Let him / her to decide what to do with null values. The help you provided
> is a good start, the rest is up to him / her.
>
> AMB
> "Uri Dimant" wrote:
>|||If you are talking about Uri's solution, then use an empty string for the
second argument of the "coalesce" function or do not use the "coalesce"
function at all.

> select LastName, FirstName, coalesce(Band,FirstName) Band
select LastName, FirstName, coalesce(Band,'') Band
...
-- or
select LastName, FirstName, Band
...
AMB
"ecoder" wrote:
> It's all working well becides when to band column is null
> This is a urgent one...
> If the band column is empty it copies the firstname Kim into the band colu
mn.
> What should I change....?
> And thanks Uri and Alejandro for your help.
> --
> Greetings ecoder
>
> "Alejandro Mesa" wrote:
>|||God morning...
I have one last question though. I have to update an existing database table
with lots off records.
How do I change your or Uri examples to UPDATE instead of INSERT.
Sorry I'm not that hardcore in T-SQL!
:-)
But i'm trying...
Hope hearing from you.
Regards,
ecoder
"Alejandro Mesa" wrote:
> If you are talking about Uri's solution, then use an empty string for the
> second argument of the "coalesce" function or do not use the "coalesce"
> function at all.
>
> select LastName, FirstName, coalesce(Band,'') Band
> ...
> -- or
> select LastName, FirstName, Band
> ...
>
> AMB
>
> "ecoder" wrote:
>