"Translate" MySQL query for SQL Server 2012

6

What is the SQL Server 2012 query that corresponds to the next MySQL query?

REPLACE INTO schedule SET jobname = "sqldump" , last_exec_date = NOW()
    
asked by Not The Real Hemingway 29.12.2016 в 05:51

3 answers

6

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.

    
29.12.2016 / 12:23
3

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() ) 
    
29.12.2016 в 12:11
-1

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 .

ON DUPLICATE KEY (MySQL)

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.

    
29.12.2016 в 06:59