Bulk insert using WCF SQL Adapter

We had a scenario where we had to do a bulk insert of about 200 msgs into sql, after a lot of branistomring and googling we came up with the following solution

– Create a new table type
CREATE TYPE [dbo].[BulkInsertDBType] AS TABLE(
      [ID] [nvarchar](50) NULL,
      [Creation_Date_Time] [nvarchar](50) NULL
)

– Create a stored proc with  MERGE  

 

Create PROC [dbo].[sp_BulkInsertInsert]
 @List  BulkInsertDBType READONLY
  
AS
      SET NOCOUNT ON
      SET XACT_ABORT ON 
     
      BEGIN TRAN
      MERGE BulkInsertDBTable AS [Target]
USING @List   AS [Source]
ON [Target].[ID] = [Source].[ID]
WHEN NOT MATCHED THEN
    INSERT ( [ID], [Creation_Date_Time] )
    VALUES ( [Source].[ID], [Source].[Creation_Date_Time]);
COMMIT

Conclusion: the new WCF SQL Adapter supports table data type which helps for inserting bulk data!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: