Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

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

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 ...
share|improve this 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 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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