"Translate" query from Mysql to SQL Server 2012

Asked

Viewed 324 times

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()
mysql sql database sql-server query

  • Sometimes the site: http://www.sqlines.com/online helps convert sometimes. the answer below @Daniel, is quite complete.

  • What is the name of the column that is the primary key of the [Schedule] table? What other columns are there in the table? How is the last_exec_date column declared?

  • What are you trying to do? an update?

  • @Marconciliosouza That’s right

  • @Josédiz The primary key is job_id

  • @David That’s right, but the REPLACE INTO not working out. I used the Microsoft SQL Server Migration Assistant for Mysql to convert all other queries, but this is not being "translated"

  • @Nottherealhemingway: If you can add more information to this topic, the answers may be more accurate. The existence of the job_id column, for example, can affect the answers. It is of the autoincrement type?

  • @Josédiz Alright! When you get home I will document the question better so that we have better answers too. I apologize!

Show 3 more comments

3 answers

6


To have action similar to construction REPLACE INTO (mysql SQL extension) mentioned in this topic, here is 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. Completion depends on the author’s pending answers to the topic.

If the last_exec_date column is of the type datetime (date and time), the Cast() should be removed, leaving only Current_timestamp.

To decide for sure how to replace REPLACE INTO in this case, it is recommended to analyze what is its effective action in the code that is migrating from Mysql to SQL Server.

  • this is the simplest and acts as replace into. There is only one detail. If you delete, all columns are deleted. At the time of intsert into, you should insert them again. But the question does not make it clear that there are other columns. For a more consistent query, merge with when Matched..

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() )

  • I think this more to the name of the job, but your answer is good.

  • 1

    @Reginaldorigo: Add the WHERE clause in the UPDATE command, setting the line to be updated.

  • You’re right. My fault.

  • in this example is different from what actually makes the replace into. This is equivalent to on duplicate

-1

The function NOW() swap for 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 SQL standard.

To understand how to translate the command correctly, you need to understand what the REPLACE INTO ago.

The REPLACE INTO deletes the entire Row if it finds a duplicate key and then does the INSERT.

For this behavior, in SQL SERVER, you must create a logic where you apply the DELETE and then the INSERT. That is, two separate queries.

To ensure consistency, run within a TRANSACTION.

ON DUPLICATE KEY (Mysql)

There are recommendations for the use of ON DUPLICATE KEY alternatively to REPLACE INTO. However, the decision is up to the developer. This command does not delete Row. When you find a duplicate key, do 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 command MERGE 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 primary key column. It is not clear in the question the structure of the table. So I want that column.



Note:
The care to use delete and Insert is that at the time of Insert should insert all existing columns. Therefore you should be aware of what you really need.

  • 2

    Excuse me, there is no REPLACE INTO in SQL-Server

  • I edited to avoid confusion, which invalidates that your comment, @Reginaldorigo

Browser other questions tagged mysql sql database sql-server query

You are not signed in. Login or sign up in order to post.