Thursday, October 25, 2012

multiple records insert in single statement using cross jopin in sql server

INSERT INTO table_name(Coll1,Coll2,Coll3)

SELECT @Variable1,C.items,E.items
FROM dbo.Split(@Variables, ',') AS C CROSS JOINdbo.Split(@Variables1, ',') AS E


Ex:


INSERT
INTO tbl_LMS_SessionEmpCourse_Xref(SessionID,CourseID,EmployeeID)
SELECT @SessionID,C.items,E.items
FROM dbo.Split(@CourseIDs, ',') AS C CROSS JOINdbo.Split(@EmployeeIDs, ',') AS E

INSERT INTO tbl_LMS_SessionEmpCourse_Xref(SessionID,CourseID,EmployeeID)
SELECT 1,C.items,E.items
FROM dbo.Split('1,3', ',') AS C CROSS JOINdbo.Split('4,5', ',') AS E

No comments:

Post a Comment