Hot answers tagged

58 votes
Accepted

How to limit maximum number of rows in a table to just 1

These two constraints would do: CREATE TABLE dbo.Configuration ( ConfigurationID TINYINT NOT NULL DEFAULT 1, -- the rest of the columns CONSTRAINT Configuration_PK PRIMARY KEY (...
ypercubeᵀᴹ's user avatar
48 votes

How to insert values into a table from a select query in PostgreSQL?

INSERT INTO test_import_two (name, name1, name2) (SELECT name, name1, name2 FROM test_import_one WHERE id = 2) For same table INSERT INTO test_import_three (id1, name1, name2) (SELECT 216 ,name1, ...
Nirmal Sharma's user avatar
44 votes
Accepted

Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

Actually, you can achieve the same thing by changing your INSERT to a MERGE. While the MERGE statement is actually a pretty neat way to do "upserts" in SQL Server, there's nothing to stop you from ...
Daniel Hutmacher's user avatar
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 ...
Martin Smith's user avatar
  • 84.4k
30 votes

Benefits of using WITH TABLOCK on an INSERT

I know of a few benefits but they're mostly situational. Using TABLOCK will reduce concurrency but will immediately take a table lock on the target table. As long as you can guarantee that just one ...
Joe Obbish's user avatar
  • 32.1k
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) ;
ypercubeᵀᴹ's user avatar
25 votes

How to limit maximum number of rows in a table to just 1

You could define the ID as a computed column evaluating to a constant value, and declare that column to be unique: CREATE TABLE dbo.Configuration ( ID AS CAST(1 AS tinyint), -- or: AS bit ... --...
Andriy M's user avatar
  • 22.9k
21 votes
Accepted

Single-row INSERT...SELECT much slower than separate SELECT

SQL Server chooses to scan the heap tables on the inner side of the loops joins using row-level locks. A full scan would normally choose page-level locking, but a combination of the size of the table ...
Paul White's user avatar
  • 83.7k
18 votes
Accepted

postgresql insert from select query, plus static values

You can put static values into SELECT clause. INSERT INTO new_tbl (column1, column2, column3) SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;
shx's user avatar
  • 1,017
18 votes
Accepted

How to perform conditional insert based on row count?

Typically, you have a team table (or similar) with a unique team_id column. Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id). Then, to avoid complications (...
Erwin Brandstetter's user avatar
17 votes
Accepted

PostgreSQL/Psycopg2 upsert syntax to update columns

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice: insert_sql = ''' INSERT INTO {t} (id,col1, col2, col3) VALUES (%s, %s, NULLIF(%s, 'nan'),...
ypercubeᵀᴹ's user avatar
17 votes
Accepted

Disk space full during insert, what happens?

The actual query wasn't executed in an explicit transaction though, can that explain the existence of the target table? Yes, exactly so. If you do a simple select into outside of an explicit ...
sepupic's user avatar
  • 10.7k
16 votes

Disk space full during insert, what happens?

You're correct, the SELECT...INTO command is not atomic. This wasn't documented at the time of the original post, but is now called out specifically on the SELECT - INTO Clause (Transact-SQL) page on ...
Josh Darnell's user avatar
  • 29.2k
15 votes
Accepted

Change IDENTITY_INSERT to ON in SQL server

Explicit identity insert require IDENTITY_INSERT property set to ON. SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into -- the ...
AA.SC's user avatar
  • 4,053
13 votes
Accepted

SQL Server Concurrent Delete/Insert Application

The only query in what you're showing above appears to be this one, repeated a few times: IF EXISTS ( select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, ...
Brent Ozar's user avatar
  • 42.9k
12 votes
Accepted

Check if the trigger was fired by INSERT or DELETE

As long as the trigger is only for insert/delete: IF EXISTS (SELECT 1 FROM inserted) -- I am an insert ELSE -- I am a delete And in fact if you are doing things based on a join to inserted/...
Aaron Bertrand's user avatar
12 votes

Does the spec permit column-count mismatch on INSERT?

Draft documents can be found at: http://www.wiscorp.com/sql20nn.zip The part that is of interest for this question is: 7IWD2-02-Foundation-2011-12.pdf At first I could not find any support for ...
Lennart - Slava Ukraini's user avatar
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 ...
Aaron Bertrand's user avatar
11 votes

Inserts in MySQL 8 are slower than Inserts in MySQL 5.7

A difference between 5.7 and 8.0 is that binary logging (to be used for replication and PITR) is on by default in MySQL 8.0. To run without binary logging in 8.0, start the MySQL server with --disable-...
Sybil's user avatar
  • 2,488
10 votes
Accepted

Insert multiple rows into a table with only an IDENTITY column

As of writing this, there is no way to insert multiple rows into just an IDENTITY column using an INSERT statement. The DEFAULT VALUES placeholder stands for just one row. And the INSERT ... SELECT ...
Andriy M's user avatar
  • 22.9k
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.
sepupic's user avatar
  • 10.7k
9 votes

Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

The output clause can only access data in the target rows and constants/variables, not data from elsewhere in the source SELECT, like if you were operating in a trigger. https://learn.microsoft.com/...
David Spillett's user avatar
9 votes
Accepted

Insert Into table Exec SP with bad performance

INSERT...EXEC might prohibit parallelism, but I highly doubt that is the main issue here. The problem is that INSERT...EXEC works differently than INSERT...SELECT. When inserting the results of a ...
Solomon Rutzky's user avatar
9 votes
Accepted

Conditional INSERT with a nested CTE?

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense. You cannot nest a data-modifying CTE like you tried (as you ...
Erwin Brandstetter's user avatar
9 votes

At which point does SQL Server validate data for insertion

In all honesty, it depends on exactly how the query is processed, and it is certainly possible that changing the query (even though it yields the same end results) can make expressions (like attempted ...
Aaron Bertrand's user avatar
8 votes
Accepted

Speeding up insert in SQL Server

Without more detail about the procedure[1] and the table it is inserting into we can't offer any specific help. Some general advice though: If you are inserting the rows individually, try to arrange ...
David Spillett's user avatar
8 votes
Accepted

Progress of SELECT INTO statement

I suspect that rows in sys.partitions is 0 due to not being committed yet. But this does not mean that SQL Server is unaware of what will go there if the Transaction does commit. The key is in ...
Solomon Rutzky's user avatar

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