Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [bcp]

Microsoft SQL Server's bulk copy utility

106 questions
14
votes
1 answer

Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between 50kb and 500kb, with a small percentage in the…
Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125
11
votes
1 answer

bcp command Incorrect syntax near '�'. Character is actually: "ä"

I have mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line: bcp DBname.dbo.Täble_Name out Täble_Name -c -k -S127.0.0.1 -Usa -PpassWord -r ~ I get this…
Houcine Nouri
  • 113
  • 1
  • 5
11
votes
3 answers

"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works

I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written. I noticed the files were piling up and BCP wasn't picking them up so I tested in Management Studio using the…
Brandon
  • 618
  • 2
  • 7
  • 17
9
votes
1 answer

BCP export more rows per batch to file (queryout)

I am using Windows 10 and running this straight from the command prompt (Administrator). Is there a way to change the row count being output to a text file? BCP "SELECT * FROM db.dbo.table WHERE row_date = '2016-10-11'" queryout "C:/Users/table.csv"…
trench
  • 235
  • 4
  • 8
8
votes
2 answers

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, and this matters more than performance…
Caitlin M. Shaw
  • 185
  • 1
  • 2
  • 6
8
votes
2 answers

Raise error if BCP command fails to dump data into a file

Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error in query written or an empty variable is passed to…
MySQL DBA
  • 502
  • 1
  • 5
  • 14
7
votes
1 answer

Unable to open BCP host data-file error

Having problem using BCP via xp_cmdshell. I exported the table data to a file as .dat but when I try to import the file to a new table, I am getting the below error. The SQL Server service and the account that I am using has full access on that…
db7
  • 1,371
  • 4
  • 16
  • 20
7
votes
3 answers

Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?

What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations. The bcp route for a 73.5 GB file filled up tempdb and started crashing other applications,…
Alex
  • 79
  • 1
  • 2
6
votes
2 answers

Insert custom header row in BCP output

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an export of our employee based data in a very strange…
JonH
  • 289
  • 1
  • 3
  • 15
5
votes
1 answer

bcp database migration

I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use bcp - database migration. In command line, I wrote…
Gurgen Hovsepyan
5
votes
2 answers

Minimum requirements to run Sql Server BCP.exe utility

I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe utility run without a SQL Server install? Thanks…
Sako73
  • 435
  • 3
  • 5
  • 8
5
votes
3 answers

How to import a varbinary(max) column with bulk import?

I have a varbinary(max) column that I need to bulk import. The data I'm putting into this column is a byte[] in C#. How do I format the data in the comma-delimited file used for bulk import? In other words, how do I convert from a byte[] to a…
Michael Hedgpeth
  • 1,361
  • 4
  • 15
  • 21
5
votes
3 answers

bcp in of datetime type data results in an "Invalid date / time format"

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited. I then bcp in to a truncated table with the same columns/format as the original table,…
user1073
5
votes
2 answers

BULK INSERT - best usage

I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table. I split 150 mln row into 3 BCP Export files each of 50…
yrushka
  • 1,994
  • 1
  • 16
  • 22
4
votes
1 answer

How to use bcp in on a table with an indexed view in SQL Server

I'm using SQL Server 2017 and want to use "bcp in" in a script to populate tables in several databases. I am unable to import data into a table that has an indexed view. The following is an MCVE that reproduces my problem: Run the script at the…
1 .
  • 143
  • 3
1
2 3 4 5 6 7 8