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 (...
- 93.3k
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 ...
- 8,879
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, ...
- 461
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 ...
- 154k
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 ...
- 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 ...
- 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 ...
- 174k
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
... --...
- 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 ...
- 72.1k
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 ...
- 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 ...
- 154k
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 ...
- 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 --;
- 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 ...
- 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.
...
- 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 ...
- 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 (...
- 154k
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'),...
- 93.3k
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 ...
- 51k
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 ...
- 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, ...
- 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 ...
- 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
...
- 154k
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 ...
- 93.3k
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 (
...
- 154k
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/...
- 177k
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 ...
- 177k
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 ...
- 8,879
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 ...
- 177k
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-...
- 2,218
Only top scored, non community-wiki answers of a minimum length are eligible