Skip to main content

Questions tagged [bcp]

Microsoft SQL Server's bulk copy utility

Filter by
Sorted by
Tagged with
0 votes
1 answer
291 views

How can I tell BCP to add headers to an output text file?

I am running this code; it only return data, how can I get headers in my file? EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM table1 " queryout G:\Backup\Test.txt -t"|" -c -T ...
Yulichka's user avatar
-3 votes
1 answer
75 views

Shortcutting/Clowning/Virtualising Azure SQL Database Bulk Loads

I'm currently working on a solution to bulk load (almost) structured data into an Azure SQL Database. The long term plan is to use ADF, but the client is keen to have some static data loaded early in ...
Vocoder's user avatar
  • 117
1 vote
2 answers
1k views

How to define text qualifier in BCP in

I have several csv files need to be imported in to sql database. The data in the file looks like following. productid,product,manufacturers[LF] 1001,TV,"Sony, LG, Panasonic, Toshiba"[CR][LF] ...
DBZHOU's user avatar
  • 11
-1 votes
2 answers
107 views

How to increment as 001,002 etc In SQL Server

i have created an sp to export scripts i stored in a table to text files. What this sp do is if I give 100 as count then each file will have 100 scripts from the table. i am saving the file name as ...
Subin Benny's user avatar
1 vote
0 answers
74 views

BCP to SQL Server DB seeing excessive waits

I have a process I built in Powershell which has been working flawlessly for years on a couple servers, but when I migrate it to new servers I see a lot of waits (ASYNC_NETWORK_IO) during BCP step ...
Bob Sacamano's user avatar
1 vote
0 answers
117 views

BCP out significant throughput drop while using read committed snapshot isolation

I am running bcp to output data to a file. I cannot run bcp out in read committed mode because of the reasons mentioned in snapshot replication - corrupted BCP files. I tried using read committed ...
toros's user avatar
  • 23
0 votes
1 answer
466 views

Bcp out - order and locking

I wanted to understand a bit more about the order in which data is exported using bcp out command. It's written in some places that it returns values in index order, but what if there is no index on ...
toros's user avatar
  • 23
1 vote
1 answer
765 views

Why is bcp out much faster than select * even when bcp uses select * internally?

I tried exporting a very large table (5B rows) to a csv file using both Select * and bcp out. Regarding Select *, I am using pyodbc module to query the database and writing it to a csv file. I also ...
toros's user avatar
  • 23
2 votes
1 answer
107 views

BCP Insert through View vs Manual insert

Consider the following code use tempdb go drop table if exists ImportTest1; create table ImportTest1 ( Column1 int null, Column2 int null, NewColumn int null ); insert into ImportTest1 select 1,...
kevinnwhat's user avatar
  • 2,224
4 votes
1 answer
687 views

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 ...
Joe's user avatar
  • 143
-1 votes
1 answer
472 views

cmdshell query Error "not a valid identifier"

I've included the entire code USE [JCIHistorianADX2New] GO /****** Object: StoredProcedure [dbo].[GetADX2PointsDyn] Script Date: 10/18/2021 5:45:38 AM ******/ SET ANSI_NULLS ON GO SET ...
gunterl's user avatar
0 votes
0 answers
533 views

Unable to open BCP host data-file with AzureDB

I'm trying to load a simple file with a single column from my local machine into my database which is located on Azure. Both file and folder are set with read/write permissions to EVERYONE but it ...
Wesley Costa's user avatar
-1 votes
3 answers
828 views

after insert trigger not firing after inserting with bcp

content of csv file TEST_1|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 5528, 'event_string': 'ENERGY_CTRL_RIGHT_PROX_DOWN', '...
vector8188's user avatar
2 votes
1 answer
198 views

How to BCP export three related tables in a transaction consistent manner?

Have three related tables that I would like to bcp export to file(s). This three tables have relationships to each other. Is there a good way to bcp export these tables in a transactionally ...
user2368632's user avatar
  • 1,097
2 votes
0 answers
2k views

Unable to resolve column level collations

This whole stored procedure is to generate a csv file into the mention folder where file is getting generated but the headers are missing. --Adding missing headers into the file---- DECLARE @...
sowmya sirsi's user avatar
0 votes
1 answer
225 views

BCP cannot read table created in the same transaction when executed from Control-M

How to export global temp table using BCP when original process locks table for BCP? We created our own exporting procedure where we provide SELECT or table_name and procedure stores data to global ...
owl's user avatar
  • 304
2 votes
2 answers
697 views

Why BCP creates such big files?

I'm creating some reports via BCP to be sent via emails. create TABLE ##tempsss ( create TABLE ##JOB_DataAssociacao_verContrato ( F1 VARCHAR(6) ,F2 VARCHAR(200) ...
Racer SQL's user avatar
  • 7,396
2 votes
0 answers
229 views

Problem with decimal point setting in Windows

I am using bcp.exe from a SQL Server 2017 installation on Windows 10 (build 19041). The OS language is English (US) but I use scandinavian settings for date, time, currency and decimal delimiter - the ...
Hans Olav Nymand's user avatar
0 votes
1 answer
431 views

OPENROWSET Bulk insert Text File NULL Columns

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
Stockburn's user avatar
  • 431
0 votes
1 answer
1k views

How to use BCP Utility queryout in a network path with crededential (login required) for SQL Server

I'm having a hard time figuring out on how can I export my selected table in a network path that requires login credentials. 'bcp "select ''StudentName'' UNION ALL SELECT StudentName AS ...
Lawless16's user avatar
0 votes
0 answers
169 views

Creating a Format File For Bulk import

So when I try implementing this query: BCP -SMSSQLSERVER01.[Internal_Checks].[Jan_Flat] format out ^ -fC:\Desktop\exported data\Jan_FlatFormat.fmt -c -T -UUGmn91 -SMSSQLSERVER01 -PChan I am getting ...
RAJAT 's user avatar
2 votes
1 answer
707 views

How to load a table using BCP

Context: I created a dump of a sql table in XML (Including data and schema) using this command from the command prompt. BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" ...
LP.'s user avatar
  • 23
0 votes
2 answers
623 views

What's the best way to migrate a database to a server of a lower SQL version?

I have Database A on SQL Server 1 that is SQL Server version 2016. I need to migrate it to Server 2 that is SQL Server version 2012. Database A is pretty small, only about 5 GB total. I don't think ...
J.D.'s user avatar
  • 38.1k
2 votes
1 answer
428 views

Importing a single large table in Azure

I have an existing DB called MyDb in Azure SQL Server. I have a bacpac of another DB with several tables in it. I'm interested in importing one single table (that table has no FK, it makes things ...
Askolein's user avatar
  • 131
3 votes
1 answer
755 views

Does ordinal position of columns matter for SQL Server replication

I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting. The ...
Booji Boy's user avatar
  • 145
1 vote
2 answers
449 views

How to copy table content before deleting to flat file using BCP

I have created stored procedure where, I delete data in any given time. I want to save the records to a flat file before deleting in case I need to import the data back to table. I DO NOT want to call ...
Mindan's user avatar
  • 127
8 votes
2 answers
3k views

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, ...
Caitlin M. Shaw's user avatar
1 vote
0 answers
1k views

What would be an appropiate BatchSize for bulk insert 34 GB of data (JSON files) into SQL Azure?

I got like 25,000 JSON files whose size (in average) is 1.2 MB (also, the average of records per JSON is 5,000), and the total size is like 34 GB. I have been reviewed the BCP utility from SQL Server, ...
curiousguy_08's user avatar
1 vote
1 answer
5k views

exporting a sql view to a csv file

Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice ...
Stl's user avatar
  • 21
0 votes
0 answers
266 views

Errors with trying to run a stored procedure with local file output

I'm trying to run a stored procedure that I'm passing some variables to. The output is being writted to a CSV file on a local file share. The CSV file gets created, but I'm hitting some errors ...
Douglas Owens's user avatar
0 votes
0 answers
4k views

Unable to open BCP host data-file

Having an issue with exporting files to a network drive via the BCP host data file. There is no problem in exporting a file to the C drive of the machine that the SQL Server is installed on, however ...
Mark's user avatar
  • 1
2 votes
1 answer
669 views

Can BCP Out cause resource contention

I had these two things running in parallel in the same database - BCP "SELECT * FROM [ServerName].[DatabaseName].[dbo].[TableName] WITH (NOLOCK)" queryout "\\LocationToMyOutputFile\TableName.csv" -c ...
Vivek Kumar Singh's user avatar
0 votes
0 answers
340 views

Sybase ASE 16.0 BCP in character problem

I am using SYBASE ASE 16.0 and in my tables i have some values like "KOCAELİ", "İZMİR", "TALATPAŞA" . They are readable in csv file however when i put database with bcp in command, i have ...
Melih's user avatar
  • 284
2 votes
1 answer
3k views

How to send parameters to BCP inside dynamic sql using sp_executesql

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a ...
GHauan's user avatar
  • 603
3 votes
1 answer
4k views

How to troubleshoot "BCP copy in failed"

I have a table that looks something like this in a SQL server database in Azure: CREATE TABLE [dbo].[TableName]( [col01] [int] IDENTITY(1,1) NOT NULL, [col02] [varchar](255) NOT NULL, [col03] [...
Erik I's user avatar
  • 183
0 votes
2 answers
2k views

Get Headers from SQL Table

I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"...., 'header50' as "header50" from MyTable) It doesn't work above a ...
Amandine FAURILLOU's user avatar
2 votes
0 answers
3k views

How do I handle the "Invalid field size for datatype" when using bcp?

I'm trying to get a database into Azure SQL Database using bcp, but first I'm trying to get it to load into a local database on my dev machine. I've only used bcp once before, years ago, so I'm a ...
Rod's user avatar
  • 121
2 votes
0 answers
3k views

Unexpected EOF encountered in BCP data-file

I am trying to load a dump file into a SQL Server table using bcp.exe but it fails to insert one row. Input Data File Content [\\\\SERVER\INPUT_SOURCE_FILE.DAT]: COL001|||COL002|||COL003XXDATA001|||...
Jenish Zinzuvadiya's user avatar
0 votes
1 answer
480 views

BCP via Linux Centos 7, cannot connect to Azure SQL

I installed sqlcmd, bcp and odbc 17 for microsoft. then I connected to Azure SQL with SQLCMD - it worked OK then I tried to import some data with BCP and i got an error I use: bcp schema....
Vadzim Nemchenko's user avatar
4 votes
2 answers
2k views

Use non-printable characters in SQL Server BCP xml format file as TERMINATOR

I have a flat-text data file containing records which fields are separated by the non printable character "File Separator" (0x1c). I am trying to use SQL Server's bcp utility to load this data into my ...
Wouter's user avatar
  • 821
0 votes
1 answer
895 views

how to delete the file which is open in bcp.exe

I have run the bcp command in query window to generate the tsv file. In between I cancelled the query. I tried to kill the SPID. But it is killed/rollback. I tried to delete the tsv file in server. ...
user2741663's user avatar
1 vote
0 answers
2k views

Exporting to Excel from SQL Server - BCP Using a Stored Procedure Headers not shown

I am running a Stored procedure to export data to an Excel sheet. Issue is that The column headers are being displayed at the bottom or they are missing at times. What am I missing here ? SQL Server ...
Heisenberg's user avatar
  • 1,505
2 votes
1 answer
1k views

MSSql bcp transaction rollback on error

I am trying to bulk insert data from a file to MS SQL Server using the bcp utility but it is not functioning as I expect it to with regard to errors. I want to understand what I'm doing incorrectly or ...
Jayden's user avatar
  • 141
0 votes
1 answer
169 views

Is BulkCopy available for SQL-Server 2012 [closed]

Is BulkCopy still supported by SQL-Server 2012?
Anamika's user avatar
  • 11
11 votes
1 answer
3k views

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 -...
Houcine Nouri's user avatar
2 votes
1 answer
1k views

Exporting/Importing data from SQL Server 2014 to PostgreSQL 9.5 with tab delimited files

Not sure if anyone has run into this problem before and I am going a little bit crazy. I am exporting a table from SQL Server 2014 using BCP to a tab delimited text file and then uploading the file ...
Dina's user avatar
  • 1,507
7 votes
3 answers
4k views

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 ...
Alex's user avatar
  • 79
4 votes
2 answers
21k views

BCP woes: Unexpected EOF encountered in BCP data-file

I have a table defined as such: CREATE TABLE [dbo].[IpMetadata]( [StartIp] [bigint] NOT NULL, [EndIp] [bigint] NOT NULL, [CountryCode] [char](10) NOT NULL, [ProxyType] [varchar](50) ...
Jeremy Holovacs's user avatar
0 votes
1 answer
2k views

Sql server bulk insert remove text qualifier

I have a flat file having hundred thousand of records separated by special kind of character, I have used format file for removing that text qualifier. Works fine with comma separated file. Note: I ...
Jenish Zinzuvadiya's user avatar
1 vote
1 answer
5k views

Bulk insert top 1000 rows from a big flat file

I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?
Jenish Zinzuvadiya's user avatar