How to load a table using BCP

Question

Context:

I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt.

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

Now that I am equiped with the XML File containing all the information that I need, I would like to load this into another database.

Since it is that easy to create this table dump into a xml file, I suppose there must be a simple way to load the XML File into a database (Creates the Table and then loads the data). Does anyone know how to do this?

Disclaimer: I am not a DBA and this is my first experience with BCP, the documentation was talking about creating a Format file but it didn't seem to work for me.

Thanks in advance for your responses

Answer 1

I wrote a blog post showing some sample BCP commands, including how to create the format file, at https://www.sqlserverscience.com/tools/example-bcp-export-import-commands/

This command should create the format file that is essential for import via bcp:

bcp "dbo.Oranges" format nul -S "FRUIT\PEARS" -T -d "Fruit" -n -f "C:\some\path\Oranges.fmt" -x

dbo.Oranges is the table name. FRUIT\PEARS is the SQL Server instance name. Fruit is the database name.

If you're having trouble with the XML file, I would recommend using the native binary format generated by BCP It ensures the export and import will work without any data loss, and is fully supported across various versions of SQL Server.