What is the SQL Server 2012 query that corresponds to the next MySQL query?
REPLACE INTO schedule SET jobname = "sqldump" , last_exec_date = NOW()
What is the SQL Server 2012 query that corresponds to the next MySQL query?
REPLACE INTO schedule SET jobname = "sqldump" , last_exec_date = NOW()
To take action similar to the above REPLACE INTO build (MySQL SQL extension) in this topic, here's the code outline:
-- código #1
BEGIN TRANSACTION;
DELETE from [schedule]
where jobname = 'sqldump';
INSERT into [schedule] (jobname, last_exec_date)
values ('sqldump', Cast(Current_timestamp as date));
COMMIT;
The above code assumes that the jobname column is the primary key of the [schedule] table. Finishing depends on the outstanding responses of the topic author.
If the last_exec_date column is of type datetime , you must remove the Cast (), with Current_timestamp remaining.
To decide with certainty how to replace REPLACE INTO in this case, it is recommended to analyze what is the actual action of it in the code that is migrating from MySQL to SQL Server.
Assuming jobname is the key of the schedule table
IF EXISTS (SELECT * FROM [schedule ] WHERE jobname = 'sqldump')
UPDATE [schedule ] SET last_exec_date = GETDATE() WHERE jobname = 'sqldump'
ELSE
INSERT INTO [schedule ] VALUES( 'sqldump', GETDATE() )
The function function NOW()
change by GETDATE()
In the question code, the syntax is wrong:
REPLACE INTO schedule SET jobname = "sqldump" , last_exec_date = NOW()
In MySQL the correct syntax should be
REPLACE INTO schedule (jobname, last_exec_date) VALUES ('sqldump', NOW())
Note that REPLACE INTO
is a MySQL syntax, which is not part of standard SQL.
To understand how to translate the command correctly, you need to understand what REPLACE INTO
does.
The REPLACE INTO
deletes the entire row if it finds a duplicate key and then does INSERT
.
For this behavior, in SQL SERVER, you must create a logic where you apply DELETE
and then INSERT
. That is, two separate queries.
To ensure consistency, run within TRANSACTION
.
There are recommendations for using ON DUPLICATE KEY
alternatively REPLACE INTO
. However, the decision is for the developer.
This command does not delete the row. When it encounters a duplicate key, it does an UPDATE.
Unfortunately SQL SERVER does not provide this type of resource.
For cases where you do not want to delete and insert is recommended.
In SQL Server you can combine the MERGE
command with WHEN MATCHED
.
MERGE schedule AS T
using (VALUES ('sqldump', GETDATE()))
AS S (jobname, last_exec_date)
ON T.jobname = 'sqldump'
WHEN MATCHED THEN
UPDATE
set last_exec_date = S.last_exec_date
WHEN NOT MATCHED THEN
INSERT (jobname, last_exec_date)
VALUES (7, S.jobname, S.last_exec_date)
In this example I used jobname
as the primary key column. It is not clear in the question the structure of the table. So I assume it's this column.
Note:
The care in using delete and insert is that at the moment of insert you must insert all the existing columns. So you should be aware of what you really need.