Solved

How to insert comma separated values in table

Posted on 2013-12-31
2
4,438 Views
Last Modified: 2013-12-31
Dear Experts,
How can I insert them into the table


Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'


INSERT Into #FeeTypeTemp(FeeType)
Select @FeeType
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
0
Comment
Question by:Mehram
2 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 500 total points
ID: 39749470
First create this function :
CREATE FUNCTION [dbo].[SplitIDs]
(
	@List varchar(MAX)
)
RETURNS 
@ParsedList table
(
	ID int
)
AS
BEGIN
	DECLARE @ID varchar(10), @Pos int

	SET @List = LTRIM(RTRIM(@List))+ ','
	SET @Pos = CHARINDEX(',', @List, 1)

	IF REPLACE(@List, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @ID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
			IF @ID <> ''
			BEGIN
				INSERT INTO @ParsedList (ID) 
				VALUES (CAST(@ID AS int))
			END
			SET @List = RIGHT(@List, LEN(@List) - @Pos)
			SET @Pos = CHARINDEX(',', @List, 1)

		END
	END	
	RETURN
END

Open in new window


Then:
DECLARE @FeeType VARCHAR(100)
SET @FeeType = '1,4,5'

CREATE TABLE #FeeTypeTemp(FeeType INT)

INSERT Into #FeeTypeTemp(FeeType)
SELECT * FROM dbo.SplitIDs(@FeeType)

Open in new window

0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39749491
You can insert multiple records using single INSERT statement as 'INSERT INTO table (columns) VALUES (values1), (values2), (values3), (etc)'

Try below code:
--create table #FeeTypeTemp(feetype int)

declare @values varchar(8000), @sql varchar(8000)
Select @values = '10,20,30'

select @sql = 'Insert into #FeeTypeTemp (feetype) values ('
                        + REPLACE (@values, ',', '),(') + ')'
exec (@sql)
select * from #FeeTypeTemp

-Harish
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question