Hot answers tagged bcp
This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will ...
You have to use one of the SQL Server distributable packages to stay within licensing terms. These are not full installs. The one you want is "Microsoft® SQL Server® 2008 R2 Command Line Utilities" Note: copying bcp.exe by itself will not work
The first problem that I see is that you are concatenating Unicode and non-Unicode values together. A better way of dealing with this would be to keep the stored procedures in some sort of source control then deploy the stored procedures from source control after the database is restored. Or better yet restore the database you are testing to a different ...
If you make sure that your filesystems are on striped storage. That way you can prevent the io bottleneck. Just specifying different location without knowing what kind of storage they are on, does not make much sense. This workload typically has large scans and large writes so striping will certainly help you. Ronald.
It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like? Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx
A varbinary value is of the form 0x01020304 for SQL Server to read. So your CSV would be col1,col2,varbinary foo,1,0x01020304 bar,2,0x988776655443 Edit May 2013, After testing, I can't get this to work with BULK INSERT with or without a format file. I will post something more when I get it working. I can't delete this answer
As I wrote in the comment to your previous post: the bcp syntax look ok. I double checked with BOL and the only limitation for bcp is that if you use a procedure all tables have to exist prior to bcp being executed No temporary tables or bcp will fail! Update 1: Here is an example on how to exclude columns in the format file (*.fmt) for the bcp ...
You have CR\LF in shell invocation, a no-no. Make it a single line: DECLARE @SQL VARCHAR(8000); SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+ 'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+ '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; EXEC ...
Well we tend not to care what the originators table structure is, but only if it meets our requirements (which we send to them). If you are trying to figure out how to design a way to store the data permanently because you don't currently have a structure, then this is the method I use. Import the file into a staging table (not the final permanent table, I ...
You can use the BCP unicode native format to import/export. Use bcp -n for the export and the WITH (DATAFILETYPE='widenative') clause for import. Click link to read more.
At first I would try to change everything to be on the same line: Instead: SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; Change it to: SELECT @SQL = 'bcp ...
I believe that BCP does a two phase process. The first is done with a format only where the query isn't actually executed but it is parsed so that BCP can figure out the columns that will be returned and in the second phase it actually runs the query. You are failing on the first phase as the temp table doesn't exist so it can't be used for the format only ...
I have seen this before. Ensure your datetimes are formatted like this: yyyy-mm-ddThh:mm:ss Example: 2011-01-18T17:30:59 would represent January 18th 2011 at 5:30:59PM It has been a while since I ran into an issue similar to this, but I remember this being a step I took to resolve my problem.
Only top voted, non community-wiki answers of a minimum length are eligible