64
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 SQL-Fiddle
Another way, with shorter syntax if you have a lot of values to insert:
...
51
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 ...
35
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
102k10 gold badges214 silver badges339 bronze badges
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 (...
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)
);
22
Three ways. Either IGNORE duplicate errors:
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, dated, filldate)
SELECT
NULL, '...
22
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 ...
21
As the answers on the other question (of which this one is considered a duplicate) mention, there is (since version 9.5) a native UPSERT functionality. For older versions, keep reading :)
I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the ...
21
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
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
As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert.
This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted).
If you find ...
18
The reason is very simple. When you insert a row into MyISAM, it just puts it into the server's memory and hopes that the server will flush it to disk at some point in the future. Good luck if the server crashes.
When you insert a row into InnoDB it syncs the transaction durably to disk, and that requires it to wait for the disk to spin. Do the math on ...
18
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
102k10 gold badges214 silver badges339 bronze badges
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
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 ...
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 ...
16
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 ...
16
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
45.8k9 gold badges90 silver badges122 bronze badges
15
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
102k10 gold badges214 silver badges339 bronze badges
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 ...
13
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-...
13
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 ...
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
It the optimiser decides it would be more efficient to sort the data prior to insert, it will do so somewhere upstream of the insert operator. If you introduce a sort as part of your query, the optimiser should realise that the data is already sorted and omit doing so again. Note the execution plan chosen may vary from run to run depending on the number of ...
12
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 ...
12
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 ...
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
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 -- ...
11
Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:
create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );
declare
r v_tq84_virtual_test_with%rowtype;
begin
select * into r from v_tq84_virtual_test_with where col_2 = 8;
r.col_4 := r.col_4 - 2;
...
Only top voted, non community-wiki answers of a minimum length are eligible
