Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

The tag has no usage guidance.

-3
votes
0answers
32 views

SQL Server Bulk Insert Performance: Bulk Logged or Simple Recovery Mode? [on hold]

We are taking data from flat files, and using BULK INSERT to place them into SQL Server tables. What is faster for bulk insert from text files into database? Bulk Logged or Simple? Or they the same? ...
3
votes
1answer
49 views

leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk

I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain. I've a clustered index on a table of about 66 million rows and rows are added throughout ...
-3
votes
1answer
35 views

Database name 'mydb' ignored, referencing object in tempdb. Bulk Insert

I'm trying to insert data from an xlsx file into my temporal table. I've also tried with a real table, but it throws me the following error: (0 rows affected) I'm pretty sure my error is in the ...
-3
votes
1answer
158 views

Bulk Insert csv file error [closed]

I am trying to BULK INSERT a csv file to my SQL Server. Using code I found on this SO question, I ran: BULK INSERT Temp FROM 'c:\csv\student.csv ' WITH ( FIELDTERMINATOR = ',', ...
1
vote
0answers
160 views

Create BCP Format File From SSMS

I want to create a BCP format file from within SSMS. Is there anyway to do this? All the sources are asking to use the BCP command line. I would rather utilize in SSMS window. https://docs.microsoft....
0
votes
1answer
109 views

SQL Bulk import vs SSIS in Simplicity

We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week. All ...
2
votes
0answers
180 views

Why is Insert-Ignore so expensive in MySQL?

TL;DR: Inserting a large number of rows with no collisions is MUCH faster than (not) re-inserting the same rows, BOTH using the INSERT IGNORE syntax. Why is this? I would assume that the index lookup ...
2
votes
0answers
73 views

Bulk Insert - Load Order

We are currently seeing something strange with our overnight file loads into SQL Server. The SQL Server version is 2014 SP2. We use BULK INSERT for the majority of file loads. It is important to us ...
2
votes
0answers
211 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
0
votes
1answer
100 views

Inserting 3 lakh (300'000) records into a table [closed]

I have a table named transaction into which I want to insert 3 lakh (300'000) records. I need it to be created as dynamic query instead of writing thousands of insert statements. For example : ...
5
votes
1answer
605 views

BULK INSERT getting “Cannot bulk load because … Access is denied” on a file I have access to

I'm trying to run a BULK INSERT but I'm getting an access denied error. So far I've tried the following: I can open the file from my machine I'm logged into the SQL Server (tried v2008 R2 & ...
3
votes
3answers
228 views

How to retrieve a large text from inside a table?

for my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
5
votes
2answers
561 views

SQL Server Bulk Insert properly interprets some Unicode characters but not others?

For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters: C9 (É) into 2B (+) A1 (¡) into ED (í) A0 ( ) into E1 (á) AE (®) into AB («) CB (Ë) into 2D (-) D1 (...
0
votes
0answers
59 views

Insert into …Select from - Still running but no longer inserting rows

We are transferring data into a new bigint table as we are running out of INTs in the current version. To avoid a large file growth of the logs we are using the following statement on batches and ...
0
votes
0answers
25 views

Drop and Restore Foreign Keys in Postgresql

I have seen this in the official docs: https://www.postgresql.org/docs/9.1/static/populate.html "Therefore it may be necessary, not just desirable, to drop and re-apply foreign keys when loading ...
0
votes
0answers
169 views

How to import a varbinary column with bcp?

In my current project I have to import data to SQL server. The exported data is CSV file and binaries. The CSV file contains data in the format below UniqueID_BinaryFileName All binary files ...
3
votes
1answer
302 views

Multiple inserts of batch insert over the wire?

I am generating about 500 records. What I want to do is INSERT them all at the same time because: The server is located elsewhere and I would rather send one big payload over. This seems like less ...
0
votes
1answer
90 views

What is a staging table and how do you protect data loss when transferring the data?

What is a staging table? What is it used for? Are there best practices that should be applied? I'm working in Oracle right now. Are staging tables platform dependent? I'm performing a bulk copy with ...
1
vote
0answers
367 views

Incorrect syntax near 'BULK' for bulk INSERT in Azure Data Studio

I am trying to bulk insert a large CSV file delimited by tabs. I am on a Mac, and executing queries on Azure Data Studio (formerly SQL Operations Studio), since SSMS is not available for Macs. Here is ...
18
votes
4answers
7k views

Why is BULK INSERT Considered Dangerous?

I would like to understand why cyber-security teams in general (more than one organization I've dealt with) is dead set against granting BULK INSERT (e.g. TSQL) rights to applications and database ...
2
votes
1answer
847 views

PostgreSQL: Why is CREATE TABLE AS faster than CREATE … INSERT INTO?

Below are the two different syntaxs for the same thing. With a COPY TABLE AS SELECT (CTAS). CREATE TABLE main AS SELECT * FROM other; As separate statements with CREATE TABLE and INSERT INTO ...
0
votes
1answer
357 views

SQL server bulk insert fail code 5 [SQLSTATE 42000] (Error 4861)

Following error occurs on running sql job. Cannot bulk load because the file could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861). The step failed. ...
2
votes
1answer
94 views

Reducing Memory Pressure When Performing Parallel Staging Table Loads

I'm attempting to migrate several very large tables in my data warehouse to new partition scheme (the older partition scheme was based on a RANGE LEFT function, that wasn't regularly split, and the ...
2
votes
1answer
654 views

Postgres COPY, is it possible to load multiple files into the same table?

Is it possible to execute several \copy commands in parallel against the same table? I know something similar exists with pg_restore, but that splits the work up amongst tables. I'm working with ...
4
votes
2answers
731 views

SSMS - Can not bulk load because the file could not be opened

We have a user who get this error message every time bulk load using network location it throws error below. "Cannot bulk load because the file could not be opened. Operating system error code 3(...
1
vote
0answers
107 views

Increase Bulk load

We have a SSIS package which do bulk insert in batch. But from couple of days the duration of the job which runs the package has been increased. Its running for 4-5 hours which usually used to get ...
1
vote
2answers
558 views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
0
votes
1answer
41 views

Transfer of data (lots) from NAS based MariaDB to Postgres at AWS

I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run. The remote systems are Rasp Pi based, running Python, logging to MariaDB ...
1
vote
0answers
162 views

bulk-insert import fails, can't handle some forward slashes in data

Table like so: [Hotelid] [int] NULL, [CountryCode] [nvarchar](10) NULL, [CountryName] [nvarchar](100) NULL, [State] [nvarchar](100) NULL, [CityName] [nvarchar](100) NULL, [DisplayName] [nvarchar](100)...
4
votes
1answer
5k 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) ...
1
vote
0answers
105 views

MySql slave thinks that the query didn't run on the master, no helpful error

We have a master-slave replication that was running perfectly until recently. Today i've found the error: Query caused different errors on master and slave. Error on master: message (format)='Size ...
0
votes
1answer
579 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 ...
1
vote
1answer
2k 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?
1
vote
1answer
764 views

BULK INSERT runs fine in window, but fails inside sql job

I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2. In the Server A, files are ...
1
vote
1answer
233 views

Bulk insert and fast ordered search large data 3B+ records

Scenario We want to study behavior of some similar entities that changes based on time. Currently I'm using MS SQL Server 2016 Table Structure CREATE TABLE [dbo].[BehaviorHistory]( [EntityId]...
7
votes
2answers
2k views

SQL Server avoid repeating the collation type multiple times in a query

I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. ...
10
votes
2answers
1k views

Tablock hint triggers deadlocks

I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form. INSERT INTO Table (TABLOCK) ...
1
vote
1answer
692 views

How to implement on duplicate update for SQL Server?

I'm trying insert/update data for table T1(int id,char name, float data), which has unique a index on id and name. I'm using SQLBulkOperation(SQL_ADD) to add records to the table, and ...
5
votes
2answers
259 views

How to keep smaller transaction logs files while rebuilding index of very large table

I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management. Background The target database is a DW database hosting 3 big ...
0
votes
0answers
112 views

EF vs SP performance inserting multiple rows

I know of three most common ways of doing so: Using a relation level as Entity Framework, which as far as I know has the worst performance. Using a bulk insert, such as INSERT INTO table1 (x,y) ...
0
votes
1answer
203 views

LOAD txt file DATA INFILE issues

I have this project I am working on, below is the sample of the text file I need to load into my database. routes.txt: "route_id","origin","destination","distance" 1,"ABE","ATL",692.000 2,"ABE","DTW"...
6
votes
2answers
957 views

How does one investigate the performance of a BULK INSERT statement?

I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
12
votes
2answers
733 views

Configure unconstrained delegation for BULK INSERT

I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a ...
1
vote
3answers
816 views

Bulk-Insert Select performance over individual Inserts

I am using sequelize as ORM for my nodejs application. The scenario is, I have to insert a Poll and its Options and get their objects. Approach 1: 1 - Insert Poll: INSERT INTO `polls` (`id`,`...
1
vote
1answer
438 views

BULK INSERT syntax error [closed]

While inserting bulk data in sqlserver with query BULK INSERT Table_name FROM 'FilePath' WITH ( FIELDTERMINATOR ',', ROWTERMINATOR '\n') ; I'm getting error "Incorrect syntax near '('." I ...
7
votes
2answers
2k views

Deadlock in INSERT … ON DUPLICATE KEY UPDATE

We have a table defined like this: CREATE TABLE ipsum ( id char(40) NOT NULL, source char(40) NOT NULL, ip_address varbinary(16) NOT NULL, port smallint(5) unsigned NOT NULL, ...
0
votes
1answer
76 views

PostgreSQL splitting one table into three and setting up references between them

My title is a little misleading, what I am doing is inserting data from a CSV file where each row has information corresponding to three different tables. You can imagine the schema being something ...
0
votes
1answer
826 views

Exception while inserting records in SQL Server (Error message id : 586)

I am getting an exception when doing a batch insert operation using a prepared statement from Java. java.sql.BatchUpdateException: The prepared statement handle 5 is not valid in this context. ...
3
votes
1answer
396 views

using sp_executesql to run bulk insert

I am trying to execute bulk insert statement using sp_executesql. I received syntax error: Msg 102, level 15, state 1Incorrect syntax near '\t'. my code block are based on this link Please ...
4
votes
1answer
377 views

SQL Server DB table data size query result not increasing if data count increasing

I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing TableName ...