Insert multiple columns with multiple values using stored procedure in SQL Server 2008

Question

We are passing 4 parameters to a stored procedure

@PageID,
@CompanyID,
@URL, 
@URLCode,

All these parameters contains comma-separated values

Example:

 @PageID contains ("1,2,3,4,5,6,7")
 @CompanyID contains ("10,20,30,40,50,60,70") 
 @URL contains ("u1,u2,u3,u4,u5,u6,u7")
 @URLCode contains ("c1,c2,c3,c4,c5,c6,c7")

In database I have one table as mentioned below:

Table name : UrlMapping

Columns:

MappingID (PK), 
PageID, 
CompanyID,
URL,
UrlCode

Can you suggest how can insert these multiple comma separated values in UrlMapping table.

score 2 · Answer 1 · edited Oct 09 '19 at 12:44

It's a two step process.

Split the strings into a set of temporary tables: T-SQL: Opposite to string concatenation - how to split string into multiple records

Join the tables back together based on a row numbering and insert into the database:

INSERT INTO UrlMapping 
(PageID, CompanyID)
FROM
SELECT PageID, CompanyID
(
    SELECT id AS PageID, ROW_NUMBER() OVER (ORDER BY id) as PageRowNum
    FROM
    @SplitPageID
) AS SplitPageID
INNER JOIN  
(
    select id AS CompanyID, ROW_NUMBER() OVER (ORDER BY id) as CompanyRowNum
    FROM
    @SplitCompanyID
) AS SplitCompanyID
ON
SplitPageID.PageRowNum = SplitCompanyID.CompanyRowNum

This is a representative query, you will need to implement the splitting, insert into in the Split temporary tables, and expand the joins to cover all columns.

Insert multiple columns with multiple values using stored procedure in SQL Server 2008

1 Answers1