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 ...
37
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 ...
28
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)
28
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
66.5k12 gold badges128 silver badges166 bronze badges
27
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
140k17 gold badges333 silver badges476 bronze badges
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
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 ...
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
Nothing is wrong with your table definition.
(Except that 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
140k17 gold badges333 silver badges476 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
66.5k12 gold badges128 silver badges166 bronze badges
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 ...
20
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 ...
18
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 ...
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
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
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 ...
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 can put static values into SELECT clause.
INSERT INTO new_tbl (column1, column2, column3)
SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;
15
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) ;
...
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
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 ...
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
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 your previous question:
SELECT in trigger function in two tables
However, you are not passing integer numbers, but the text representation of integer arrays: an ...
answered Jul 15 '15 at 17:00
Erwin Brandstetter
140k17 gold badges333 silver badges476 bronze badges
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
140k17 gold badges333 silver badges476 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 ...
11
The large offsets can have this effect. I would try to remove the offset and use only LIMIT 10000:
INSERT INTO domains.dictionary_language
(text_id, english)
SELECT t.old_id,
IF(t.old_text LIKE '%==English==%', 1, 0)
FROM wikt.text AS t
JOIN ( SELECT COALESCE(MAX(text_id), 0) AS offset
FROM ...
11
Looking at your problem from an SSIS perspective I feel the reason this may have taken so long is that you didn't have batching on. This can lead to too many rows filling the SSIS pipeline and can hinder your SSIS performance as a result. What you need to do is alter your rows per batch setting and possibly your maximum insert commit size. Now what you set ...
Only top voted, non community-wiki answers of a minimum length are eligible