I used the MERGE function for the first time and absolutely LOVED it!

Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.

What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.

Any help or experience doing this would be GREATLY appreciated.

Thanks!

There are several options that you can use in order to export the result SET into a file. you can Inserting the results of the MERGE statement into another table and then export it or you can export the result directly to a file. check these options for example.

1. configure the SSMS to send results directly to file:

> Go to Tools --> Options --> Query Results --> SQL Server --> Results To Text
> On the far right, there is a drop down box called Output Format
    Choose Comma Delimited and click OK

> Now on the SSMS main buttons chose "Results to File"

execute the query and you will get an option to save the result instead of showing it in grid or text. In this case there is no need for output. the results is send to a file.

2. Use BCP to export the data

> save the reslt to a simple table using your output
> execute BCP command to export the data

3. use SQL Server Export Wizard

http://bpmdeveloper.com/sql-server-export-wizard-csv/

4. Using osql

This allowed you to use any query and save the result to a file. You will need to use xp_cmdshell which by default is not Enabled. So first you will need to Enable 'xp_cmdshell' SQL Server, which is not always recommended on production. Same with bcp that can be executed using master..xp_cmdshell.

** There are more options :-(
Let's start with

Personally, I would consider SSIS to export what you have in your "delta-table".

Do I have to include every blessed column in my UPDATE even if we pass no data via that data column yet the 3rd party wants the entire file structure back?

For example...I matching on [MemberAccountNumber] and I will pass back all Member Address data touch points and deltas, [MemberStreet1], [MemberStreet2], [MemberCity], [MemberState], etc.. but also have to pass back [InsuranceRank], [InsuranceID], [InsuranceType] that I never value on our end...sooooo they will never be delta'ed yet the 3rd party wants all their data columns back in a pipe-delimited file.

Do I have to include every blessed column in my UPDATE even if we pass no data via that data column yet the 3rd party wants the entire file structure back?

For example...I matching on [MemberAccountNumber] and I will pass back all Member Address data touch points and deltas, [MemberStreet1], [MemberStreet2], [MemberCity], [MemberState], etc.. but also have to pass back [InsuranceRank], [InsuranceID], [InsuranceType] that I never value on our end...sooooo they will never be delta'ed yet the 3rd party wants all their data columns back in a pipe-delimited file.

>> Do I have to include every blessed column in my UPDATE even if we pass no data via that data column yet the 3rd party wants the entire file structure back?

This is a good question and the answer is NO

You can see several options that you can use:

1. You can insert the relevant columns to a new table, and use the methods above (including the option of SSIS that TiborK

mentioned) in order to export the data from that table.

2. even if you export the data from a table that include all columns Using BCP fro example you can configure which columns will be added to the file. You can use an XML format file for this (there is option to use non-XML format file, which personally I do not like).

3. If this is something that you do every time that you update the data, then you can have a replicate database in the 3rd party place, which replicate the table automatically. This way there is no need to create the file at all and he will see any action in his database (replicates only the relevant tables in this case, and remember that he have all the data).

Again, like always... there are lot of options :-)
You should chose the one that fits your needs

* I can not recommend which one best for you, without the full business logic and fully understand your specific system and knowledge :-)