27

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 their position in the output rowset back to the original input? No, you can't rely on anything to be guaranteed without an actual documented guarantee. The ...


24

Suppose you have mydb.mytb and you want to create mydb.mytbcopy I have five(5) approaches to doing this copy APPROACH #1 In the mysql client, run the following USE mydb CREATE TABLE mytbcopy LIKE mytb; INSERT INTO mytbcopy SELECT * FROM mytb; APPROACH #2 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" mysql ${...


21

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 policy why they are concerned. However, I would guess that it probably has something to do with what BULK INSERT / SqlBulkCopy / BCP / OPENROWSET(BULK ...) allow ...


16

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 not having the plan. I'll go over a few ways of optimizing an insert but most of them probably don't apply for the insert you specified in the question. However,...


14

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide. At SSIS level, you can look into below things to speed up data read and data load : Fast Parse Option along with its limitations. Use the SQL Server Native ...


13

A few of these numbers do not seem to match the table on the technet page. There are small differences in the sizes of the log records generated in your tests, but these are due to other internal logging behaviours, not whether minimal logging is occurring or not. A good definition of minimal logging is provided by Sunil Agarwal of the Storage Engine team: ...


13

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 example: CREATE SCHEMA Test ; GO CREATE SEQUENCE Test.RangeSeq AS int START WITH 1 INCREMENT BY 1 CACHE 10 ; CREATE TABLE Test....


12

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


12

I agree that "correct answer" isn't correct. You can't rebuild indexes that have been dropped, you would need to create them again, and it would make no sense to do the insert last after the indexes have been dropped and created anyway. I presume the correct answer is in fact something like Disable the non clustered indexes. Do the Insert Rebuild the non ...


12

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 knowledge in the process. Gut Check: Is your firewall doing stateful, deep packet inspection? You won't find much on the Internet about this, but if your bulk ...


11

A BULK INSERT from our application was the cause of untrusted foreign keys, as confirmed by an MSDN question. Similar to BCP, anytime a BULK INSERT is performed then the foreign key is not checked during insert, therefor making it not trusted. As Kin mentioned, there are simple scripts available to find and fix untrusted foreign keys, but in my scenario the ...


11

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 pages aren't full, which means adding 15 more rows doesn't increase table size by (15 * initial size), it just means that the data page holding the initial row ...


9

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.


8

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


8

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: Bulk Loading an Empty, Nonpartitioned Table Loading data into a nonpartitioned table, while a simple operation, can be optimized in several ways. ......


7

Use a data-modifying CTE to chain the inserts in a single statement. Assuming from your example that you want to insert 1 for related_key_id in all rows. WITH ins_key AS ( INSERT INTO key (key_name) VALUES ($$key_1$$) , ($$key_2$$) RETURNING key_id -- return newly generated key_id(s) ) INSERT INTO related_key (related_key_id, ...


7

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 means "UTF-16 Little Endian"). It is just a simple miscommunication. The source of the communication breakdown (it's always the same, right ;-) is merely not ...


7

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 will have XML identifying marks at the beginning and end, which may be another drawback depending on how you want to use the output. But it'll be clicky and ...


6

You need to escape the single quotes in the parameters, like: exec (' bulk insert weblog from '''+@datapath+'access1.log'' with ( firstrow = 1, FIELDTERMINATOR ='' '', ROWTERMINATOR=''\n'', MAXERRORS=99999999 ) '); Use the PRINT command to see what the command contains prior to EXECing it: DECLARE @datapath VARCHAR(260); SET @datapath = 'C:\...


6

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 results, or as an easy retry point), you can make it temporary and unlogged: BEGIN; CREATE TEMP UNLOGGED TABLE intermediate (... columns matching the CSV ...


6

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 that? This will require your test database to use a recovery model of simple. You also need to be careful to follow the rules which allow you to get minimal ...


6

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 to MySQL 5.5 (Of course, I am joking). I have three suggestions. Please look into them. SUGGESTION #1 You could setup MySQL Replication and perform the ...


6

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 Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to ...


6

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 might lock a table for hours, preventing other inserts from happening (as well as select, updates, or deletes). Or, from a security perspective, it can produce ...


6

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 table are used to: Validate integrity constraints (those with business logic more complicated than is usually used in DB constraints) More importantly, to audit ...


6

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 control characters. You might be able to convert that column to XML, then double-click to view the data in a new tab. I describe this here, but for a different ...


6

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 in directly to a work station, and then connected remotely to SQL Server: this means that you connected to the server running SQL Server indirectly, by passing ...


6

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. Try CREATE TABLE [Temp] ( ... Also check for typos, did you mean to insert into Temp2 instead? You may need to specify the schema name. Are you trying to insert ...


6

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 rebuilding indexes every 5 to 10 minutes to be beneficial in forcing deleted record clean up. This dramatically improved read performance on the table. In our case, ...


6

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 the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the ...


Only top voted, non community-wiki answers of a minimum length are eligible