INSERT same GUID into two tables

Question

I've searched for a solution to this, and found several examples, but none have been exactly right for my scenario.

I've got a [source] table, and two destination tables [table1] and [table2]. [table1] and [table2] both have an Id column (UniqueIdentifier). All columns in [table1] have default values, including the UniqueIdentifier.

[table1] is a parent to [table2], and contains date-time data to track when records were inserted, modified, deleted. [table2] is a detail table, describing records in [table1].

I need to SELECT all from [source], and INSERT into [table1] and [table2], retaining Id generating during [table1] insert, so I can insert this same Id into [table2] along with the data selected from [source].

[source]
col1, col2, col3, col4 <------This data needs to be inserted into [table2]

[table1]
Id*, date-created, date-modified, to-date <-------these all have default values

[table2]
Id*, name, description, category <-------Id generated in [table1] needs to match here

Hopefully I made this clear. Let me know if you need more details. Thanks!

It seems like you need to create a Foreign Key in the second table, linked to that GUID in the first (master) table — Alexander Bell, May 10 '13 at 16:12
The Foreign Key exists already, but I'm still not sure how the SELECT | INSERT would be implemented. Would you happen to have an example of that? — Jonathan, May 10 '13 at 16:37

score 0 · Answer 1 · answered May 10 '13 at 16:12

0

You may be able to use the t-sql OUPUT clause to accomplish this. The difficult part will be relating the newly inserted table1 record back to the source so you can insert into table2. It depends if you are doing this one record at a time or by batch.

http://msdn.microsoft.com/en-us/library/ms177564.aspx

answered May 10 '13 at 16:12

DCNYAM

11,966
8
53
70

I played around with OUTPUT for a while, using several different examples and after reading the MSDN article. I must be completely missing the boat on how to use OUTPUT - completely unsuccessful. Can you provide an example that solves my problem? – Jonathan May 10 '13 at 16:39

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

I've been able to solve my problem after reading https://stackoverflow.com/a/3712735/2370655

CREATE PROCEDURE Import_ManagedEntity_Insert 
@TypeId int,
@CategoryId int

AS
BEGIN

SET NOCOUNT ON;

MERGE INTO ManagedEntityDetail AS med
USING z_test AS source_table
ON 1=0
WHEN not matched THEN INSERT (Id, QualifiedName, DisplayName, Description, CategoryId)
VALUES (newid(), source_table.Url, source_table.Name, source_table.Description, @categoryid)
OUTPUT inserted.id, @typeid, getutcdate(), getutcdate(), 'system', null
INTO ManagedEntity;

END
GO

Thanks for your responses.

edited May 23 '17 at 11:49

Community

1
1

answered May 11 '13 at 07:11

Jonathan

311
3
10

There's also a [question dedicated to the technique itself](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id "Using merge..output to get mapping between source.id and target.id"). – Andriy M May 11 '13 at 21:33

INSERT same GUID into two tables

2 Answers2