Mass insert into Parent to cause mass insert into child table

Question

Post reply

Mass insert into Parent to cause mass insert into child table

Coriolan

SSCommitted

Points: 1931
More actions
January 31, 2008 at 5:58 pm

#178743

I have a parent table T1 (KeyID int (system generated), Asset ID int, Asset Tag int, ....)
Another child table T2 contains ( ParentKeyID, Asset ID, Asset Tag, Comments, Action Items, Status etc...)
I am doing a mass insert to T1
insert into T1
select * from some excel spreadsheet
I would like to write a trigger ??? to cause mass insert into T2 as well.
So if T1 data looks like below with the mass insert
KeyID Asset ID Asset Tag ......
_-------------------------------------
23 34792 SRXeru
24 67897 GRE8976
25 900 70AVCD
T2 will also have
parentKeyID Asset ID Asset Tag Comments Action Items
_-----------------------------------------------------------------
23 34792 SRXeru ......
24 67897 GRE8976
25 900 70AVCD
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

Why not do one more insert into T2 table?

insert into T2

select * from some excel spreadsheet (what ever columns needed for T2).

Coriolan SSCommitted Points: 1931 More actions · Answer 2

The child table has a foreign key ( which is the KeyID system generated on T1).

The KeyID will get populated only after the first mass insert is completed and cannot be read in from the excel file.

Thanks

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Ops...Just realized your KeyID is system generated.

After T1 is loaded, may try to select all the newly inserted KeyID and save to a column within the excel spreadsheet. Then load all the columns needed to the T2 table.

Hope this works for your case. I see your parent table to child table is 1:1.

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

I'd read up on the virtual tables "inserted" and "deleted" that are available to you in triggers.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Secondary thought - use an INSTEAD OF trigger, and leverage the OUTPUT TO clause of the insert.

So - your trigger would look something like:

INSERT T1

OUTPUT INSERTED.* into T2

Select * from myTable

INSERTED will be sporting the new Identity values created, and will have them for inserting into T2

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 6

What about using the OUTPUT clause. That will capture the generated fields. This script assumes the ID field in the t1 table is

CREATE TABLE #temptable

(id INT, val VARCHAR(50))

INSERT INTO t1

(val)

OUTPUT INTO #temptable

(inserted.id

,inserted.val)

--either method

--SELECT val FROM t3

--VALUES('x')

INSRET INTO t3

(id)

SELECT id FROM #temptable

Obviously you can mix & match and figure out what works best for you. I used a temp table because we were calling a series of insert procedures from a wrapper proc that created the temp table for all the other procedures to use.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Coriolan SSCommitted Points: 1931 More actions · Answer 7

Thanks,

But when I coded OUTPUT INSERTED.KEYID,

INSERTED.AssetID

into T2

I got this error.

The target table 'T2' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint ...I will have to play with it some more.

Coriolan SSCommitted Points: 1931 More actions · Answer 8

Thanks to everyone, especially Matt, Grant and Jeremy.

The OUTPUT works.