31
votes
Accepted
Is it safe to rely on the order of an INSERT's OUTPUT clause?
Can I rely on the returned identity values from the dbo.Target table
insert to be returned in the order they existed in the 1) VALUES
clause and 2) #Target table, so that I can correlate them by ...
- 86.5k
26
votes
How to insert 10000 new rows?
You can use the generate_series() set returning function:
INSERT INTO bins (id)
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;
- 98.7k
21
votes
Accepted
Why is BULK INSERT Considered Dangerous?
Given that there are probably just as many unfounded fears as there are unknown risks, I would think that it is difficult to really say why a policy is in place without asking whomever created the ...
- 69.5k
18
votes
Accepted
How does one investigate the performance of a BULK INSERT statement?
As far as I can tell you can optimize a bulk insert in a very similar way that you'd optimize a regular insert. Typically, a query plan for a simple insert isn't very informative so don't worry about ...
- 32.6k
16
votes
Accepted
Copying a table (and all of its data) from one server to another?
Create a new DB on the same server.
Use SELECT INTO to clone the table into that DB.
Backup that now-tiny DB.
Now you have a backup containing the data that you want, and nothing else, so it can't be ...
- 286
15
votes
How does one investigate the performance of a BULK INSERT statement?
I'm going to start answering this question, with the intention of continuously updating this answer as I build a knowledge base of tricks. Hopefully others come across this and help me improve my own ...
- 465
14
votes
Accepted
What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?
You can use procedure (introduced in SQL Server 2012):
sp_sequence_get_range
To use it you need to create a SEQUENCE object and use it as a default value instead of IDENTITY column.
There is an ...
- 1,591
12
votes
Copying a table (and all of its data) from one server to another?
What I've used a lot is native SQL Server Import and Export Wizard.
In SSMS's Object Explorer -> Source SQL Server -> Right click database -> Tasks -> Export Data
Choose a Data Source:
...
- 5,964
11
votes
Accepted
SQL Server DB table data size query result not increasing if data count increasing
SQL Server stores data on 8kb pages. You can't have a table that's smaller than than two pages. So when you add one row to the table, you have a minimum space consumption of 2 * 8kb = 16kb. But those ...
- 182k
9
votes
Accepted
SQL Server avoid repeating the collation type multiple times in a query
You can once create a view using column definitions like this:
FirstName COLLATE SQL_Latin1_General_CI_AS, ...
Then anytime you'd like to use User tables, you can use this view instead.
- 11.2k
9
votes
Copying a table (and all of its data) from one server to another?
One option is to use copy-dbadbtabledata from https://docs.dbatools.io/ powershell module.
copy-dbadbTableData -SqlInstance sourceserver -Database sourcedatabase -Table dbo.sometablename -Destination ...
- 3,322
8
votes
Tablock hint triggers deadlocks
The Data Loading Performance Guide was written for SQL Server 2008 but as far as I can tell Microsoft hasn't made any improvements in this area for heaps. Here's a quote for your loading scenario:
...
- 32.6k
8
votes
Accepted
SQL Server Bulk Insert properly interprets some Unicode characters but not others?
This is not a bug in SQL Server (or even in Windows), nor is it a situation that requires the additional step of converting the file into another encoding (i.e. into "Unicode", which in Windows-world ...
- 69.5k
8
votes
How to retrieve a large text from inside a table?
There's another way similar to Aaron's, using the little-documented processing-instruction... thing.
Using this gives you unchecked XML, which will won't replace predefined entities.
Also, the column ...
7
votes
Accepted
BULK INSERT getting "Cannot bulk load because ... Access is denied" on a file I have access to
The problem is that:
You are logging in using Windows Authentication: this means that SQL Server will attempt to impersonate your Windows / Domain account for the file system access.
You have logged ...
- 69.5k
6
votes
Accepted
MySQL - INSERT DELAYED <- deprecated What now?
According to the MySQL 5.5. Documentation
INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not support DELAYED, an error occurs.
You could downgrade ...
- 184k
6
votes
Import CSV in Postgres with static data in other columns
You will have to create an intermediate table as the target of COPY, and then insert into the final table in the second step.
If you don't have to keep the intermediate table (for cross-checking your ...
- 31.6k
6
votes
Accepted
Fast way to load a large amount of test data
Here are a few guiding principles to load not small amounts of test data:
1) When possible use minimal logging.
If you can avoid writing unnecessary data to the transaction log, great, why not do ...
- 32.6k
6
votes
Accepted
Bulk insert top 1000 rows from a big flat file
bcp Import
You can import data from your huge data file using Microsoft SQL Server's bcp utility from the command prompt.
bcp Description
The bcp utility bulk copies data between an instance of ...
- 18.3k
6
votes
Why is BULK INSERT Considered Dangerous?
Another possibility is the impact of running a BULK INSERT operation.
Normally, this sort of thing would be run off-hours when possible, so as not to interfere with normal activity. A bulk insert ...
- 11.7k
6
votes
Why is BULK INSERT Considered Dangerous?
This was kind of alluded to in an earlier answer ("... disable triggers"), but does not explain why disabling would be un-desirable from business standpoint.
In many businesses, triggers on main ...
- 183
6
votes
How to retrieve a large text from inside a table?
You can't copy from text output, because it truncates the data.
You can't copy from grid output, not only because it truncates the data, but it also mangles carriage returns, line feeds, and other ...
- 182k
6
votes
Bulk Insert csv file error
Your error message means the object can't be found in the context of the session where you're executing your BULK INSERT. This could be because...
The object does not exist and needs to be created. ...
- 5,001
6
votes
Accepted
Best way to configure and maintain a table with many insertions and deletions that's empy on average?
In my experience with SQL Server 2016 Enterprise Edition in a similar process where a permanent table is used as a message queue - so it has a very high rate of inserts and deletes - we found ...
- 287
6
votes
Does SQL Server Support Parallel Bulk Inserts From Multiple Connections?
Does Microsoft have any sort of documentation on this?
Yes, lots. One particularly good reference is the Data Loading Performance Guide.
I am trying to understand what is happening under the hood on ...
- 90.3k
6
votes
Does simple recovery model not log bulk inserts?
INSERT can be minimally logged in some cases. See: Prerequisites for Minimal Logging in Bulk Import.
Note if you switch to SIMPLE recovery your log chain is broken, and you'll need to start with a ...
- 47.7k
5
votes
Accepted
Not displaying special characters imported from a file
If characters are being imported incorrectly, then there is a problem with one (or both) of the following areas, as this is really a two-step process:
BCP / BULK INSERT does not know how the file is ...
- 69.5k
5
votes
SQL Server avoid repeating the collation type multiple times in a query
Collations are per each string column / expression (XML datatype excluded). So in this sense, no, Collations cannot be set per Table or query.
I need to address the collation difference in my query ...
- 69.5k
5
votes
PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO?
CREATE TABLE AS has some advantages over the other form, namely in the reduction or elimination of WAL.. Certainly optiimizations can be applied to a few commands (viz. CREATE TABLE AS, CREATE INDEX, ...
- 64.7k
5
votes
Accepted
Why BULK INSERT insert data in random order?
The rows from the file are read in order, and added to the table in the same order.
The issue occurs when you read rows from the table. Without an ORDER BY clause on your SELECT, SQL Server is free ...
- 90.3k
Only top scored, non community-wiki answers of a minimum length are eligible