Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting into a self-referencing table using an identity int


Inserting into a self-referencing table using an identity int

Author
Message
JKSQL
JKSQL
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 683
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]



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search