4

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).

Thanks!

3

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):

enter image description here

enter image description here

enter image description here

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 ...
  • That only meets part of the requirements. I want the output to be INSERT statements. – Robin Jun 13 '13 at 19:27
  • @Robin why are you concerned with the insert statements ? Did you launch the wizard and tried it ? It will create an SSIS package that will do the stuff for you. If you want to Export the data from PROD to destination then use Export wizard from PROD server. I would choose Import wizard that will run on Destination and import data from PROD. If anything is unclear, please let me know. – Kin Shah Jun 13 '13 at 20:15
  • I am creating test cases that insert sample data. Execute code against the sample data, test the results and rollback. If I was building a development or isolated test environment I would use the Import/Export wizard, but that is not what I am looking for. – Robin Jun 14 '13 at 11:26
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.