x

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

avatar image

Jim Maloy
11 1 1 3

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Håkan Winther
16.5k 37 45 57

(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

avatar image

Pavel Pawlowski
22.7k 10 15 26

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

Hi there,

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

http://www.volpet.com/

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

Please let me know for anything.

Thank you,

Giammarco

more ▼

answered Dec 23, 2009 at 09:54 PM

avatar image

Giammarco Schisani
1

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

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x279
x49

asked: Dec 15, 2009 at 01:31 PM

Seen: 1963 times

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

Copyright 2016 Redgate Software. Privacy Policy