BCP returns no errors, but also doesn't copy any rows

Question

I'm trying to dump a very large set of data from a .csv file into a SQL Server 2012 database. Rather than doing thousands of INSERTs, I'm investigating bcp.

EDIT: This is for an automated process, and not just a one-off. I also do not have BULK INSERT rights to this database.

When I try to copy data into the database, bcp doesn't return any errors, but also doesn't actually copy anything - it just returns 0 rows copied. I've whittled this down to a minimal case that doesn't work.

First, create a simple table with two columns:

CREATE TABLE [dbo].[mincase](
    [key] [varchar](36) NOT NULL,
    [number] [int] NOT NULL

    PRIMARY KEY CLUSTERED 
    (
        [key] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 40) ON [PRIMARY]
) ON [PRIMARY]

GO

Then, use bcp to create a format file from this. Note that this example creates an XML format file, but it doesn't matter whether it's XML or native for this.

bcp MyDB.dbo.mincase format nul -T -n -f mincasexml.fmt -x -S .\SQLEXPRESS

Now create a data.csv file with one row and two entries, tab-delimited. In my case, the file is simply:

somecharacters  12345

Again, that's a tab, not two spaces, and it doesn't seem to matter whether there are trailing newlines or not.

Now try to use bcp with that format file to insert the data from this file:

bcp MyDB.dbo.mincase in data.csv -f mincasexml.fmt -T -S .\SQLEXPRESS

Rather than copying data to the database, I get this:

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Does anyone know what's going on here?

Thanks!

Answer 1

The bcp command typically needs an identifier to specify the format mode of the bcp file.

• -c specifies character (plaintext) mode
• -n specifies native mode
• -w specifies unicode mode

In your test case, the file you created is plaintext, so you should specify '-c' in your bcp in command.

bcp MyDB.dbo.mincase in data.csv -c -T -S .\SQLEXPRESS

Microsoft Recommends using the '-n' for imports and exports to avoid issues with field delimiters appearing within column values (See section on Character Mode and Native Mode Best Practices).