Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [bulk-insert]

225 questions
26
votes
1 answer

Is it safe to rely on the order of an INSERT's OUTPUT clause?

Given this table: CREATE TABLE dbo.Target ( TargetId int identity(1, 1) NOT NULL, Color varchar(20) NOT NULL, Action varchar(10) NOT NULL, -- of course this should be normalized Code int NOT NULL, CONSTRAINT PK_Target PRIMARY KEY…
ErikE
  • 4,355
  • 4
  • 29
  • 39
21
votes
4 answers

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 programmers? I can't believe the "filling up the…
JackLThornton
  • 313
  • 1
  • 2
  • 5
16
votes
3 answers

Copy from one MySQL table to another MySQL table of same database

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?
Devashish Dixit
  • 263
  • 1
  • 2
  • 4
15
votes
4 answers

Bulk Insert Through Network

Can someone help me with these? BULK INSERT DATABESE01.dbo.TABLE01 FROM '\\COMPUTER01\FOLDER01\TextFile.txt' WITH ( FIELDTERMINATOR = ' ', rowterminator = '\n', tablock ) The error shows, couldn't open: Could not bulk insert because…
Jonas Ivy V. Imperial
  • 153
  • 1
  • 1
  • 6
15
votes
3 answers

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, during the development I start the profiler and…
Alexei
  • 1,191
  • 1
  • 14
  • 36
13
votes
3 answers

How to insert 10000 new rows?

Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id). I have used: INSERT INTO bins (id) VALUES (1) to create a single…
Andrew
  • 131
  • 1
  • 1
  • 3
12
votes
2 answers

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 Windows Server 2016 File Server Failover Cluster, but…
Muh Fugen
  • 221
  • 2
  • 4
11
votes
2 answers

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) SELECT FROM ... After the job hangs a bit, one of the…
SqlWhale
  • 137
  • 2
  • 9
11
votes
1 answer

Foreign keys become untrusted after bulk insert

In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet) with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are consistently marked as not trusted in the database. I've…
LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
10
votes
1 answer

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting 150 Millions data in one execution. We are…
Virul Patel
  • 171
  • 1
  • 3
  • 8
10
votes
2 answers

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a) SELECT p.id, a FROM p, unnest($2::text[])…
user32234
9
votes
1 answer

Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about when minimal logging does or does not occur. Using…
user2920708
9
votes
5 answers

Copying a table (and all of its data) from one server to another?

I have a massive table, let's say 500,000 rows. I want to copy it (schema and data) from one server to another. This is not an upsert or any kind of update; It's a one-off straight copy and paste. What are the idiomatic approaches to this? I've…
J. Mini
  • 1,161
  • 8
  • 32
9
votes
1 answer

What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?

I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table. Note this is part of a bulk insert of several tables, where those other tables…
Daniel James Bryars
  • 701
  • 1
  • 5
  • 17
9
votes
1 answer

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 & v2016) using windows auth I have sysadmin permissions I…
Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116
1
2 3
…
14 15