Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Creating table with insert command to recreate every row

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 :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading