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.
>Solution :
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;