Exporting SQL data to a flat file

[Ray Powid]

SQL 6.5
NT 4.3

Can someone assist me with the following....

1. I am attempting to export data from a SQL DB (single table using a query) to a "flat file".
2. I would then like to take this "flat file" and import the data into a different SQL DB (same schema structure as first DB).

Unfortunately this has to be done in two steps.


Thank you for your help.

RPowid

[Ray Miao]

Why don't use db transfer?


------------
Ray Powid at 3/19/2002 8:52:39 AM

SQL 6.5
NT 4.3

Can someone assist me with the following....

1. I am attempting to export data from a SQL DB (single table using a query) to a "flat file".
2. I would then like to take this "flat file" and import the data into a different SQL DB (same schema structure as first DB).

Unfortunately this has to be done in two steps.


Thank you for your help.

RPowid

[Ray Powid]

Ray,

I am unfamiliar with "db transfer", can you elaborate. My databases in question reside on two different servers. The data that I wish to transfer is historic in nature and was deleted from the Production server. I wanted to restore a copy of the Production DB on another server and run a SQL query to identify the records that were deleted. Once identified I wanted the records to be transferred to the Production server.

Thank you for your assistance.......

------------
Ray Miao at 3/19/2002 9:05:34 AM

Why don't use db transfer?


------------
Ray Powid at 3/19/2002 8:52:39 AM

SQL 6.5
NT 4.3

Can someone assist me with the following....

1. I am attempting to export data from a SQL DB (single table using a query) to a "flat file".
2. I would then like to take this "flat file" and import the data into a different SQL DB (same schema structure as first DB).

Unfortunately this has to be done in two steps.


Thank you for your help.

RPowid

[rogerjh]

What version of SQL Server are you on? If you are on 7 or later, you can use the DTS features to do this graphically in Enterprise Manager. Simply right-click on the table you wish to transfer, and choose Export data (this is done on the Source server).

If this 6.5, you will need to first re-create the table on the destination server, then use bcp to move the information over. If the original table has an identity field, you will need set the -E switch to enable identity insert. The following commands in a batch file from the source server should do the trick:

bcp <database>..<table> out <file_name> -n -E -U<login> -P<password> -S<source_server>


bcp <database>..<table> in <file_name> -n -E -b5000 -U<login> -P<password> -S<destination_server>

The file_name will be the same for both statements.


------------
Ray Powid at 3/20/2002 9:18:23 AM

Ray,

I am unfamiliar with &#34;db transfer&#34;, can you elaborate. My databases in question reside on two different servers. The data that I wish to transfer is historic in nature and was deleted from the Production server. I wanted to restore a copy of the Production DB on another server and run a SQL query to identify the records that were deleted. Once identified I wanted the records to be transferred to the Production server.

Thank you for your assistance.......

------------
Ray Miao at 3/19/2002 9:05:34 AM

Why don&#39;t use db transfer?


------------
Ray Powid at 3/19/2002 8:52:39 AM

SQL 6.5
NT 4.3

Can someone assist me with the following....

1. I am attempting to export data from a SQL DB (single table using a query) to a &#34;flat file&#34;.
2. I would then like to take this &#34;flat file&#34; and import the data into a different SQL DB (same schema structure as first DB).

Unfortunately this has to be done in two steps.


Thank you for your help.

RPowid