Creating table with insert command to recreate every row

user_18544412 0
Viewed 42

I have table named workerTab:

| Id | Name | Age | Cityid | 
| ---| ---- | --- |  ---   | 
| 1| John   | 22  | 5      |
| 2| Adam   | 34  | 5      |
| 3| Eve    | 19  | 5      |

And I would like to have in column: Build, insert query that will rebuild/fill my table. I have something like that:

SELECT *
    ,CONCAT (
        'INSERT INTO workerTab(id,name,Age,Cityid)
VALUES(1, ''John'', 22, 5),'
        ,'(2, ''Adam'', 34, 5), '
        ,'(3, ''Eve'', 19, 5)'
        ) as Build
from workerTab
where cityid = 5

This is what I got :

Id Name Age Cityid Build
1 John 22 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(1, 'John', 22, 5),(2, 'Adam', 34, 5), (3, 'Eve', 19, 5)
2 Adam 34 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(1, 'John', 22, 5),(2, 'Adam', 34, 5), (3, 'Eve', 19, 5)
3 Eve 19 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(1, 'John', 22, 5),(2, 'Adam', 34, 5), (3, 'Eve', 19, 5)

I would like to have in column Build insert command for every row. For example:

Id Name Age Cityid Build
1 John 22 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(1, 'John', 22, 5)
2 Adam 34 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(2, 'Adam', 34, 5)
3 Eve 19 5 INSERT INTO workerTab(id,name,Age,Cityid) VALUES(3, 'Eve', 19, 5)

I just got stuck and can't get it done.

tsql sql-server sql
1 Answers

You should build the insert for each row using values only from that row.

SELECT Id, Name, Age, Cityid,
       'INSERT INTO workerTab (Id, Name, Age, Cityid) VALUES (' +
       CAST(Id AS NVARCHAR(MAX)) + ', ' + QUOTENAME(Name, '''') + ', ' +
       CAST(Age AS NVARCHAR(MAX)) + ', ' + CAST(Cityid AS NVARCHAR(MAX)) + ')' AS Build
FROM workerTab
WHERE cityid = 5;
user_18632290
Related