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

Using 2 TVPs in a stored proc to insert into 2 related tables Expand / Collapse
Author
Message
Posted Thursday, July 23, 2009 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 44, Visits: 486
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
Post #758375
Posted Thursday, July 23, 2009 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 44, Visits: 486
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.
Post #758392
Posted Thursday, July 23, 2009 12:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:50 PM
Points: 292, Visits: 1,623
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?
Post #758499
Posted Thursday, July 23, 2009 12:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 44, Visits: 486
Is there an easier way to do it?
Post #758516
Posted Thursday, July 30, 2009 5:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:23 AM
Points: 14, Visits: 200
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
Post #762318
Posted Thursday, July 30, 2009 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 44, Visits: 486
Hi guys

Many thanks for your responses...I solved it now by putting the properties into the first table as XML.
Post #762321
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse