Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    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.


    Propblem :

    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?

    Thanks
    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your problem would be much simpler if you used a GUID (globally unique identifier), as the ID field. The field type is uniqueidentifier. Then you can create these IDs in your temporary table useing the NewID() function prior to inserting them into your permanent tables.

    If you can't change the ID type, then look for a natural key in your tblInteraction table (a combination of fields which are unique, other than an internally generated ID). Then after inserting data into tblInteraction you can link it to your temp table on the natural key to find out what IDs were assigned.

    GUIDS, though long, have other advantages over sequential integer IDs in that they allow you to safely and easily merge data from multiple tables or databases without having to worry about duplicate keys.

    blindman

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply.
    I can't use GUID's cause I'm using tblInteraction for Replication and I though it might create problems for replication stuff since SQL Server uses it own GUID's for replication purposes.
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    While it is true that you can have only one Globally Unique Identifier column per table, you can have as many UniqueIdentifier columns as you want, use them to store GUIDs generated by NewID(), and set them as primary keys.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •