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.

When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest change occurs. This makes it impossible for the developers to run a diff program with the previous scripted data and see the data changes: all the tables every time are reshuffled around even if no rows were inserted/deleted so that a diff program can't really show the few data changes in coherent manner.

Is there a way to script data in a database so that they return roughly the same order if no major data additions/deletions that might affect the order happen?

share|improve this question
    
Do you have a clustered index on the table? –  Kenneth Fisher Mar 4 at 0:10
    
@KennethFisher, sorry, what table? –  zespri Mar 5 at 7:25

3 Answers 3

I personally would use a tool (as indeed I already use) , such as are sold by Apex, Red Gate, Toad, and others. These could be used to script only the rows that have changed. That would make your looking for changes very simple indeed.

If you are looking for a free tool you might examine http://opendbiff.codeplex.com/. I have not personally used it, but some people find it useful. (There are doubtless other such tools in the public domain.)

share|improve this answer
    
Thank you. It does not appear that opendbdiff can script data. –  zespri Mar 3 at 21:18
    
I see quite a few open source tools, but I have no energy for searching through them. Under the topic I shared is: opendbiff.codeplex.com/documentation That has a recently updated list of potential tools. (Of course, you could write your own code, but getting a tool, even if paid for, will doubtless serve you better.) –  RLF Mar 3 at 21:59

If by "script data" you mean "generate INSERT statements", generally I'd use something along the lines of the pseudocode:

    SELECT 'INSERT INTO ' + sys.tables.name + ' ( ' + column_list + ' ) VALUES ... ORDER BY <your order>

Include SET INDENTITY_INSERT statements as required, and so on and so forth.

You can probably also use Profiler to capture the SQL that Generate Scripts is running, if you want to start with that.

You may want to look at the stored procedure listed in the answer @NeerajPrasadSharma gave to How to generate an INSERT script for an existing SQL Server table that includes all stored rows?

share|improve this answer
up vote 0 down vote accepted

What I ended up doing is the following:

  • Created an empty (no data) database with the same schema as my database. Can be done with 'SQL Schema Compare' feature of VS2013
  • Ran 'Sql Data Compare' between my database and the empty one
  • Generated Update Script from the changes

The result seems to be more deterministic than the one from SSMS after my few trials. I hope it stays this way.

share|improve this answer

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.