Moving data from DB to DB w/ different structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Moving data from DB to DB w/ different structure

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 09:53:19

Hey all,

My task is that I have a MS SQL DB with a Vendor Table in it. I then have a new MS SQL DB that also has a Vendor Table but the column names/structure is different in areas. What is the best way to transfer hundreds of these records from one DB to the other DB???

Am I going to have to use comma-delimited files, or can I use some code in QA to match up columns between the DBs, etc. and execute a mass-transfer??

Thanks for any help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 09:59:36

Insert into(columns with order) DB1..vendortable
Select (columns with same above order) from DB2..vendortable

Madhivanan

Failing to plan is Planning to fail

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-05 : 10:05:38

Might also want to use this:

set identity_insert DB1..vendortable on
--madhivan's code used without permission!
Insert into(columns with order) DB1..vendortable
Select (columns with same above order) from DB2..vendortable

set identity_insert DB1..vendortable off

That way the Identity fields come over.

Aj

Hey, it compiles.

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 10:09:00

Ahhhhh okay, that seems easy enough. Thanks guys, i'll go try it out.

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 10:11:16

Oh, forgot... will this MOVE they data or just make a COPY of it? I would like to just copy it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 10:12:26

>>--madhivan's code used without permission!

No problem at all

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 10:17:53

>>I would like to just copy it.

Yes Only copying not moving

Madhivanan

Failing to plan is Planning to fail

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 10:27:48

Great! Thank you for your help. Is it possible while doing this data transfer to insert a value into one of columns too?

For example, I need to enter the company # (which doesn't exist in the old DB) for each record into the new DB. Can I use additional code to go ahead and insert it while doing the move?

Insert into(columns with order, CompanyNo = '555') DB1..vendortable
Select (columns with same above order) from DB2..vendortable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 10:35:26

Yes it is possible
First create a column companyNo in DB1.vendortable
Then

Insert into(columns with order, CompanyNo) DB1..vendortable
Select (columns with same above order),555 from DB2..vendortable

If CompanyNo is varchar use '555'

Madhivanan

Failing to plan is Planning to fail

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 10:39:24

Ahhhh.. Okay I see what you are doing. Cool! Thank you so much for your help.

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 10:53:06

[code]Insert into(LastN, FirstN, addy1, phno, age, extra) test2.Emp
Select (LName, Fname, address, phone, age), '55' from test1.Employee[/code]

[code]Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.[/code]

Okay, I've created some test databases and I'm playing with these commands. I am getting some syntax errors and trying to work them out, how is everything looking?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 10:59:53

That should be


Insert into(LastN, FirstN, addy1, phno, age, extra) test2.Emp
Select LName, Fname, address, phone, age, '55' from test1.Employee

As an example I put that braces

Madhivanan

Failing to plan is Planning to fail

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 11:08:50

Got it. Thanks...

I had to switch the DB.Table reference to the front to pass the check in QA. But now it can't find the DB.Table reference. I'm getting "Invalid object name 'Test2.Emp'." for both of them...

Do I need to add something extra in QA to make this connection?

Insert into Test2.Emp(LastN, FirstN, addy1, phno, age, extra)
Select LName, Fname, address, phone, age, '55' from Test1.Employee

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 11:11:14

Use two dots
test2..Emp
Test1..Employee

Madhivanan

Failing to plan is Planning to fail

hueby
Posting Yak Master

127 Posts

Posted - 2005-08-05 : 11:13:55

Thanks again. That was it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources