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

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](http://stackoverflow.com/a/10950418/1225845) 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, Oct 28 '14 at 12:58
Yes, that did the trick! Thanks a lot. I had never used the MERGE command before... — Daniel Hillebrand, Oct 28 '14 at 13:39

score 0 · Answer 1 · answered Mar 13 '15 at 14:49

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

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

1 Answers1