CODEWITHSUNDEEP
sql-server
DotNetBeginner
DotNetBeginner

Reputation: 439

Inserting using CSV

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

Upvotes: 0

Views: 92

Answers (1)

Lamak
Lamak

Reputation: 70648

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

Upvotes: 6

Related Questions