Extracting table contents to a file (including "NULL")

As part of a software testing effort I need to compare the complete set of records stored in one 'version' of a MS-SQL 2005 database, to those from another 'version' of that DB (stored on a different server), and get a list of differences.

My normal approach is to use BCP with Queryout to extract each table's records to a text file, concatenate those files into one large file (doing this for each DB), and then use a text-file-compare utility to scope out differences. The limitation of this method is that the text files don't distinguish a field containing a blank string ('') from a field containing NULL. The text file shows just a blank string in either case. Normally this distinction is unimportant but in this instance it's critical to my test results.

So the questions:

  • Is there any way to get a BCP Queryout to show me "NULL" when a field has a NULL? I've already tried the -k switch and that's not what it's for.

  • Is there a completely different approach, with a different tool, that I could use to get this comparison and list of differences?

more ▼

asked Dec 15, 2009 at 01:31 PM in Default

Jim Maloy gravatar image

Jim Maloy
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Both Red Gate SQL Data Compare & Apex SQL Data Diff will do this for you...

more ▼

answered Dec 15, 2009 at 01:38 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

You can also use Visual Studio 2008 Team system - database edition to compare the data (and schema). Another benefit with VS is that you may apply "best practice" rules to validate your database code with.

more ▼

answered Dec 15, 2009 at 03:56 PM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left

If you want to use a text files and BCP, you can simply create a view or query in which you can quote all strings and for null values simply put "NULL" without quotes in the output.

SELECT ISNULL('"' + CharacterField '"', 'NULL')
FROM Table

So all strings will be queoted and empty string will be represented by empty queotes. NULLvalue will be represented by NULL

more ▼

answered Dec 15, 2009 at 02:48 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

Hi there,

You might want to give a try to Volpet's Table Diff:


You can try a fully-functional copy for 30 days.

Please let me know for anything.

Thank you,


more ▼

answered Dec 23, 2009 at 09:54 PM

Giammarco Schisani gravatar image

Giammarco Schisani

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 15, 2009 at 01:31 PM

Seen: 1766 times

Last Updated: Dec 15, 2009 at 01:31 PM