Need help transferring and modifying database

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

General SQL Server Forums

New to SQL Server Programming

Need help transferring and modifying database

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2012-02-02 : 10:48:13

Hi,
I need to transfer a customer's old user db to my new db. The old was a flat file structure (db2) and in order to make it more flexible I need to split it up into several.

So...I exported the db to csv and then imported it into ms sql 2008 using bulk import.

Now, the next step is importing the user details from the old structure into the new one. This is fairly easy. However, there are data in the old one I need to reference in order to fill the other tables. When inserting into the new they will get an id (primary key int) and I would like to put this into the old file as reference.

Is there a way to do this? I guess the base query would be something like
insert into newTable(firstname, lastname) select first_name, last_name from oldTable

The return from this would be the identity - but how do I pick it up and insert it into the same row in oldTable?

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 11:00:32

you need to capture generated ids against their firstname,lastname in another tenporary table and then use that table to update back the ids

you can use OUTPUT clause to capture generated ids

see an example code below

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tiwas
Starting Member

37 Posts

Posted - 2012-02-02 : 13:31:46

To be honest, I didn't really understand that at all. :S

Isn't it possible to do some foreach-magic? I've added a column for id to the imported db2 table. If I could just do something like
foreach (@id in select col_id from table) {
select * from table where col_id = @id
-- insert what I need into the users table, get user_col_id
-- insert into other tables using the user_col_id value
-- insert user_col_id value into the original db
}

Isn't this possible in sql?

tiwas
Starting Member

37 Posts

Posted - 2012-02-02 : 13:35:33

I might be able to use something like this? -> http://weblogs.asp.net/jgalloway/archive/2006/04/12/442618.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 13:36:12

the solution you're proposing does row by row iteration which will not perform as much as set based solution i gave you.
suggest you to first have a look at OUTPUT caluse to understand how it works

http://msdn.microsoft.com/en-us/library/ms177564.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-02 : 14:25:53

SSIS has facilities to do transformations of this nature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources