Hot answers tagged

56 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 (...
user avatar
39 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 ...
user avatar
34 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, ...
user avatar
28 votes
Accepted

Does Postgres preserve insertion order of records?

The answer for this simple case is: "Yes". Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will ...
user avatar
28 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 ...
user avatar
  • 78.4k
25 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 ...
user avatar
  • 30.7k
24 votes
Accepted

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

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 ...
user avatar
24 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 ... --...
user avatar
  • 21.3k
23 votes

Why is "INSERT INTO table;" a syntax error?

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause. INSERT INTO people ...
user avatar
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 ...
user avatar
  • 72.1k
17 votes
Accepted

How to cast to int array in Postgresql?

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to ...
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 ...
user avatar
  • 10.3k
16 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 --;
user avatar
  • 937
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 ...
user avatar
  • 27.4k
15 votes

Insert into table select * from table vs bulk insert

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. ...
user avatar
  • 61.3k
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 ...
user avatar
  • 3,953
15 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 (...
user avatar
15 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'),...
user avatar
14 votes

Sql server table insert performance optimization

What are the possible reasons of the slow table insert? What are ways to identify this bottleneck without the execution plan? Read How to analyse SQL Server performance, specially the part about ...
user avatar
13 votes
Accepted

order of inserting record is mismatching

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest. Since you have ...
user avatar
  • 15.4k
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, ...
user avatar
  • 41.4k
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 ...
user avatar
  • 21.7k
11 votes
Accepted

INSERT from a SELECT combined with a single value

Basic INSERT INSERT INTO item_tags (item_id, tag_id) SELECT p.item_id, t.id FROM (SELECT item_id FROM properties WHERE name LIKE 'body') p , (SELECT id FROM tags WHERE name ILIKE '%hoax%') t ...
user avatar
11 votes

join value in a RETURNING which is not inserted

Perhaps there's a better alternative but I can only think of joining back to the 2 tables. This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable: with ...
user avatar
11 votes
Accepted

INSERT multiple values via join

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs. INSERT INTO groupmember (person_id, group_id) SELECT p.person_id, g.group_id FROM ( ...
user avatar
11 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/...
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 ...
user avatar
10 votes
Accepted

Insert into select into multiple related tables using INSERT_IDENTITY

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key ...
user avatar
10 votes
Accepted

How to determine if insert or update

Since it's only UPDATE,INSERT you can say: IF EXISTS (SELECT 1 FROM deleted) -- update ELSE -- insert You have a bigger problem, though. There is no such thing as new.Id, and an insert or update ...
user avatar
10 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-...
user avatar
  • 2,218

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