91
Your syntax is almost good, needs some parenthesis around the subqueries and it will work:
INSERT INTO bar (description, foo_id) VALUES
( 'testing', (SELECT id from foo WHERE type='blue') ),
( 'another row', (SELECT id from foo WHERE type='red' ) );
Tested at DB-Fiddle
Another way, with shorter syntax if you have a lot of values to insert:
...
54
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 (ConfigurationID),
CONSTRAINT Configuration_OnlyOneRow
CHECK (ConfigurationID = 1)
) ;
You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the ...
50
Plain INSERT
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM (
VALUES
(text 'testing', text 'blue') -- explicit type declaration; see below
, ('another row' , 'red' )
, ('new row1' , 'purple') -- purple does not exist in foo, yet
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f ...
answered Jul 16 '13 at 20:40
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
32
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 using it just for the purpose of inserting:
-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));
-- My data I want to ...
29
Suppose I have to export data from one server to another.
Best is to use
IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS
IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN
TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance.
Executing in ...
26
One common approach:
Disable / drop indexes / constraints on target table.
INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this (...
26
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 be fetched in that order.
But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries ...
answered Mar 20 '15 at 22:47
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
26
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 ...
25
Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.
You need to set the correct value for the sequence using setval()
select setval('context_context_id_seq', (select max(context_id) from context));
Then the next call to nextval() should return the correct value.
If the column is indeed defined as ...
answered May 22 '14 at 9:32
a_horse_with_no_name
61.7k12 gold badges116 silver badges154 bronze badges
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 ${...
24
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
... -- other columns
CONSTRAINT UQ_Configuration_ID UNIQUE (ID)
);
23
Three ways. Either IGNORE duplicate errors
(but don't use that, see note in the end):
INSERT IGNORE
... ; -- without ON DUPLICATE KEY
or try to do a redundant update when there is a duplicate:
INSERT
...
ON DUPLICATE KEY UPDATE
id = id ;
or check for duplicates before inserting:
INSERT INTO requests
(id, ctg, msg, nick, filled, ...
23
Nothing is wrong with your table definition.
(Except hat I would use jos_content_id or something instead of the non-descriptive column name id.
And I probably would use text instead of varchar(50).
Your INSERT statement is the problem.
With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value ...
answered Mar 12 '14 at 21:11
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
23
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 DEFAULT VALUES;
another alternative is to provide one column with the DEFAULT clause:
insert into people ("timestamp") values (default);
answered Aug 2 '20 at 6:16
a_horse_with_no_name
61.7k12 gold badges116 silver badges154 bronze badges
22
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, name2 FROM test_import_three WHERE id = 4)
21
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 and the predicate means the storage engine chooses row locks, since that appears to be the cheapest strategy.
The cardinality misestimation deliberately ...
18
I guess you could (ab)use MERGE for this. First create a (temporary) table:
CREATE TABLE tempIDs
( PersonId INT,
FinancialInstitutionId INT
) ;
Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause:
MERGE INTO Person
USING FinancialInstitution AS fi
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (...
17
By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY() (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to ...
17
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 session will insert into the table this will avoid unnecessary row or page locks and will prevent lock escalation. After all, if you are inserting so much data ...
17
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 transaction, there are two transactions in autocommit mode: the first creates the table and the second fills it up.
You can prove it to yourself this way:
In a ...
15
Explicit identity insert require IDENTITY_INSERT property set to ON.
SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into
-- the identity column of a table.
GO
INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
GO
SET IDENTITY_INSERT MyTable OFF -- ...
15
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 MS Docs (yay open source!):
The SELECT...INTO statement operates in two parts - the new table is created, and then rows are inserted. This means that if the ...
14
For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL.
You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-...
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
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 Analyzing individual query execution wait times.
What actions can I take to reduce the cost of the table insert?
That would depend largely on the result of ...
13
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'), NULLIF(%s, 'nan'))
ON CONFLICT (id)
DO UPDATE SET
(col1, col2, col3)
= (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
...
13
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, SERIALIZABLE)
where trd.HeaderId = @HeaderId
)
DELETE from dbo.FinanceDetail
where HeaderId = @...
12
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 the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but ...
12
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 (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in team and then, in the ...
answered Mar 15 '17 at 19:07
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
12
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 leaving out the column specification at all. However, in 14.11 the BNF looks like:
<insert statement> ::=
INSERT INTO <insertion target> <insert ...
Only top voted, non community-wiki answers of a minimum length are eligible
