Consider this a follow-up to BULK INSERT: Trials and Tribulations
I have the following
VIEW defined and would like to insert into the multiple underlying tables when sending a
BULK INSERT from a CSV.
CREATE VIEW dbo.StagingViewB as SELECT Q.[SQSQueue], Q.[SQSMessageID], D.[ReceiverID], Q.[DLOrder], D.[DetectionDate], D.[DetectionMS], D.[TagID], M.[TxAmplitude], M.[TxOffset], M.[TxNBW], M.[TxChkSum] FROM [StQueue] as Q INNER JOIN [StDetection] as D ON Q.eID=D.eID INNER JOIN [StDetMeta] as M ON Q.eID=D.eID WITH VIEW_METADATA
I want an underlying autoincrement primary key (eID) to tie the 3 underlying tables together. In other words, this is the StQueue DDL
CREATE TABLE [dbo].[StQueue]( [eID] [bigint] IDENTITY(1,1) NOT NULL, [SQSQueue] [varchar](25) NULL, [SQSMessageID] [varchar](50) NULL, [DLOrder] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [eID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
D aliased tables all have
eID, but I'm willing to remove the
IDENTITY(1,1) part of the definition (on all except one), as I don't think it's wise to trust that three separate tables will remain synchronous with respect to the value of eID for the next insert.
To avoid this issue, should I do a
SET @ID = COALESCE(IDENT_CURRENT('StQueue'),0)+1
SET @ID = SELECT MAX(eID) FROM StQueue SET @ID = COALESCE(ID,0)+1
And then do
INSERT INTO StQueue ([SQSQueue], [SQSMessageID],[DLOrder]) VALUES (SQSQueue, SQSMessageID, DLOrder) From Inserted INSERT INTO StDetection (eID, ReceiverID, DetectionDate, DetectionMS, TagID) VALUES (@ID, ReceiverID, DetectionDate, DetectionMS, TagID) FROM Inserted INSERT INTO StDetMeta (eID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum]) VALUES (@ID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum]) FROM Inserted
Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.
I assume this warning applies equally to the
MAX-based method. Should I
INSERT...WITH TABLOCK on the underlying tables to avoid that issue? Or would the multi-session/thread issue still be a problem. The problem might still exist because the computation for the next ID number happens before attempting to grab a lock, discovering it's already locked, and spinning wheels until the existing lock is released, the value would be stale any time a lock is encountered or a race exists where two threads are trying to grab the attention of the table (establish a lock) at the same time? Is there a way I can request a lock for all three tables at once? Would this be wise to do before grabbing
Will this whole process be slower than
BULK INSERTing and then
SELECT eID,x,y,z from a single master table into the 3 "child" tables? Would iterating through a
CURSOR be worse?