Multiple table Set-based insert with identity column in table1 and foreign key in table2 [duplicate]

Question

I am writing an Application that imports configuration data from multiple external systems. To decouple the user-data from imported data y am using an intermediate table. The following tables are simplified for the sake of the argument.

table [connection] (generic connection information referenced by other tables not included)
table [importConfig] (Connection configuration from one of the external systems)
table [connectionImportConfig] (intermediate table, linking [connection] and [importConfig]

For each connection in [importConfig] I want to create a row in [connection] with an identity column. Then I want to insert the new IDs from [connection] together with the identifier from [importConfig] in [connectionImportConfig].

Constraint: I don't want to use a cursor. Must be a set-based solution.

The examples I have found on stackoverflow are either not set-based or I found them not applicable for other reasons.

T-SQL Insert into multiple linked tables using a condition and without using a cursor (not valid for multiple rows, using @@IDENTITY)
How to perform INSERT/UPDATE to Linking (Join) table which has FKs to IDENTITY PKs (not valid for multiple rows. Not telling how to join the identity values with the original data I want to insert)

I have tried a lot and am stuck at the point where I have to insert the new IDs into connectionImportConfig

-- Test tables&data (stripped down version) CREATE TABLE connection (ConnectionID int identity(1,1) NOT NULL, comment nvarchar(max) null) CREATE TABLE connectionImportConfig(connectionID int NOT NULL, ConfigCode nvarchar(50) NOT NULL) CREATE TABLE importConfig (ConfigCode nvarchar(50) NOT NULL) DECLARE @MyConnection table (ConnectionID int not null); insert into importConfig values ('a') insert into importConfig values ('b') insert into importConfig values ('c') -- Insert into PK-table creating the IDs INSERT INTO connection (comment) OUTPUT INSERTED.ConnectionID INTO @MyConnection SELECT * from importConfig -- How do I insert the new IDs together with the ConfigCode? 1 has to be replaced with the ID. -- JOIN doesn't seem to work because there is no join condition to use INSERT INTO connectionImportConfig (connectionID, ConfigCode) SELECT 1, ConfigCode FROM ImportConfig select * from @MyConnection select * from connectionImportConfig -- Cleanup DROP TABLE importConfig; DROP TABLE connection; DROP table connectionImportConfig;

Have you read this answer on using the MERGE command? The OUTPUT functionality can load data into one table, then add that table's identity to the existing data and insert it into another table. I've had it favorited for a while now, and used it happily on several bridge tables. — AHiggins
– AHiggins, Commented Oct 28, 2014 at 12:58
Yes, that did the trick! Thanks a lot. I had never used the MERGE command before... — Daniel Hillebrand
– Daniel Hillebrand, Commented Oct 28, 2014 at 13:39

Matt · Accepted Answer · 2015-03-13 14:49:52Z

Have a look at this. I am sure you could loosely modify this to your needs. I don't typically like inserting right off the output but it really depends on your data. Hope this example helps.

IF OBJECT_ID('tempdb..#ImportConfig') IS NOT NULL DROP TABLE #ImportConfig IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config IF OBJECT_ID('tempdb..#Connection') IS NOT NULL DROP TABLE #Connection GO CREATE TABLE #ImportConfig (ImportConfigID INT PRIMARY KEY IDENTITY(1000,1), ImportConfigMeta VARCHAR(25)) CREATE TABLE #Config (ConfigID INT PRIMARY KEY IDENTITY(2000,1), ImportConfigID INT, ConfigMeta VARCHAR(25)) CREATE TABLE #Connection (ConnectionID INT PRIMARY KEY IDENTITY(3000,1), ConfigID INT, ConnectionString VARCHAR(50)) INSERT INTO #ImportConfig (ImportConfigMeta) VALUES ('IMPORT_ConfigMeta1'),('IMPORT_ConfigMeta2') ;MERGE INTO #Config AS T USING #ImportConfig AS S ON T.ConfigID = S.ImportConfigID WHEN NOT MATCHED THEN INSERT (ImportConfigID, ConfigMeta) VALUES ( S.ImportConfigID, REPLACE(S.ImportConfigMeta,'IMPORT_','') ) OUTPUT INSERTED.ConfigID, 'CONNECTION_STRING: ' + INSERTED.ConfigMeta INTO #Connection; SELECT 'IMPORT CONFIG' AS TableName, * FROM #ImportConfig SELECT 'CONFIG' AS TableName, * FROM #Config SELECT 'CONNECTION' AS TableName, * FROM #Connection

Collectives™ on Stack Overflow

Multiple table Set-based insert with identity column in table1 and foreign key in table2 [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related