Looping Inserts - SQL Server Forums

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)

Looping Inserts

Author

Topic

d2u
Starting Member

3 Posts

Posted - 2004-04-20 : 13:22:10

I am working on a project and need to insert data from one table in one database to three tables located in another database. I am having problems with the looping part of the insert. I am not familar with the looping syntax/structure in sql. Here is what I have so far:

declare @val1 int
declare @val2 int

insert dbname1.table1
(field1, field2, field3,...)
select field1, field2, field3,...
from dbname2.table1

select @val1 = @@identity

insert dbname1.table2
(field1, field2, field3,...)
select field1, field2, field3,...
from dbname2.table1

select @val2 = @@identity

insert dbname1.table3
(field1, field2)
value (val1, val2)

I need to do this one record at a time and then loop back up to the top and do the whole thing again until there is no more records in the table.

Any help would be great.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-20 : 13:36:12

Why don't you just transfer the data as is to your tables? Just turn the IDENTITY_INSERT ON.
SET IDENTITY_INSERT ON table1

INSERT dbname1.table1 (...)
SELECT ...
FROM dbname2.table1

SET IDENTITY_INSERT OFF table1

SET IDENTITY_INSERT ON table2

...

SET IDENTITY_INSERT OFF table2
...

So just carry over the identity values from your tables in dbname2.

Tara

d2u
Starting Member

3 Posts

Posted - 2004-04-20 : 13:51:47

Okay the problem with that, as I understand Identity_Insert, is that it will move all of the specified data from the source table to the target table and then move on to the next. The problem is I need the last inserted value from table1 and table2 so that I can join them in table3.

Basically I am going from a non-normalized db to a highly normalized db. So all the information that is tracked currently (in one table)is split into two different tables and then is linked back together in a third.

Ex. Person info = table1 Address info = table2 Person_Address = table3.

If Identity_Insert still works then let me know but as I read it, I don't think it would.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-20 : 13:55:43

Ah I didn't realize that you were moving from a different schema. I assumed the schema was the same. Do you have a datetime column in your tables which signifies when the row was inserted? If so, you could use that to determine what values to put into table2 from table1 and also into table3 from table2.

Looping in SQL Server is done with a WHILE.

Tara

d2u
Starting Member

3 Posts

Posted - 2004-04-20 : 14:04:32

Yeah sorry I forgot to add that little bit.

Yes, each table has its own datetime field so I can try that.

Thanks again for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources