Creating table with insert command to recreate every row-CodePudding

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.

CodePudding user response：

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 VARCHAR(MAX))   ', '''   Name   ''', '  
       CAST(Age AS VARCHAR(MAX))   ', '   CAST(Cityid AS VARCHAR(MAX))   ')' AS Build
FROM workerTab
WHERE cityid = 5;