March 6, 2012 at 3:42 am
I need to export data from a table and import into another. The source table contains several columns of different datatypes as well as 2 columns that are xml datatype. I need to export data from ALL columns and import into another table (with exactly the same structure) in another database. Ive used bcp to export to a text file, but this doesnt seem to work for xml column types. Is there a straightforward way to do this ?
March 6, 2012 at 3:53 am
I'm sure you can do this in a text file with the correct delimiters, but I'd avoid flat files entirely and use SSIS to move the data directly from one source to another.
You can use the Import/Export wizard to quickly setup a package to do this.
March 6, 2012 at 4:22 am
i need to setup some sort of script (or package) to do this. I wont have control over the destination since its on a live server that i dont have access to, i just need to implement 'a process' that will copy the data from the specified table and import it into another table. Ill let the network support guys specify the destination server and database. using the export wizard requires a destination server, so i figured id export as a text file and just supply the text file and a sql script that can be modified
March 6, 2012 at 4:46 am
Other than SSIS, you could use BCP with the native format.
-- Gianluca Sartori
March 6, 2012 at 5:04 am
Ive tried BCP but always get an error
The data type for input column is DT_NTEXT
how would i use native format ? any examples ?
March 6, 2012 at 5:10 am
bcp AdventureWorks.production.Product out c:\temp\product.dmp -n -S. -T
The above command copies the table "Production.product" from database AdventureWorks in the native file c:\temp\product.dmp.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply