September 28, 2014 at 4:22 pm
Example: I have a 1,081,202,178 byte binary file consisting of 86,349 records, each 12,522 bytes in length. I want to use BCP to push them into a SQL Server 2012 table named WideBin.
The WideBin table consists of two fields:
1) ID BigInt Identity
2) Bin VarBinary(max)
The problem is the width of the records being greater than 8000 bytes. I can insert these records into the table programatically one-at-a-time using a parameterized query in code, but can't figure out how to do this with BCP (I want the speed of BCP - inserting one-at-a-time is painfully slow, and I have many files created, many with varying record lengths - and each imported record can not span fields or records in the table)
Now, if I use a format file and import those 12,522 byte records as 2 x 6261 byte records (splitting them in half), it's unusable for me, but it inserts the records and it's fast (~10,000 rows/sec):
D:\temp\v37.fmt...
11.0
2
1 SQLBINARY 0 0 "" 1 id ""
2 SQLBINARY 0 6261 "" 2 Bin ""
Execute...
bcp WideBin in d:\temp\sf_20140926_v37.0.sql -T -d Test -f d:\temp\v37.fmt
If I change the 6261 (in the format file) to 12522, it blows up because I've exceeded the SQLBINARY data type limit.
**How can I BCP in full 12,522 byte chunks to varbinary(max) records from my binary flat files?**
October 2, 2014 at 2:48 pm
Did you try -1 as the length? Just a guess, but that's how SQL represents "MAX" internally in its system tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply