Generate insert script for exporting SPECIFIC data

Question

The task I am performing is moving some data from one database to another. The two databases has identical schemas so I only need the data. As of now, I'm using SQL Server Management Studio's built-in utility to generate scripts like this: Right click database -> Tasks -> Generate Scripts and then following the wizard. My problem with this is that my table is rather big and I need only specific rows. There is no option to include a where-clause in this so I get the whole table each time. Is there any way around this?

My desired result is something similar to what SQL Server Management Studio outputs when generating scripts, like this:

USE [databaseName]
GO
INSERT [dbo].[table_name] ([column1], [column2], [varCharColumn3], [varCharColumn4], [column5]) VALUES (1, 2, 'varCharValue1', 'varCharValue2', 4)
GO
INSERT [dbo].[table_name] ([column1], [column2], [varCharColumn3], [varCharColumn4], [column5]) VALUES (1, 2, 'varCharValue1', 'varCharValue2', 5)
GO
INSERT [dbo].[table_name] ([column1], [column2], [varCharColumn3], [varCharColumn4], [column5]) VALUES (1, 4, 'varCharValue1', 'varCharValue2', 4)
GO
INSERT [dbo].[table_name] ([column1], [column2], [varCharColumn3], [varCharColumn4], [column5]) VALUES (1, 4, 'varCharValue1', 'varCharValue2', 5)
GO
INSERT [dbo].[table_name] ([column1], [column2], [varCharColumn3], [varCharColumn4], [column5]) VALUES (1, 3, 'varCharValue1', 'varCharValue2', 4)
GO
And so on........

[What is the best way to auto-generate INSERT statements for a SQL Server table?](https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table) — Lukasz Szozda, Mar 07 '19 at 16:58
Are you saying that I could post my answer to that question instead? — wenzzzel, Mar 07 '19 at 17:01
I wanted to point out that similar question exists already. We should avoid creating duplicates and it looks like one. Feel free to post your answer there or leave it as is. — Lukasz Szozda, Mar 07 '19 at 17:03
Why do you need a giant insert script? BCP (with the appropriate options) does exactly this - and more efficiently. — SMor, Mar 07 '19 at 18:22
Our client has multiple similar systems and I do this to move configuration or parts of configurarion between them. Good tip, I'll check it out! — wenzzzel, Mar 07 '19 at 18:24

wenzzzel · Accepted Answer · 2019-03-08T07:59:46.907

- As I was writing this question, a collegue of mine got me the answer. Since I didnt find this on here before I'm still going to ask the question and answer it myself.

This simple query did the trick for me:

select 'insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (' + RTRIM(column1) + ', ' + RTRIM(column2) + ', ' + '''' + RTRIM(varCharColumn3) + '''' + ', ' + '''' + RTRIM(varCharColumn4) + '''' + ', ' + RTRIM(column5) + ');' 
from table_name
where 
    column5 = 4
    and column2 = 4

Some thing's that might be unclear:

at some points in the select-part there's four ' in a row. That is because the data in the column is a varchar and since the escape character for ' is ' it forces me to write '''' to specify ' around my values.

The output of above query looks like this:

insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (1, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (2, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (3, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (4, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (5, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (6, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (7, 4, 'varCharValue1', 'varCharValue2', 4);
insert into table_name (column1, column2, varCharColumn3, varCharColumn4, column5) VALUES (8, 4, 'varCharValue1', 'varCharValue2', 4);

Generate insert script for exporting SPECIFIC data

1 Answers1

This simple query did the trick for me:

The output of above query looks like this: