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?

  • Do you have a clustered index on the table? Mar 4 '14 at 0:10
  • @KennethFisher, sorry, what table? Mar 5 '14 at 7:25

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

  • Thank you. It does not appear that opendbdiff can script data. Mar 3 '14 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 '14 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?


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.

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.