Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting into a self-referencing table using an identity int Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 10:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:05 AM
Points: 421, Visits: 607
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]


Post #1592403
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse