Posted by: R Manimaran | September 24, 2010

Insert Comma Separated values to a Table in a Single Insert

Insert Comma Separated values to a Table in a Single Insert

I have a table named Departments with the columns

I have a requirement to insert into this table in a single insert operation. The user will enter multiple department names in a single pass as a comma separated values.

I wrote a Stored Procedure which will separate the Department name and insert into the table.

Use the XML, cast and Replace functions to separate the department names

Stored Procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_INSERT_DEPARTMENTS]

@DeptNames varchar(Max)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @xml AS XML,@delimeter AS CHAR

SET @delimeter =‘,’

SET @xml = CAST((‘<x>‘+REPLACE(@DeptNames,@delimeter,‘</x><x>’)+’</x>’) AS XML)

INSERT INTO Departments (DepartmentName)

SELECT N.value(‘.‘,’varchar(MAX)’) as value FROM @xml.nodes(‘x‘) AS T(N)

END

Calling the SP

DECLARE @return_value int

EXEC @return_value = [dbo].[SP_INSERT_DEPARTMENTS]

@DeptNames = N’Physics,IT,Chemistry

SELECT ‘Return Value’ = @return_value

GO

Output:

Advertisements

Responses

  1. Good One… Thanx Pal


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Categories

%d bloggers like this: