BULK COPY/INSERT Example !

Hi,

Recently I faced a scenario where I have to copy multiline data in varchar(2000) field from one data base to another database.
I tried copy and paste in SQL Server Management Studio and always eneded up with only top row of multi row data in target table.

Writing an ETL (previously know as DTS) was an expensive approach.
I was testing something so I just copied the data to query editor and wrote an insert query  and managed with duplicate multi-line data in all rows.

Somehow I missed to used BULK COPY and INSERT approach and thus I though to share it below:

exec master..xp_cmdshell 'bcp "Select * from myDB..myTable" queryout "C:\Data.txt" -c -T -t "^,~!@"'

BULK INSERT myDB..myTable FROM 'c:\Data.txt' WITH (FIELDTERMINATOR = '^,~!@')

1 important note here is that you need to enable xp_cmdshell before doing this using your SQL Server Management Studio.

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]

Namaste (Greetings)