Exporting data to text file

thomask

Hi there - I want to export my table data to a text file.

The thing is, I want to store data from certain core tables with my versioning control system and then load them into an empty database.

Moreover, I want the format to be in a text format in order to make it easy for my co-developers to detect conflicts and do merges on these text files.

I've had a look at the "bcp" utility but the file it is producing does not lend itself to an easy merge/conflict resolution.

I guess it would be ideal if I could do a "data compare" to a file folder similar to the way SQL Compare Pro can do a schema compare to a script folder

Does anyone know of a feature in SQL Data Compare or another tool (Red Gate or otherwise) which can do this?

Cheers,
Thomas

David Atkinson

Hi Thomas,

We are considering support some sort of text file representation of data that SQL Data Compare could read and write, but we haven't yet decided what is the best format for this. Having DML (INSERT statements) would make it consistent to what SQL Compare does, although having a simple csv file would make it easier to edit details directly in source control, and would provide a format that could be used for a BULK INSERT. I'm sure that other formats could also work.

What format would work best for you?

David Atkinson
Product Management
Red Gate Software

thomask

Hi David - I would probably prefer to have both versions - ie. one with INSERTs and one as straight CSV both with suitable settings for encoding.

The nice thing about INSERTs is that it would work more or less out of the box. With CSV you'd still need some other tool in order to insert it into the table.

Regards
Thomas

David Atkinson

So INSERTs would be slightly better for you?

I'd be interested to know whether others would want this feature in SQL Data Compare, and if so, which format would work best.

Thanks,

David Atkinson
Product Manager
Red Gate Software

jerryhung

That's an interesting and good idea, store data contents in text and SourceControl it

For that purpose, I would say TEXT > INSERT/DML

but obviously when there's the need to INSERT, obviously having INSERT is nice - but I thought that's why Data Compare exists in the first place - you hit a button and it synchronizes data for you, without the need for INSERT statement

I forgot now, doesn't Data Compare generate a script like SQL Compare? which will be purely INSERT if you compare with empty database

David Atkinson

Yes, SQL Data Compare would generate INSERTs if compared to an empty database, which could in theory be copied into files, but this is hardly a usable approach. What it can't do is read from a set of INSERT files and synch these back to a live database, which you may want to do if you're using the source controlled representation as the source of your schema and reference/lookup data.

David Atkinson
Red Gate Software

thomask

The reason for having INSERT's would be to make it as easy as possible to use it in an automated build/deploy script.

My ambition is to create a build/deploy procedure that given a tag in subversion will do the following:
1) Check-out all my files from SubVersion.
2) Build the application.
3) Create an empty database.
4) Use SQL Compare Pro to create all the tables, SP's, UDF's, etc in the empty database from the script files.
5) Run the data-scripts discuseed in this thread to insert "core" data.

I am very close to completing these steps, I am only missing a nice (easy) way of creating the data files for step 5.

Cheers,
Thomas

David Atkinson

The only thing I can suggest is that you create a temporary database which you synch using SQL Compare Pro to get the baseline schema. You then run the data scripts on this using sqlcmd.exe. You synch up your deployment database from the temporary database using the SQL Data Compare command line and subsequently delete your temporary database.

Would this work for you?

David Atkinson
Red Gate Software

thomask

Hi David - your approach is more or less what I had in mind.

The reason I am searching for an easy tool to do the data file part is to "ease the pain of change" in the development team.

I am currently considering building a quick tool using the free filehelpers library (http://www.filehelpers.com/)

Cheers,
Thomas

David Atkinson

Thomas,

This sounds interesting. Could you describe exactly what this tool would do?

David

DotMedic

Hi,

I tried to use Export Comparison Results (only inserts) to generate CSV files of my Test data. This should work with following SQL statement but the csv file is wrong.

BULK INSERT [DOK_FRASER]
FROM 'c:\Temp\DOK_FRASER.csv'
WITH
(
FieldTerminator = ';',
FIRSTROW = 2
)
GO

There are 2 problems with generated CSV file...

First is that the generated CSV file is doubling the columns (column names are doubled, the values are)
"FRASLOPNR";"FRASNAMN";"FRASNAMN";"FRASTEXT";"FRASTEXT";"GILTIGFROM";"GILTIGFROM";"GILTIGTOM";"GILTIGTOM"
"30";"Injektion ";"";"Injektion Behephan 1mg/ml, 1 ml subcutant.";"";"2008-05-06 00:00:00";"";"";""

the correct rows should look like
"FRASLOPNR";"FRASNAMN";"FRASTEXT";"GILTIGFROM";"GILTIGTOM"
"30";"Injektion ";"Injektion Behephan 1mg/ml, 1 ml subcutant.";"2008-05-06 00:00:00";""

Secound issue is scripting of values that are blobs. The BLOBs are not generated correctly. The value is scripted as "<Binary Data...>" instead of the real data.

Is this known\reported bug?

Regards,
Sanjin