EXPORT specific columns to INSERT statements

Question

Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?

I am trying to generate these for unit testing based on a large production table so using something like "Generate Scripts" in SQL Server Management Studio is out of the question.

There are a few tools that might be able to do this, but something so trivial in MySQL should not be that complex in SQL Server (at least I would hope not).

Answer 1

Use Import/Export wizard and instead of exporting the entire table, choose sql to export specific rows and columns.

You can write sql like : select column1, column2 from Table1 where some_condition or if you don't care about specific data then you can use top command to extract any number of rows that you require.

Edit : Below are screenshots for Import export Wizard (Launch it from SSMS):

EDIT:

@Robin IN your situation, you can do 2 things -

1. Go for something like Redgate's sql data generator that will generate test data -- OR --
2. You can do a 1 time import of data as I suggested and then backup database and for your rest of testing you can just keep restoring the database from the backup (keeping in mind the data that you imported will not change). I cant think another way of doing what you want to achieve, so will keep this for others to answer ...

Answer 2

here is something I did to do exactly what you wanted...

(altho i needed my insert statements in MySQL to transfer parts of some tables to an Apache server. So the syntax might vary).

 SELECT CONCAT(
'INSERT INTO `db_name`.`table_name`
(`col1`,
`col2`,
`col3`)
VALUES
(
' , [col1] , ','
, [col2], ','
, [col3], ');'
)
FROM [dbo].[db_name]
  where col5 = 'some condition';

Then u can right click on the results and save them as... CSV file. Then open the CSV file in notepad++ and replace all the double quotes " with nothing.

And volah I had 7000 insert statements to run in mysqlworkbench and transfer only the specific columns I needed to a different database.

You can use workbench or mssms to generate the insert statement for the destination table as a starting point by right clicking on the table in either program and copy to your select statment.

The beauty of this method also is if your destination where you want to run the inserts might have different column names to the source.

Answer 3

If you using tool sql tool such dbbeaver write your "select col1 , col2 , .. from table_name where condition" after getting the result. select from result panel generate insert statements as shown below