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.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-06-07 : 13:34:13
|
| I need to transfer several hundred rows of data from a table in an old database (tableA) into two tables in the new database (tableB and tableC, some normalization is occurring). When I insert into the tableB, an integer identity value is created there, and tableC needs that identity value to insert it into the corresponding row with the other data. Is there a way to do this without using a cursor to insert one row at a time (which could use SCOPE_IDENTITY)? If this is too confusing I can post some sample table code. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-07 : 13:48:35
|
| Isolate the environment, get the max(ID)as Max1, do an INSERT into tableB, get the max(Id) as MAx2 again, do an insert into tableC with SELECT also including the IDs between MAx1 and MAx2.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-07 : 13:52:29
|
| I thought about that but I wasn't sure if there was an easier way. Thanks Dinakar. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-07 : 14:04:55
|
| It is a batch insert ==> no looping. you have 2 tables so it has to be at least 2 INSERTs. ain't that enough?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-07 : 14:14:40
|
| The inserts are no problem, the difficult part for me would be isolating everything (I'm terrible at transactions/locks) and making sure the other columns in tableC (i.e. those besides the foreign key to tableB) have the data that correlates to the row referred to be the foreign key. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-07 : 14:22:05
|
| Whan I said Isolate the enviroment, I meant make sure no one else is doing the insert. Because your second SELECT will include records from a range of values you want to be sure all those records are from your first INSERT and not from any other user.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-06-07 : 14:56:39
|
| Here is the key (pun indended!) question: Does your old table have a true primary key in the data? If so, it is very easy to do.Also, in addition to answering that question, it would be great if you gave us a very simplified example; use provide an Old table with some sample rows, and the definitions of two New tables and let us know how it should look when done. Just include the relevant primary key columns.The overall technique is simple:step 1: insert rows into the first new table step 2: you simply join the old data to the newly inserted data to bring back the generated primary key, and insert those results into the second new table.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
insert multiple rows into multiple tables