Using TVP to insert rows into 2 tables with 1-1 relationship using Identify column

In this example, there are 2 tables defined as following:

CREATE table Contact

(

      Id INT identity (1,1),
      CustomerId INT ,

      ContactType VARCHAR(5) NOT NULL,

      Value VARCHAR(100) NOT NULL,

      CONSTRAINT ContactPK PRIMARY KEY CLUSTERED (Id),

      CONSTRAINT ContactTypeUK UNIQUE (CustomerId ,ContactType)

)

Create  table ConatctLog

(

      Id int identity (1,1),

      ContactId INT,

      VerificationMessage VARCHAR(100) ,

      CONSTRAINT ConatctLogPK PRIMARY KEY CLUSTERED (Id),  

      CONSTRAINT ConatctLogContactFK FOREIGN KEY  (ContactId) REFERENCES Contact (Id)

)

What we want to do is to pass a TVP paramater into a stored procedure to insert rows in both tables.

Let’s get thing started. First of all, we need to have a Table Type defiend which is capable to hold all needed columns to insert rows in both tables.  With that we have this ContactWithTableType defined

CREATE   TYPE ContactWithLogTableType AS TABLE

(

      CustomerId INT ,
      ContactType VARCHAR(5) NOT NULL,

      Value VARCHAR(100) NOT NULL,     

      VerificationMessage VARCHAR(100) not null

)

Now, let’s get into some real fun. Let’s write a stored procedure to do what we want to do:

CREATE PROC UspInsertContacts

 @ContactWithLog ContactWithLogTableType  READONLY

 AS

 BEGIN

 DECLARE @MyInsertedContact table

(

      ContactId INT ,

      ContactType VARCHAR(5)

      );

     INSERT  INTO

            Contact (CustomerId , ContactType, Value)

       OUTPUT INSERTED.id, INSERTED.ContactType

       INTO @MyInsertedContact

     SELECT
         CustomerId ,

         ContactType,

         Value

     FROM  

         @ContactWithLog

        

    INSERT INTO    ConatctLog (  ContactId,  VerificationMessage)

    SELECT

            IC.ContactId,

            C.VerificationMessage

    FROM

            @MyInsertedContact IC

            join @ContactWithLog C ON IC.ContactType = C.ContactType

           

END

Go

Okay, so far so good. Let’s write some scripts to test the stored procerure we just wrote:

DECLARE @ContactWithLog ContactWithLogTableType

INSERT INTO @ContactWithLog(ContactType,Value, VerificationMessage) VALUES(1, 'Email','Test@xyz.com', 'valid address 1')

INSERT INTO @ContactWithLog(ContactType,Value, VerificationMessage) VALUES(1, 'Fax','123-456-3454', 'valid address 2')

INSERT INTO @ContactWithLog(ContactType,Value, VerificationMessage) VALUES(1, 'Phone','234-456-3456', 'valid address 3')

EXEC Usp_Insert_Contacts @ContactWithLog.

if you are as lucky as I am, the script above should just run fine.

if you issue  select * from Contact, you will get 3 rows

Id   CustomerId  ContactType  Value

1    1           Email       Test@xyz.com

2    1           Fax         123-456-3454

3    1           Phone       234-456-3456

if you issue  select * from ConatctLog, you will get 3 rows returned back as following:

Id    ContactId   VerificationMessage

1     1           valid address 1

2     2           valid address 2

3     3           valid address 3

From the code you can see that in order to get the identity values generated when we insert into the first table, we use OUTPUT together with INTO clause to store the data in a local table type variable. Then we use the local table type variable table to join back with the original parameter table to insert into the second table.

Enjoy querying with SQL Server 2008