Unanswered: Bulky Insert in subtype - super type tables
Consider the following tables:
CREATE TABLE [dbo].[tblInteraction] (
[IntID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ContactID] [int] NULL ,
[PropID] [int] NULL ,
[RecordCreator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[LastUpBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblInteractionMktg] (
[IntID] [int] NOT NULL ,
[MailerID] [int] NULL ,
[CampID] [int] NULL
) ON [PRIMARY]
tblInteractionMktg is a subtype for tblInteraction.
I have a temp table (#Temp [ContactID] [ContactName]) and I need to do a bulk insert into tblInteractionMktg. The problem is that since tblInteractionMktg is a subtype of tblInteraction the primary key for both table is the same, however the pk is assigned in the supertype and since it is an auto number, in order for me to do a bulk insert I need to first enter data in the supertype to get the pk values and then insert the data in the subtype.
In my case, first I need to insert all ContactIDís from the temp table to tblInteraction, then get the IDís for these people and insert those Idís and values for MailerID and CampID to the tblInteractionMktg.
My problem is getting the IntIDís from the supertype. The solution that I can think of is to have a stored procedure and use two variables for the iden_current and @@identity for the supertype table, this way before I do an insert I save the last identity value and I retrive the last one after insert using @@identity function then I can get all IntIDís that are between the identity values before and after insert, select them and insert them into the subtype. But what if another users is inserting at the same time?
The other solution is to loop through records and to an insert for each subtype and supertype one record at a time and retrive the IDís using @@identity which is much slower but probably safer.
Am I on the right track or there is something wrong with my table design and or stored procedure ideas?
The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.