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


Using 2 TVPs in a stored proc to insert into 2 related tables


Using 2 TVPs in a stored proc to insert into 2 related tables

Author
Message
MrSQLDBA
MrSQLDBA
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 654
Hi all
First post, so please be gentle.

I am creating a stored proc which has 2 table valued parameters which need to go into 2 tables within the database.

The 2nd TVP contains further details about each row in the 1st TVP (upto 4 rows in 2nd TVP per 1 row in 1st TVP). They are linked by the scopeRequestID.

CREATE TYPE [dbo].[udtt_Requests] AS TABLE(
   [ScopeRequestID] [bigint] NOT NULL,
   [DataSourceCode] [uniqueidentifier] NOT NULL,
   [Priority] [tinyint] NOT NULL,
   [RequestXml] [text] NULL,
   [RequestCode] [uniqueidentifier] NOT NULL,
   [TransmitResults] [bit] NOT NULL,
   PRIMARY KEY CLUSTERED
(
   [ScopeRequestID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

CREATE TYPE [dbo].[udtt_RequestProperties] AS TABLE(
   [ScopeRequestID] [bigint] NOT NULL,
   [Name] [varchar](100) NOT NULL,
   [Value] [varchar](4096) NULL,
   [Type] [varchar](100) NOT NULL,
   PRIMARY KEY CLUSTERED
(
   [ScopeRequestID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO



Now my proc needs to read from these tables, and then insert into 2 related tables in the database....

CREATE TABLE [dbo].[RequestQueue](
   [RequestQueueID] [bigint] IDENTITY(1,1) NOT NULL,
   [DataSourceCode] [uniqueidentifier] NOT NULL,
   [RequestGroupID] [bigint] NOT NULL,
   [InsertionDate] [datetime] NOT NULL,
   [Priority] [tinyint] NOT NULL,
   [RequestQueueStatusID] [int] NOT NULL,
   [Started] [datetime] NULL,
   [Completed] [datetime] NULL,
   [RequestXml] [varchar](max) NOT NULL,
   [RequestCode] [uniqueidentifier] NOT NULL,
   [TransmitResults] [bit] NOT NULL,
CONSTRAINT [PK_RequestQueue] PRIMARY KEY CLUSTERED
(
   [RequestQueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[RequestProperty](
   [RequestPropertyID] [bigint] IDENTITY(1,1) NOT NULL,
   [RequestQueueID] [bigint] NOT NULL,
   [Name] [varchar](100) NOT NULL,
   [Value] [varchar](4096) NULL,
   [Type] [varchar](100) NOT NULL,
CONSTRAINT [PK_RequestProperty] PRIMARY KEY CLUSTERED
(
   [RequestPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]




So when I insert into dbo.RequestQueue, I need to store the RequestQueueID identity for each insert and somehow hold it and use it when I insert into the 2nd table..using the RequestQueueID to relate the data in the 2 tables.

I need to do this as fast as possible, so can't really use anything like a cursor.
The proc will probably be running about 3 times a second, with about 100 rows in the first TVP and 2-4 rows in the 2nd TVP per row in the 1st TVP

Also, before I insert into the 1st table, I need to get the MAX(RequestGroupID) from dbo.RequestQueue and add 1 when the proc inserts the next group of records....so eg MAX(RequestGroupID) is 1, so when the stored proc inserts the 100 records from the 1st TVP, they all go in with RequestGroupID of 2.

I wrote this down as notes when I started to look at the proc:

   Insert   DataSourceCode from incoming table1
         RequestGroupID from the next ID from the RequestQueue table
         InsertionDate as GetUTCDate()
         Priority from incoming table1
RequestQueueStatusID as 1
         Started as NULL
         Completed as NULL
         RequestXML from incoming table1
         RequestCode from incoming table1
         TransmitResults from incoming table1
         
   For each row that is inserted, get the SCOPE_IDENTITY() of RequestQueueID
   
   Then get 2nd table and insert into RequestProperty getting each SCOPE_Identity()

If anyone can give me any ideas on the best way to do this, that would be great....at the moment I was just thinking

Insert a row from TVP table 1 into dbo.RequestQueue
Take SCOPE_IDENTITY()
Insert related rows from TVP table 2 into dbo.RequestProperty
Repeat for each row in TVP table 1 but not using a cursor
MrSQLDBA
MrSQLDBA
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 654
One more thing as well....I did think about getting the MAX(RequestQueueID) from the dbo.RequestQueue table before the insert and working out from there...but as there could be more procs running and inserting as well...this could be wrong.
Lamprey13
Lamprey13
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1657
I think you can do what you need to do by using the OUTPUT clause from your first insert. That will allow you to capture the new ID and the associated ScopeRequestID so you can figure out what matched to the Request Properties.

Does it need to be that complicated though?
MrSQLDBA
MrSQLDBA
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 654
Is there an easier way to do it?
SQLEgan
SQLEgan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 232
I agree the OUTPUT clause is the way to go.

Something along these lines should work

Declare @Results Table
(
   RequestQueueID bigint not null,
   ScopeRequestID bigint not null
)


Insert Into dbo.RequestQueue
   Select
   From [dbo].[udtt_Request]
   Output Inserted.RequestQueueID, ScopeRequestID Into @Results
   

Insert Into dbo.RequestProperties
   Select , R.RequestQueueID
   From    [dbo].[udtt_RequestProperties] As RP
      Inner Join @Results As R ON Rp.ScopeRequesID = R.ScopeRequesID
MrSQLDBA
MrSQLDBA
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 654
Hi guys

Many thanks for your responses...I solved it now by putting the properties into the first table as XML.
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