CODEWITHSUNDEEP
sql-server
aggicd
aggicd

Reputation: 737

SQL Server: Copy rows of table to same table

I have 2 tables with data: GroupTable and PeopleTable.

GroupTable has this columns: id, continent, country

PeopleTable has this columns: id, groupId , name

groupId is a foreign key to GroupTable.id

Both tables are already populated from my application. What I want to do is create a stored procedure to copy contents of both tables to the same tables but changing the continent column of GroupTable. So the new rows in PeopleTable will point to the new rows in GroupTable. Input for the stored procedure are @sourceCont, @destinationCont

What I have:

GroupTable:
      id   |    continent   |   company
       0   |    europe      |     aa
       1   |    europe      |    bb
       2   |    europe      |    cc

PeopleTable:
      id   |   groupId    |   name
      0    |      0       |   John
      1    |      0       |   Mary
      2    |      0       |   Nick
      3    |      1       |   Peter
      4    |      2       |   Michel

This is what I want (@sourceCont = europe, @destinationCont = america)

GroupTable:
      id   |    continent   |   company
       0   |    europe      |    aa
       1   |    europe      |    bb
       2   |    europe      |    cc
       3   |    america     |    aa
       4   |    america     |    bb
       5   |    america     |    cc
PeopleTable:
      id   |   groupId    |   name
      0    |      0       |   John
      1    |      0       |   Mary
      2    |      0       |   Nick
      3    |      1       |   Peter
      4    |      2       |   Michel
      5    |      3       |   John
      6    |      3       |   Mary
      7    |      3       |   Nick
      8    |      4       |   Peter
      9    |      5       |   Michel

Upvotes: 0

Views: 79

Answers (1)

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

CREATE Proc Rocount
@sourceCont NVARCHAR(50), @destinationCont NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON

DECLARE @String_sql NVARCHAR(MAX),@Maxid INT

     --NOTE IF GroupID Is AUto Generated IN GroupTable
    SET @String_sql='Insert INTO GroupTable
                     SELECT '''+@destinationCont+''' FROM GroupTable WHERE continent ='''+@sourceCont+''' '
    PRINT @String_sql
    EXEC (@String_sql)

    SELECT @Maxid= ID FROM GroupTable WHERE  continent = @destinationCont

    SET @String_sql='Insert INTO PeopleTable
                     SELECT ID,'''+CONVERT(NVARCHAR(2),@Maxid)+''',name 
                     FROM PeopleTable WHERE GroupID IN (SELECT GroupID FROM GroupTable WHERE continent ='''+@sourceCont+''')'
    PRINT @String_sql
    EXEC (@String_sql)




END

Upvotes: 1

Related Questions