Inserting mulitple rows from 1 table into multiple joined tables w/ identity

Question

Post reply

Inserting mulitple rows from 1 table into multiple joined tables w/ identity

TaoEntropy

SSC Rookie

Points: 30
More actions
September 4, 2008 at 10:13 am

#192463

Hello!
I'm a T-SQL stored procedure novice and I got all proud of myself when I wrote this script until I realized it doesn't really work!
Here it is:
____________________________________________________
use mydatabase
DECLARE @tbl1ID int
INSERT Into DestinationTable1
(fname, lname)
SELECT
fname, lname
FROM SourceTable
WHERE SourceTableId = '97854' or SourceTableId = '262488'
Set @tbl1ID = @@IDENTITY
INSERT INTO DestinationTable2
(SourceTableId, ClientID)
VALUES(@tbl1ID, '26')
_______________________________________________
Here's the scenario:
I have a client that has an excel table (SourceTable). She wants it added into a normalized database that contains mulitple joined, related tables. These tables are related using the SourceTableId.
So... What I'm trying to do is to insert the first row from the SourceTable into the DestinationTable1 which has an auto incremented primary key id. Next, I want to get the identity of the primary key for row 1 in DestinationTable1 and insert that primary key into DestinationTable2 with a pre-specified ClientId. After that is inserted, I want the process to begin again with row 2 and so forth until the entire SourceTable is written to Destination Tables 1 and 2.
My code inserts all the rows into DestinationTable1 but only enters the last Indentity primary key id of the bottom row DestinationTable2.
I think I need some sort of row by row looping function like "For Each".
Can anyone help?
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

try to look up the OUTPUT command in BOL

This will help you do the inserts without having to do a loop.

By the way to loop in SQL you can use a cursor(which I don't like) and the while command

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

TaoEntropy SSC Rookie Points: 30 More actions · Answer 2

Ok, thanks Chris.

I looked it up. Lemmie see if I can figure out how to make it work with the code I've got. Thanks.

TaoEntropy SSC Rookie Points: 30 More actions · Answer 3

That totally works! Thanks muy mucho!

Here's the code for anyone else with the same task:

_____________________________________________

use mydatabase

INSERT Into DestinationTable1

(fname, lname)

OUTPUT INSERTED.SourceTableId, '26'

INTO DestinationTable2

(SourceTableId, ClientID)

SELECT

fname, lname

FROM SourceTable

WHERE SourceTableId = '97854' or SourceTableId = '262488'

_______________________________________________________________

TaoEntropy SSC Rookie Points: 30 More actions · Answer 4

Damn! I'm having trouble ammending this statement to insert into an additional DestinationTable3

How might I OUTPUT INSERTED INTO 2 tables?

Glen SSCertifiable Points: 6727 More actions · Answer 5

1. OUTPUT INSERTED INTO temptable.

2. Create a trigger.

TaoEntropy SSC Rookie Points: 30 More actions · Answer 6

Ok, I'm looking up temporary tables and triggers. Let's see if I can apply these concepts. 🙂

TaoEntropy SSC Rookie Points: 30 More actions · Answer 7

Ok I'm getting this error:

Cannot create trigger on a temporary object.

Glen SSCertifiable Points: 6727 More actions · Answer 8

These are two distinct solutions I suggested.

Either use OUTPUT and INSERT OUTPUT to a temp table and then process the data inserted to a temp table to insert in any number of tables

OR

Create a trigger on your DestinationTable1. This trigger will capture inserted data and then insert in the lsit of other tables.

TaoEntropy SSC Rookie Points: 30 More actions · Answer 9

I tried it with a regular table and It gives me:

The target table 'BatchInsertTemp7' of the OUTPUT INTO clause cannot have any enabled triggers.

I remember reading in the Insert Output into about this rule.

::Sigh::

Is there really no way to just include an additional table into the Insert Output Into statement?

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 10

Is all this data coming from a single sheet?

If so do the following...

Recreate the flat table in SQL but add an Identity Column to it (Your PK for your base table).

Insert your data into the flat table

SET IDENTITY_INSERT on your base table

Insert your data into the base table

reset Identity_Insert

Insert data into your child tables

Delete the flat table if you wish.

Gary Johnson
Sr Database Engineer

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 11

OK if you create a temp table then do the first output into that temp table then you all the data in there to use to input into as many other tables as you want. 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

TaoEntropy SSC Rookie Points: 30 More actions · Answer 12

I suppose that's true! I can manually export the contents of the temp table to my 2 other tables because the keydata indentity of the 1st table will be saved in the temp table.