Creating SQL Server format file to use with bulk insert

Question

I want to bulk insert a big pile of data into SQL Server, and thus, I need a format file (I'm not inserting value into all columns).

But using This link and the bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c..xml –t, -T format, I get an error, pointing at the -t, bit, saying ParentContainsErrorException saying there are missing arguments.

What's wrong with the above?

What are you trying to do? You need to select the data. Do you want to output it to an xml file? — SqlKindaGuy
– SqlKindaGuy, Commented Dec 15, 2017 at 8:13
What do you mean I need to select the data? I am, much like I wrote, trying to create a model file to use with bulk insert. I have followed the linked guide, using the above quoted syntax, and then get the error I listed. — Christofer Ohlsson
– Christofer Ohlsson, Commented Dec 15, 2017 at 8:15

SqlKindaGuy · Accepted Answer · 2017-12-15 08:23:14Z

1

You need to specify your path to where you want your xml file.

This here works for me:

DECLARE @str VARCHAR(1000)
SET @str = 'bcp AdventureWorks2014.HumanResources.Department format nul -c -x -f D:\Stack\Department-c.xml -t, -T'  
EXEC xp_cmdshell @str
GO

Given the result:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

answered Dec 15, 2017 at 8:23

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SqlKindaGuy Over a year ago

@ChristoferOhlsson The reason why they suggest a CMD is because BCP is actually an executable file. BCP.exe which you can call from command prompt. But i find it easier to use SQL :)

Christofer Ohlsson Over a year ago

One weird thing though, bulk insert then complains that "Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query." I am trying to insert two identical DateTime fields into two different columns. Both are DateTime, one allows NULL, the other doesn't. They look like this in the model: <FIELD ID="7" xsi:type="NativeFixed" LENGTH="8"/> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>

SqlKindaGuy Over a year ago

@ChristoferOhlsson can you make a new question with the code you use for insert. And show the data of your date columns

Christofer Ohlsson Over a year ago

Sure thing, the new question is here: stackoverflow.com/questions/47829486/…

Collectives™ on Stack Overflow

Creating SQL Server format file to use with bulk insert

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related