user584018 user584018 - 2 months ago 14
SQL Question

master child table entries from single source table

I have below 3 tables "#tblData", "#tblMaster" and "#tblChild", where I need to split "#tblData" into 2 other tables "#tblMaster" and "#tblChild".

In "#tblChild" table, I need Identity Key from "#tblMaster" along with other data from "#tblData" table.

Create Table #tblData (Id int, UniqueKey VARCHAR(10), DateTimeNow DATETIME, UpdateBy VARCHAR(10))
insert into #tblData values (1, 'uq1', getdate(), 'abc'), (2, 'uq2', getdate()+1, 'xyz')
--select * from #tblData
create Table #tblMaster (MasterId INT IDENTITY(100,1), DateTimeNow DATETIME, UpdateBy VARCHAR(10))
create Table #tblChild (ChildId INT IDENTITY(10,1), MasterId INT, UniqueKey VARCHAR(10), DateTimeNow DATETIME)


I am trying with MERG, but not able to insert identity value from "#tblMaster" to #tblChild".

Below cursor solves my issue, but looking for better replacement,

Create Table #tblData (Id int, UniqueKey VARCHAR(10), DateTimeNow DATETIME, UpdateBy VARCHAR(10))
insert into #tblData values (1, 'uq1', getdate(), 'abc'), (2, 'uq2', getdate()+1, 'xyz')
--select * from #tblData
create Table #tblMaster (MasterId INT IDENTITY(100,1), DateTimeNow DATETIME, UpdateBy VARCHAR(10))
create Table #tblChild (ChildId INT IDENTITY(10,1), MasterId INT, UniqueKey VARCHAR(10), DateTimeNow DATETIME)

declare @id int
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD FOR
SELECT Id FROM #tblData
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
declare @UniqueKey VARCHAR(10) declare @DateTimeNow datetime
declare @iIdentity int
create table #OutputTbl (Id Int, UniqueKey VARCHAR(10), DateTimeNow DATETIME)

select @UniqueKey = UniqueKey, @DateTimeNow = DateTimeNow from #tblData where Id = @id

INSERT INTO #tblMaster(DateTimeNow, UpdateBy) SELECT DateTimeNow, UpdateBy FROM #tblData where Id = @id
SELECT @iIdentity=SCOPE_IDENTITY()

INSERT INTO #OutputTbl values (@iIdentity, @UniqueKey, @DateTimeNow)
select * from #OutputTbl
drop table #OutputTbl

FETCH NEXT FROM @MyCursor INTO @id
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

drop table #tblChild
drop table #tblMaster
drop table #tblData

Answer

In this simple case there is no need for cursor.

You can use MERGE together with OUTPUT clause. OUTPUT clause in MERGE allows to access columns from both source and destination tables.

Sample data

Create Table #tblData (Id int, UniqueKey VARCHAR(10), DateTimeNow DATETIME, UpdateBy VARCHAR(10));
insert into #tblData values (1, 'uq1', getdate(), 'abc'), (2, 'uq2', getdate()+1, 'xyz');

create Table #tblMaster (MasterId INT IDENTITY(100,1), DateTimeNow DATETIME, UpdateBy VARCHAR(10));
create Table #tblChild (ChildId INT IDENTITY(10,1), MasterId INT, UniqueKey VARCHAR(10), DateTimeNow DATETIME);

Query

MERGE can insert, update and delete, but we need only simple insert, so the join criteria is always false (1=0).

MERGE INTO #tblMaster AS Dest
USING
(
    SELECT UniqueKey, DateTimeNow, UpdateBy
    FROM #tblData
) AS Src
ON (1 = 0)
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (DateTimeNow
    ,UpdateBy)
VALUES
    (Src.DateTimeNow
    ,Src.UpdateBy)
OUTPUT inserted.MasterId, Src.UniqueKey, Src.DateTimeNow
INTO #tblChild(MasterId, UniqueKey, DateTimeNow)
;

Check and clean up

select * from #tblData;
select * from #tblMaster;
select * from #tblChild;

DROP TABLE #tblData;
DROP TABLE #tblMaster;
DROP TABLE #tblChild;
Comments