Skip to main content
3 votes

question about using Insert where not exists instead of if not exists then

If you want to go that route then you can use INSERT INTO dbo.Test (CorrelationId, TransactionId) SELECT @CorrelationId, @TransactionId WHERE NOT EXISTS (SELECT * ...
Martin Smith's user avatar
  • 84.7k
3 votes

"#1292 - Incorrect datetime value" when time is within one hour

The US treats 2024-03-10 as the date for Daylight Savings Time adjustment. At 02:00, clocks advance by one hour. So effectively there is no hour between 02:00 and 03:00 on that date. https://www....
Bill Karwin's user avatar
  • 14.8k
3 votes
Accepted

SQL Server: Inserting empty value (zero-arity INSERT)

Try with INSERT INTO tmp DEFAULT VALUES; To explicitly specify the column name,in particular cases, try with SET IDENTITY_INSERT ON (1. and don't forget do set it back to off SET IDENTITY_INSERT tmp ...
Sabin B's user avatar
  • 4,401
3 votes

SQL breaking my query where there is NULL value

Error was thrown because of trying to insert NULL values in the COUNTRY column, not COUNTRY_CODE. You did not specify any value for COUNTRY column, an INSERT like that would insert NULL values in ...
Balazs Papp's user avatar
  • 40.7k
2 votes
Accepted

Lazily Evaluate Insert Arguments If Constraint Violated

If you put the check in the same query, the expensive function call is only executed if there is no conflicting row, yet: INSERT INTO a (b, c) SELECT 'd', pg_sleep(1) WHERE NOT EXISTS (SELECT FROM a ...
Erwin Brandstetter's user avatar
2 votes

How to handle deadlocks while multiple batch inserts?

The best solution would be to make sure that no two of the concurrent statements try to insert rows with the same primary or unique key. And no, ON CONFLICT doesn't do that trick. The second best ...
Laurenz Albe's user avatar
  • 51.9k
2 votes
Accepted

mysql bulk insert with constraints

INSERT INTO Leaderboard (matchID, score, country, tournamentID) WITH RECURSIVE cte1 (num) AS ( SELECT 1 UNION ALL SELECT num + 1 FROM cte1 LIMIT 100 ), cte2 (country) AS (SELECT "SPAIN" ...
Akina's user avatar
  • 19.9k
2 votes
Accepted

(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence

Fortunately, PostgreSQL error messages are quite helpful. Following the hint shown by the PostgreSQl engine, I added the instruction "OVERRIDING SYSTEM VALUE" and it worked flawlessly: ...
ValerioOliveira's user avatar
2 votes
Accepted

PostgreSQL: Insert dynamic columns and values from jsonb argument

You concatenate values as (untyped) string literals into the query string, so you don't need to add an explicit cast. Postgres will coerce each value with an assignment cast to the data type of the ...
Erwin Brandstetter's user avatar
1 vote

SQL breaking my query where there is NULL value

Since all three columns of country are defined as NOT NULL, you have to provide a value for all three columns when you insert a new row. To load that table using distinct values from airportdemodata, ...
Paul W's user avatar
  • 633
1 vote

Replace "INSERT" by "UPDATE"

Take a database snapshot before you start. If you mess up restore from that snapshot, fix your script and repeat. Restore from snapshot will be faster than taking a full database backup and restoring ...
Michael Green's user avatar
1 vote
Accepted

Replace "INSERT" by "UPDATE"

You can't just find and replace INSERT with UPDATE and have it work, the command syntaxes are completely different. If you want to get back to the EXACT state you were in before you started this, ...
Jonathan Fite's user avatar
1 vote
Accepted

Moving records between tables in CockroachDB is taking too long

In CockroachDB, doing an INSERT INTO <table> SELECT * FROM <other_table>; operation is generally a bad idea. I would suggest using CREATE TABLE AS syntax (https://www.cockroachlabs.com/...
jhatcher9999's user avatar
1 vote

Auto-increment [id] field in a table in an SQL-Server database

Typically the best way to get this done if you have to preserve the values in the id column and not manually insert the id is - CREATE TABLE dbo.Settings_x ( GroupId INT --or ...
Caleb Carl's user avatar
1 vote
Accepted

In PostgreSQL, what is the `index_predicate` for in `INSERT`?

Yes, you got it right. What the documentation is trying to make clear is that if you write ON CONFLICT (department_id) WHERE active = TRUE, PostgreSQL can also infer a unique index without a WHERE ...
Laurenz Albe's user avatar
  • 51.9k
1 vote

PostgreSQL: Insert dynamic columns and values from jsonb argument

See the updated insert function in Erwin's answer. I decided to merge the insert with the update function from the related question: Update dynamic column names using keys from jsonb argument. This ...
Russell Turner's user avatar
1 vote
Accepted

insert trigger to auto-set key fields for composite primary key

Are you certain that you can have only one update per trigger? I didn't find any reference to this limit in the sqlite docs. Anyway, you can just combine both updates in a single one: CREATE TRIGGER ...
Andrea B.'s user avatar
  • 1,558
1 vote
Accepted

SQL Server stored proc locking, serialization

Are you saying you're concerned with reading the balance twice because it can change between the reads? yes that is my concern. For the use case you presented, you can actually get away with just ...
J.D.'s user avatar
  • 37.5k
1 vote
Accepted

Compositing ordered SQL tables without Joins

My first choice would be your very first option (option 0?). It's very clear and obvious what you are doing and how. If there are problems with creating an index on your master data's names you can a)...
LoztInSpace's user avatar

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