Inserting into a self-referencing table using an identity int

  • Currently our application uses guids. We are in the conversion process of making the database ints.

    This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.

    We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:

    1) Insert all the data into the dbo table

    2) Update the parent Id in the table

    3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program

    This is a high transaction table that will have a lot of records (millions). I am looking for a performance tune and potentially better logic to the solution I am providing.

    Any help which points me in the right direction is great and I thank you.

    --Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[JSets](

    [SetsID] [uniqueidentifier] NOT NULL,

    [Sets_Id] bigint Identity (1,1) NOT NULL,

    [ParentSets_ID] [bigInt] NULL,

    [SetName] [nvarchar](256) NULL,

    ) ON [PRIMARY]

    END

    GO

    --TVP that will be passed from code

    IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'JType' AND ss.name = N'dbo')

    DROP TYPE [dbo].[JType]

    GO

    CREATE TYPE [dbo].[JType] AS TABLE(

    [SetsID] [uniqueidentifier] NOT NULL,

    [SetName] [nvarchar](50) NOT NULL,

    [ParentSetsID] [uniqueidentifier] NULL

    )

    GO

    --Population of data for the TVP

    --="SELECT '"&A1&"','"&B1&"', '"&C1&"' UNION ALL"

    DECLARE @JType [dbo].[JType]

    INSERT INTO @JType

    SELECT 'C1FA5E92-F8DE-4496-A389-F59B63B740BE','Set 1', Null UNION ALL

    SELECT '8639CF31-DD79-46C7-9C5B-04A91572BF75','Set 2', 'C1FA5E92-F8DE-4496-A389-F59B63B740BE' UNION ALL

    SELECT 'A12FFEB3-2763-4E82-9DE7-71764F1129E9','Set 3', '8639CF31-DD79-46C7-9C5B-04A91572BF75' UNION ALL

    SELECT '113471E0-8473-46C2-8B88-D1E24E74B130','Set 4', 'A12FFEB3-2763-4E82-9DE7-71764F1129E9' UNION ALL

    SELECT '7EC4577E-9B8A-4D47-A735-6D1889922244','Set 5', Null UNION ALL

    SELECT '1BD14B2D-8897-4532-AAC0-26AD9B764952','Set 6', '7EC4577E-9B8A-4D47-A735-6D1889922244' UNION ALL

    SELECT '5A093978-0EAD-49FE-A477-1D20A92F1D2A','Set 7', '1BD14B2D-8897-4532-AAC0-26AD9B764952' UNION ALL

    SELECT 'BD25412D-5436-4027-A56C-F018E695A33D','Set 8', '5A093978-0EAD-49FE-A477-1D20A92F1D2A' UNION ALL

    SELECT '52BC3557-4439-47C1-8627-B03A9DF0972D','Set 9', '5A093978-0EAD-49FE-A477-1D20A92F1D2A' UNION ALL

    SELECT '52BC3557-4439-47C1-8627-B03A9DF0972E','Set 900', Null UNION ALL

    SELECT '2FAAEAA8-0ADB-4AB4-8165-56F6CD4AF068','Set 10', '5A093978-0EAD-49FE-A477-1D20A92F1D2A'

    --Reseting the table so it can be run over and over using the same ids

    TRUNCATE TABLE [dbo].[JSets]

    -- temp table for getting ids out from bulk insert

    DECLARE @TempSet Table(SetsId UniqueIdentifier, Sets_Id BigInt, ParentSets_Id BigInt )

    --Insert into Prod table

    INSERT INTO [dbo].[JSets]

    ([SetsID],

    [SetName])

    OUTPUT Inserted.SetsID, Inserted.Sets_Id, Null as ParentSets_Id INTO @TempSet

    SELECT [SetsID]

    ,[SetName]

    FROM @JType t

    --Testing not needed but nice to see the data will not be in prod

    SELECT TempSet.SetsId,TempSet.Sets_Id, ParentSet.SetsId, ParentSet.Sets_Id as ParentSets_Id

    FROM @TempSet TempSet

    INNER JOIN @JType Jtype On TempSet.SetsID=Jtype.SetsId

    INNER JOIN @TempSet ParentSet ON Jtype.ParentSetsID=ParentSet.SetsId

    --Setting up CTE for update

    BEGIN

    WITH SetUpdate (Sets_Id,ParentSets_Id)

    AS (

    SELECT TempSet.Sets_Id,ParentSet.Sets_Id

    FROM @TempSet TempSet

    INNER JOIN @JType Jtype On TempSet.SetsID=Jtype.SetsId

    INNER JOIN @TempSet ParentSet ON Jtype.ParentSetsID=ParentSet.SetsId

    )

    --Update to production

    UPDATE JSet

    SET [ParentSets_ID] = SU.ParentSets_Id

    FROM [dbo].[JSets] JSet

    INNER JOIN SetUpdate SU ON JSet.Sets_Id=SU.Sets_Id

    END

    --Verification select

    SELECT *

    FROM [dbo].[JSets]

Viewing 0 posts

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