Set Based Solution

  • Does anyone know of a set based T-SQL solution to insert data into a parent table and related data in another table at the same time? The problem is not knowing the primary key values to link the data in related table. Below is a simplified version of what I'm trying to do. The temp table represents data gathered from a query from other tables in the database.

    I have searched high and low for a solution but have not found anything. Any help is appreciated.

    CREATE TABLE Parent

    (

    ID INT IDENTITY(1,1),

    Code1 CHAR(1),

    CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)

    )

    CREATE TABLE Related

    (

    ID INT IDENTITY(1,1),

    ParentID INT,

    Code2 CHAR(2),

    CONSTRAINT PK_Related_ID PRIMARY KEY (ID),

    CONSTRAINT FK_Related_ParentID

    FOREIGN KEY(ParentID)

    REFERENCES Parent (ID)

    )

    CREATE TABLE #TmpOther

    (

    Code1 CHAR(1),

    Code2 CHAR(2)

    )

    INSERT #TmpOther VALUES('A','A1')

    INSERT #TmpOther VALUES('D','D2')

    INSERT #TmpOther VALUES('J','J2')

    INSERT #TmpOther VALUES('F','F3')

    INSERT Parent

    SELECT Code1

    FROM #TmpOther

    SELECT * FROM Parent

    -- Set based INSERT for table Related??

    DROP TABLE #TmpOther

    DROP TABLE Related

    DROP TABLE Parent

    /*

    data for table Parent

    ID Code1

    1 A

    2 D

    3 J

    4 F

    Desired data for table Related

    ID ParentID Code2

    1 1 A1

    2 2 D2

    3 3 J2

    4 4 F3

    */

  • Assuming that the codes are unique

    insert into related(ParentId,Code2)

    Select Parent.Id,

    Code2

    from Parent,

    #TmpOther

    where Parent.Code1 = #TmpOther.Code1

    Else you can , apparently , do this using merge , are you on 2008? , you have posted to a 2000 forum.



    Clear Sky SQL
    My Blog[/url]

  • Unfortunately, I'm still on 2000. Also, the codes will not be unique...bummer.

  • cy-513176 (10/9/2009)


    the codes will not be unique...bummer.

    Why not? What's the point of filling up a table with duplicate codes with different IDENTITY values? That goes against the very point of having a surrogate key in the first place.

    I'd say the problem is with the design of your tables because if you make the code unique then the problem will surely be solved.

  • I'd say the problem is with the design of your tables because if you make the code unique then the problem will surely be solved.

    Yes! The problem is with the table design. It if were up to me, I would not even have it. Unfortunately it is beyond my powers at this point in time to change it.

  • In the past, i have solved this sort of issue by having an extra column on the target table to store the source id. That way you have something to join back on, its not ideal but better than a cursor.



    Clear Sky SQL
    My Blog[/url]

  • Do you have to insert duplicates though - even though you can't declare a key?

    Basically the design is unusable - you can insert rows to it but you can never retrieve them because there's no way to know what you've just inserted... unless maybe you take an exclusive table-level lock to prevent anyone else updating it for the duration of the transaction. Or perhaps a bit less drastic, you could use a trigger to return the just-inserted values.

    Can't think of anything else. What a pain to have to work with such rubbish!

  • I have coped with this sort of rubbish in the past by using a temp table and identity insert:

    DECLARE @MaxID int

    SELECT @MaxID = MAX([ID])

    FROM Parent

    SELECT IDENTITY(int) AS [ID]

    ,*

    INTO #temp

    FROM #TmpOther

    SET IDENTITY_INSERT Parent ON

    INSERT INTO Parent([ID], Code1)

    SELECT @MaxID + [ID], Code1

    FROM #temp

    SET IDENTITY_INSERT Parent OFF

    INSERT INTO Related (ParentID, Code2)

    SELECT @MaxID + [ID], Code2

    FROM #temp

    This works well for data conversions but may cause problems if concurrent access is required.

  • Thanks for all of your input. I'm currently negotiating a table re-design, so hopefully I won't have to deal with this. I will take your ideas into consideration if I'm not successful.

  • Hello 500,

    If you cannot negotiate a redesign, I hope this workaround can help:

    -- **** BEGIN PART WE DO NOT HAVE CONTROL OVER ****

    CREATE TABLE Parent

    (

    ID INT IDENTITY(1,1),

    Code1 CHAR(1),

    CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)

    )

    GO

    CREATE TABLE Related

    (

    ID INT IDENTITY(1,1),

    ParentID INT,

    Code2 CHAR(2),

    CONSTRAINT PK_Related_ID PRIMARY KEY (ID),

    CONSTRAINT FK_Related_ParentID

    FOREIGN KEY(ParentID)

    REFERENCES Parent (ID)

    )

    GO

    -- **** END PART WE DO NOT HAVE CONTROL OVER ****

    -- *** BEGIN WORKAROUND SETUP ****

    -- Create work table

    CREATE TABLE TmpOther

    (

    ID INT IDENTITY(1,1),

    Code1 CHAR(1),

    Code2 CHAR(2)

    )

    GO

    -- Add trigger to work table

    CREATE TRIGGER [trTmpOther] ON [dbo].[TmpOther] FOR INSERT

    AS

    BEGIN

    INSERT INTO TmpParent (Code1, TmpID)

    SELECT Code1, ID

    FROM inserted

    INSERT INTO TmpRelated (Code2, TmpID)

    SELECT Code2, ID

    FROM inserted

    END

    GO

    -- Create surrogate parent table

    CREATE TABLE TmpParent

    (

    ID INT IDENTITY(1,1),

    Code1 CHAR(1),

    TmpID INT,

    CONSTRAINT PK_TmpParent_ID PRIMARY KEY (ID)

    )

    GO

    -- Create surrogate related table without foreign key constraint

    CREATE TABLE TmpRelated

    (

    ID INT IDENTITY(1,1),

    ParentID INT,

    Code2 CHAR(2),

    TmpID INT,

    CONSTRAINT PK_TmpRelated_ID PRIMARY KEY (ID),

    )

    GO

    -- **** END WORKAROUND SETUP ****

    -- Insert records into work table

    INSERT INTO TmpOther VALUES('A','A1')

    INSERT INTO TmpOther VALUES('D','D2')

    INSERT INTO TmpOther VALUES('J','J2')

    INSERT INTO TmpOther VALUES('F','F3')

    -- Fill in the ParentID column in TmpRelated

    UPDATE TmpRelated

    SET TmpRelated.ParentID = TmpParent.ID

    FROM TmpRelated

    JOIN TmpParent ON TmpRelated.TmpID = TmpParent.TmpID

    -- See what's in our Tmp tables

    SELECT * FROM TmpOther

    SELECT * FROM TmpParent

    SELECT * FROM TmpRelated

    -- **** NOW WE CAN UPDATE OUR PERMANENT TABLES ****

    SET IDENTITY_INSERT Parent ON

    INSERT INTO Parent (ID, Code1)

    SELECT ID, Code1

    FROM TmpParent

    SET IDENTITY_INSERT Parent OFF

    SET IDENTITY_INSERT Related ON

    INSERT INTO Related (ID, ParentID, Code2)

    SELECT ID, ParentID, Code2

    FROM TmpRelated

    SET IDENTITY_INSERT Related OFF

    -- See what we ended up with

    SELECT * FROM Parent

    SELECT * FROM Related

    -- Clean up

    DROP TRIGGER trTmpOther

    DROP TABLE TmpOther

    DROP TABLE TmpParent

    DROP TABLE TmpRelated

    DROP TABLE Related

    DROP TABLE Parent

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply