I am new to sql server and need help to creating a query to insert all data into table in one call using stored procedure.
For example I have 1 User(say "Tech" has Id 1) and list of Customers("C#","ASP","MVC").
My Table structure
Id (PK),
UserId(FK)
Customers varchar.
Accepted result like
UserId----|-----Customers
1 ----------|----- C#
1 ----------|----- ASP
1 ----------|----- MVC
I will pass userId and list of customers to my SP.
One way of doing this is as follows . I am assuming that you have input like this format @userId=1 , @ListOfSubjects ='asp,c#' .. here is the sample proc . The idea is to first convert list od comma seprated string ofsubject to table and then insert it . To do that I created a sample UDF which will retun a table when comma separated string is passed to it .
create proc SampleUser_Insert
@UserId int ,
@ListOfSubjects varchar(1000)
as
begin
--set @UserId=1
--set @ListOfSubjects ='asp,c#,mvc'
declare @sampleTable table (userId int, subjects varchar(100))
insert into @sampleTable (userId,subjects )
SELECT @userId, data FROM dbo.[SplitString](@ListOfSubjects, ',')
select * from @sampleTable
end
-- exec SampleUser_Insert 1,'a,b,c'
the udf is as follows
CREATE FUNCTION [dbo].[SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO
