CODEWITHSUNDEEP
sqlsql-server
Muhammad Abdullah
Muhammad Abdullah

Reputation: 1

Copy multiple rows of same table but with different id and store old ids of copied row in column name old ids

Here is my code using curser it doing well but I don't want to use cursers due to speed issues

DECLARE @StudentID NVARCHAR(MAX)='1,2,4,5,6,7,8,9,10'    

SELECT value StudentID INTO #TMP_tbSchedulTas FROM STRING_SPLIT(@StudentID,',')

DECLARE @SchedID AS Integer
SELECT @SchedID = StudentID
FROM #TMP_tbSchedulTas

DECLARE Attendence CURSOR FOR

SELECT StudentID FROM #TMP_tbSchedulTas

OPEN Attendence
FETCH NEXT FROM Attendence INTO @SchedID

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO dbo.Student
(
FirstName,LastName,
FatherName,DOB,CNIC,Religon,Mobile,Email,Address,Gender,Isactive,Isdeleted,CreatedBy,CreatedOn,CityID,OldstudentId,BranchID,IsSelected,StudentGUID )
SELECT FirstName,LastName,
FatherName,DOB,CNIC,Religon,Mobile,Email,Address,Gender,Isactive,Isdeleted,CreatedBy,CreatedOn,CityID,@SchedID,BranchID,IsSelected,StudentGUID FROM dbo.Student
WHERE StudentID=@SchedID

FETCH NEXT FROM Attendence INTO @SchedID
END

CLOSE Attendence
DEALLOCATE Attendence

-- drop temporary table to store schedule event attendees email status
DROP TABLE #TMP_tbSchedulTas

Upvotes: 0

Views: 139

Answers (1)

eshirvana
eshirvana

Reputation: 24568

You can simplify it like so, however I'm not sure if you really want to read from the same table and insert into it again for which reason!

DECLARE @StudentID NVARCHAR(MAX) = N'1,2,4,5,6,7,8,9,10';

INSERT INTO dbo.Student
    (
        FirstName
        , LastName
        , FatherName
        , DOB
        , CNIC
        , Religon
        , Mobile
        , Email
        , Address
        , Gender
        , Isactive
        , Isdeleted
        , CreatedBy
        , CreatedOn
        , CityID
        , OldstudentId
        , BranchID
        , IsSelected
        , StudentGUID
    )
SELECT
    FirstName
    , LastName
    , FatherName
    , DOB
    , CNIC
    , Religon
    , Mobile
    , Email
    , Address
    , Gender
    , Isactive
    , Isdeleted
    , CreatedBy
    , CreatedOn
    , CityID
    , @SchedID
    , BranchID
    , IsSelected
    , StudentGUID
FROM
    dbo.Student
WHERE
    StudentID IN
        (
            SELECT
                value StudentID
            FROM
                STRING_SPLIT(@StudentID, ',')
        );

Upvotes: 1

Related Questions