SQL Server support #97
Description
Hi,
During the past ~ hour I have been investigating whether it was possible to get consistent support for merge queries in the SQL server dialect. For achieving this I tried adding my own dialect, and working out the details, but I don't seem to find out how to do it.
Here is an attempted commit:
What is still wrong with this:
An example output of what I would like would be something like this:
MERGE [dbo].[addresses]
WITH (SERIALIZABLE, TABLOCKX) AS t
USING (
SELECT
[importaddresses].code,
[importaddresses].client_id,
[importaddresses].address_line_one,
[importaddresses].address_line_two,
[importaddresses].address_line_three,
[importaddresses].address_line_four,
[importaddresses].address_postal_code,
[importaddresses].address_city,
[importaddresses].[address_country_code],
[importaddresses].phone_number,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
FROM
[dbo].[importaddresses] importaddresses
ORDER BY
[importaddresses].code ASC,
[importaddresses].client_id ASC,
[importaddresses].address_line_one ASC,
[importaddresses].address_line_two ASC,
[importaddresses].address_line_three ASC,
[importaddresses].address_line_four ASC,
[importaddresses].address_postal_code ASC,
[importaddresses].address_city ASC,
[importaddresses].[address_country_code] ASC,
[importaddresses].phone_number ASC,
CURRENT_TIMESTAMP ASC OFFSET 1750000 ROWS FETCH NEXT 250000 ROWS ONLY
) AS s (
code,
client_id,
address_line_one,
address_line_two,
address_line_three,
address_line_four,
address_postal_code,
address_city,
address_country,
phone_number,
created_at,
updated_at
) ON (
t.code = s.code
)
WHEN MATCHED THEN
UPDATE
SET
t.address_postal_code = s.address_postal_code,
t.address_line_two = s.address_line_two,
t.address_country = s.address_country,
t.address_line_one = s.address_line_one,
t.client_id = s.client_id,
t.address_city = s.address_city,
t.address_line_three = s.address_line_three,
t.updated_at = s.updated_at,
t.phone_number = s.phone_number,
t.address_line_four = s.address_line_four,
t.archived = NULL
WHEN NOT MATCHED THEN
INSERT
(
code,
client_id,
address_line_one,
address_line_two,
address_line_three,
address_line_four,
address_postal_code,
address_city,
address_country,
phone_number,
created_at,
updated_at,
archived
)
VALUES
(
s.code,
s.client_id,
s.address_line_one,
s.address_line_two,
s.address_line_three,
s.address_line_four,
s.address_postal_code,
s.address_city,
s.address_country,
s.phone_number,
s.created_at,
s.updated_at,
NULL
);
However, we must keep in mind that this should not screw over the regular CASE WHEN statements, in which the difficulty lies.
Suspected issues that I was unable to resolve:
WHEN( NOT)?MATCHED( .*?) THENshould be both aopenParensand acloseParens; the first time an open parens and the second time both at the same time? Note: it can be alsoWHEN MATCHED BY id THEN, hence the( .*?)WHENshould be preceded by a newline as well
Two (ugly) queries to test easily:
MERGE [dbo].[addresses]
WITH (SERIALIZABLE, AS t
USING (
SELECT
[importaddresses].code, [importaddresses].client_id, [importaddresses].address_line_one, [importaddresses].address_line_two, [importaddresses].address_line_three, [importaddresses].address_line_four, [importaddresses].address_postal_code, [importaddresses].address_city, [importaddresses].[address_country_code], [importaddresses].phone_number, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM
[dbo].[importaddresses] importaddresses
ORDER BY
[importaddresses].code ASC, [importaddresses].client_id ASC, [importaddresses].address_line_one ASC, [importaddresses].address_line_two ASC, [importaddresses].address_line_three ASC, [importaddresses].address_line_four ASC, [importaddresses].address_postal_code ASC, [importaddresses].address_city ASC, [importaddresses].[address_country_code] ASC, [importaddresses].phone_number ASC, CURRENT_TIMESTAMP ASC OFFSET 1750000 ROWS FETCH NEXT 250000 ROWS ONLY
) AS s (
code, client_id, address_line_one, address_line_two, address_line_three, address_line_four, address_postal_code, address_city, address_country, phone_number, created_at, updated_at
) ON (t.code = s.code) WHEN MATCHED THEN
UPDATE
SET
t.address_postal_code = s.address_postal_code, t.address_line_two = s.address_line_two, t.address_country = s.address_country, t.address_line_one = s.address_line_one, t.client_id = s.client_id, t.address_city = s.address_city, t.address_line_three = s.address_line_three, t.updated_at = s.updated_at, t.phone_number = s.phone_number, t.address_line_four = s.address_line_four, t.archived = NULL
WHEN NOT MATCHED THEN
INSERT
(
code, client_id, address_line_one, address_line_two, address_line_three, address_line_four, address_postal_code, address_city, address_country, phone_number, created_at, updated_at, archived
)
VALUES
(
s.code, s.client_id, s.address_line_one, s.address_line_two, s.address_line_three, s.address_line_four, s.address_postal_code, s.address_city, s.address_country, s.phone_number, s.created_at, s.updated_at, NULL
);
SELECT ProductNumber,
Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END
, Name
FROM Production.Product
ORDER BY ProductNumber;