MS SQL bulk export / import

To export in native format execute from the command-line:

bcp MyDatabase..MyTable format nul -n -x -f C:\MyFolder\MyTableFormat.xml -T
bcp MyDatabase..MyTable out C:\MyFolder\MyTableData.dat -n -T

To deal with tables containing XML datatype the format file (MyTableFormat.xml) must be modified: add MAX_LENGTH=”8001″ to the FIELD element that represents column containing XML!

To import data in existing table that already contains data execute SQL:

print 'Inserting MyTable with KEEPIDENTITY ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS, KEEPIDENTITY)
([Id],[Col1],[Col2],[ColXml])
SELECT [Id],[Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)
as t1
WHERE t1.Id NOT IN (SELECT Id FROM [dbo].[MyTable])

If you don’t want to preserve Id values from imported table then instead of previous example execute following:

print 'Inserting MyTable with autoincrement ...'
INSERT INTO [dbo].[MyTable]
WITH (KEEPDEFAULTS)
[Col1],[Col2],[ColXml])
SELECT [Col1],[Col2],CONVERT(xml, [ColXml])
FROM
OPENROWSET
(
BULK 'C:\MyFolder\MyTableData.dat',
FORMATFILE='C:\MyFolder\MyTableFormat.xml''
)