July 23, 2009 at 9:50 am
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:
InsertDataSourceCode 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
July 23, 2009 at 10:02 am
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.
July 23, 2009 at 12:41 pm
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?
July 23, 2009 at 12:52 pm
Is there an easier way to do it?
July 30, 2009 at 5:36 am
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
July 30, 2009 at 5:40 am
Hi guys
Many thanks for your responses...I solved it now by putting the properties into the first table as XML.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply