CODEWITHSUNDEEP
sqlsql-servert-sql
Measel
Measel

Reputation: 41

Insert row using a SELECT, and update the source table row with the target new row Id

I have a table SourceTable and I want to copy some of its data into TargetTable. TargetTable is an existing table with existing rows, and an identity id.

On SourceTable, there is a column that is a foreign key to TargetTable, e.g. SouceTable.TargetTableId

What I need to do is insert the rows from SourceTable into TargetTable, and then update the SourceTable.TargetTableId with the newly created Id from TargetTable.

I cannot do an insert and then a subsequent update by matching on multiple columns as the rows are not unique enough.

The insert needs to "insert into new table and update existing table's column that is a foreign key to the new table with the new row's id".

CREATE TABLE #SourceTable
(
    Id INT IDENTITY(1,1),
    Field1 NVARCHAR(MAX),
    Field2 NVARCHAR(MAX),
    TargetTableId INT
)

CREATE TABLE #TargetTable
(
    Id INT IDENTITY(245,1),
    Field1 NVARCHAR(MAX),
    Field2 NVARCHAR(MAX),
)

INSERT INTO #SourceTable(Field1, Field2)
VALUES
('Apple','Pie'),
('Chocolate','Cake'),
('Apple','Pie')

INSERT INTO #TargetTable(Field1, Field2)
SELECT Field1, Field2
FROM #SourceTable

Desired Result on SourceTable:

Id - Field1 - Field2 - TargetTableId
1 - Apple - Pie - 245
2 - Chocolate - Cake - 246
3 - Apple - Pie - 247

Upvotes: 2

Views: 1792

Answers (2)

Adamszsz
Adamszsz

Reputation: 581

You can do something like this as you needed.

  • just create temp table #TargetTableForInsert
  • insert this data too into this table with added ID of SourceTable
  • and finally update your main table based on TargetTableForInsert

Here you are :)

CREATE TABLE #SourceTable
(
    Id INT IDENTITY(1,1),
    Field1 NVARCHAR(MAX),
    Field2 NVARCHAR(MAX),
    TargetTableId INT
)

CREATE TABLE #TargetTable
(
    Id INT IDENTITY(245,1),
    Field1 NVARCHAR(MAX),
    Field2 NVARCHAR(MAX)
)


CREATE TABLE #TargetTableForInsert
(
    Id INT IDENTITY(245,1),
    Field1 NVARCHAR(MAX),
    Field2 NVARCHAR(MAX),
    SourceTableId INT
)


INSERT INTO #SourceTable(Field1, Field2)
VALUES
('Apple','Pie'),
('Chocolate','Cake'),
('Apple','Pie')

INSERT INTO #TargetTable(Field1, Field2)
SELECT Field1, Field2 
FROM #SourceTable

INSERT INTO #TargetTableForInsert(Field1, Field2, SourceTableId)
SELECT Field1, Field2 ,Id
FROM #SourceTable


update #SourceTable
set TargetTableId =  ST.Id
FROM #SourceTable ST
     join #TargetTableForInsert TG on ST.id = TG.SourceTableId





select 'SourceTable',* from #SourceTable

select 'SourceTable',* from #TargetTable

Upvotes: 0

Thom A
Thom A

Reputation: 95564

Seems like you need to use the old MERGE method to OUTPUT values from both the source data and the destination. Then you can put that data into a table variable/temporary table and use that to perform your UPDATE:

MERGE #TargetTable TT
USING #SourceTable ST ON ST.TargetTableId = TT.ID
WHEN NOT MATCHED THEN
    INSERT (Field1,Field2)
    VALUES(ST.Field1, ST.Field2)
OUTPUT ST.ID, inserted.ID INTO @IDs;

UPDATE ST
SET TargetTableId = I.TargetID
FROM #SourceTable ST
     JOIN @IDs I ON ST.Id = I.SourceID;
GO

SELECT *
FROM #SourceTable;

Upvotes: 1

Related Questions