Inserting using CSV

Question

I have a table with two fields Name and ID. Name comes from input parameter @Name and ID comes a CSV @CSVID. I have a spilt function that return a temp table .

My stored proc is

INSERT INTO dbo.MyTable
(
    Name, 
    ID
 )
 VALUES
(

    (SELECT @Name, id FROM dbo.Split(@CSVID))
)

My split function

ALTER FUNCTION [dbo].[Split] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END

So what I want is

say @Name = 'John Doe' @CSVID = '1,2'

Then I want the result of insert to be

 Name    ID
 John     1
 Jhon     2

I saw so many example but they were all so complicated. I just a simple explanation as to how insert works if the subquery

SELECT * FROM dbo.Split(@CSVID)

returns more than 1 value.

Thanks

Lamak · Accepted Answer · 2013-05-03 21:57:16Z

6

INSERT INTO dbo.MyTable(Name, ID)
SELECT  @Name,
        id
FROM dbo.Split(@CSVID)

edited May 3, 2013 at 21:57

answered May 3, 2013 at 21:39

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

DotNetBeginner Over a year ago

So i return id from my split function. And added SELECT id FROM dbo.Split(@CSVID) in insert. it does not work. Can you point where did i go wrong

Lamak Over a year ago

@Neha But does SELECT id FROM dbo.Split(@CSVID) return something?, or it throws an error?. What is the actual column name of your function table?

DotNetBeginner Over a year ago

Yes if i pass SELECT id FROM dbo.Split('1,2'). I get values 1 and 2. No error there. but when I insert i get error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Lamak Over a year ago

@Neha I updated my answer so it uses id instead of the InsertYourColumnNameHere, but are you using the code I posted?, because it is different to the one you have on your question. I ask you that because I'm not using a subquery, so not sure where are you getting that error from

DotNetBeginner Over a year ago

Sorry to be a pain but I am not understanding completely. In the script you posted @Name becomes a part of Select. Now the Split function added in the mail question returns only id. How will that work.

|

Collectives™ on Stack Overflow

Inserting using CSV

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related