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 |
|
sujosh
Yak Posting Veteran
55 Posts |
 Posted - 2005-07-02 : 15:09:21
|
| I want to retrieve 1000 rows from T1 and insert one record into table T2 and one or more records into table T3, with the latter containing the PK of the row you just added to T2.Can someone please tell me how I can do this using a SP?Thanks |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-07-02 : 15:46:04
|
| Is there a natural key for the data you are inserting into T2? If so, just INNER JOIN T1 to T2 for the INSERT into T3. Join on the natural key and carry the "PK" over to T3.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2005-07-02 : 16:29:48
|
| When you say natural key do you mean a identity column? T1/T2/T3 all have identity key as the PK. T2's PK is a FK in T3. Actually I am not sure how I can do this in my SP?Here is what I want to do.1) There is table T1. There are 1000 rows.2) A row of T1 has 10 columns. 3) I have to insert into T2 with 5 columns and take that PK and insert into T3 as a FK and insert the remaining 5 columns from T1.I am looking for how the SP should like. Can you please give me a place to start? Like an outline how my SP shoudl be? Should I need a WHile Loop or how should I go about?Any help is appreciated!Thanks |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-07-03 : 14:25:33
|
| http://solidqualitylearning.com/blogs/dejan/archive/2005/02/24/434.aspxYou need to answer the question of whether there's a natural key on the table or not. Otherwise, you might be looking at a loop, so you can capture each of the SCOPE_IDENTITY() values (you can look this up in Books Online) as they are populated.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
sujosh
Yak Posting Veteran
55 Posts |
Posted - 2005-07-04 : 01:53:27
|
| Derrickleggett,the way I got this done is creating a in-memory table and using a while loop and things are working great! BTW I it was a good article Thanks! |
|
|
|
|
|
|
Inserting data into multiple tables