Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Доброго пятничного дня коллеги!!

Прошу очередной помощи=)

Суть в следующем - получаю новый набор данных с измененными атрибутами или новыми строками (справочник) по средством BULK INSERT во временную таблицу, затем в цикле сверял со своим справочником и выполнял UPDATE (+ писал в таблицу истории, какой атрибут был изменен) или INSERT. Всю эту бодягу крутил в цикле, пока не свершилось чудо - открыл для себя MARGE. С одной стороны все отлично, быстро и компактный листинг. Но вопрос остался открытым - как заносить данные в таблицу истории если они были изменены, триггер вешать нет желания (не та ситуация). Смотрел в сторону процедуры - засунуть мой UPDATE справочника и INSERT в таблицу истории, а процедуру выполнять в предложении ...WHEN MATCHED THEN..., но как то не красиво получается =\
Собственно сам вопрос: возможно ли это реализовать внутри инструкции MARGE

BEGIN TRAN	
		MERGE DBO.CLIENT AS TARGET
		USING	(SELECT SERIALKEY, FIO, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3 FROM #NEW_DATA)  AS SOURCE
				ON TARGET.SERIALKEY = SOURCE.SERIALKEY
		WHEN MATCHED THEN   -- ВОТ СЮДА ХОТЕЛ ХП ЗАСУНУТЬ 
				UPDATE SET	TARGET.ATTRIBUTE1 = SOURCE.ATTRIBUTE1,
							TARGET.ATTRIBUTE2 = SOURCE.ATTRIBUTE2,
							TARGET.ATTRIBUTE3 = SOURCE.ATTRIBUTE3
		WHEN MATCHED THEN
		WHEN NOT MATCHED BY TARGET THEN
				INSERT (SERIALKEY, FIO, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3)
				VALUES (SOURCE.SERIALKEY, SOURCE.FIO, SOURCE.ATTRIBUTE1, SOURCE.ATTRIBUTE2, SOURCE.ATTRIBUTE3)
		OUTPUT $action, Deleted.*,Inserted.*; 
		
DROP TABLE #NEW_DATA
ROLLBACK TRAN;


Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
18 дек 15, 12:40    [18577566]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
Но вопрос остался открытым - как заносить данные в таблицу истории если они были изменены, триггер вешать нет желания (не та ситуация).

Ну так OUTPUT

<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT <dml_select_list> ]
}

output_table
Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

-Have enabled triggers defined on it.
-Participate on either side of a FOREIGN KEY constraint.
-Have CHECK constraints or enabled rules.
18 дек 15, 12:45    [18577597]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
правильный проходящий.
Guest
СТУДЕНТ123
получаю ... по средством BULK
Получать можно посредств_о_м (от слова посредник). По средств_а_м можно, например, жить - жить по имеющимся средствам.

ps: не выдержал ибо это далеко не первый раз.
18 дек 15, 12:46    [18577606]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
правильный проходящий.
СТУДЕНТ123
получаю ... по средством BULK
Получать можно посредств_о_м (от слова посредник). По средств_а_м можно, например, жить - жить по имеющимся средствам.

ps: не выдержал ибо это далеко не первый раз.


Согласен с Вами, но я исправляюсь!
Уже не так сердито, по отношению к предыдущим вариантам =)
18 дек 15, 12:54    [18577671]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Glory,

Спасибо огромное, что-то я "Смотрю в книгу и вижу фигу".
18 дек 15, 12:57    [18577689]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
iap
Member

Откуда: Москва
Сообщений: 47045
можно ещё и так:
INSERT LogTable(...)
SELECT ...
FROM
(
 MERGE ...
 ..........
 OUTPUT ......
) M(.....)
WHERE ....;
То есть, результат MERGE есть возможность вставить в таблицу с дополнительным фильтром во внешнем WHERE
18 дек 15, 13:04    [18577740]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
правильный проходящий.
СТУДЕНТ123
получаю ... по средством BULK
Получать можно посредств_о_м (от слова посредник). По средств_а_м можно, например, жить - жить по имеющимся средствам.

ps: не выдержал ибо это далеко не первый раз.


То есть mArge вместо mErge вас не смутил?:)
18 дек 15, 13:34    [18578012]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
правильный проходящий.
СТУДЕНТ123
получаю ... по средством BULK
Получать можно посредств_о_м (от слова посредник). По средств_а_м можно, например, жить - жить по имеющимся средствам.

ps: не выдержал ибо это далеко не первый раз.


Жестко поддерживаю, сам не сдержан бываю.

Еще надо отметить по поводу ударения "По срЕдств_а_м"
18 дек 15, 13:49    [18578138]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
WarAnt
правильный проходящий.
пропущено...
Получать можно посредств_о_м (от слова посредник). По средств_а_м можно, например, жить - жить по имеющимся средствам.

ps: не выдержал ибо это далеко не первый раз.


То есть mArge вместо mErge вас не смутил?:)

Я думаю, что "маржа" очень в тему с "по средствам"
Покупаю по 200, продаю по 400. Вот на эту маржу и живу.
18 дек 15, 13:53    [18578166]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Glory
WarAnt
пропущено...


То есть mArge вместо mErge вас не смутил?:)

Я думаю, что "маржа" очень в тему с "по средствам"
Покупаю по 200, продаю по 400. Вот на эту маржу и живу.


+1
18 дек 15, 14:00    [18578223]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Флуд =) ПЯТНИЦО ЖЕ


Итак, вроде освоил MERGE, листинг предыдущего варианта сократил в разы, но мне кажется, что можно еще упростить, но не догоняю как =(. Суть вопроса изложу по максимуму.

Есть три таблицы (все в рамках примера, по факту атрибутов около 10 - 12 на каждый справочник, но вопрос не в этом)

таблица 1 - CLIENT, содержит всех клиентов
таблица 2 - ATTRIBUTE, содержит перечень всех атрибутов (для каждого справочника своя таблица с атрибутами)
таблица 3 - ATTRIBUTE_HISTORY_CLIENT, история изменений атрибутов (участвует в запросах крайне редко, но все же есть)

CREATE TABLE CLIENT(SERIALKEY INT NOT NULL, 
                    FIO VARCHAR(36) NOT NULL, 
                    ATTRIBUTE1 VARCHAR(12) NOT NULL, 
                    ATTRIBUTE2 VARCHAR(12) NOT NULL,
                    ATTRIBUTE3 VARCHAR(12) NOT NULL	)
GO
CREATE TABLE ATTRIBUTE (SERIALKEY INT IDENTITY(1,1) NOT NULL,
                        ATTRIBUTE_NAME VARCHAR(36) NOT NULL,
                        ATTRIBUTE_VALUE VARCHAR(36) NOT NULL)
GO
CREATE TABLE ATTRIBUTE_HISTORY_CLIENT (SERIALKEY  INT IDENTITY(1,1)	NOT NULL,
                                       SERIALKEY_CLIENT INT  NOT NULL,
                                       SERIALKEY_ATTRIBUTE INT NOT NULL,
                                       DATE_ATTRIBUTE DATE NOT NULL)
GO



Данные для примера

-- АТРИБУТЫ КЛИЕНТОВ
INSERT INTO ATTRIBUTE (ATTRIBUTE_NAME, ATTRIBUTE_VALUE)
        SELECT 'ATTRIBUTE1','ХОРОШИЙ'
 UNION  SELECT 'ATTRIBUTE1','ПЛОХОЙ'			
 UNION  SELECT 'ATTRIBUTE2','ТОЛСТЫЙ'
 UNION  SELECT 'ATTRIBUTE2','ХУДОЙ'
 UNION  SELECT 'ATTRIBUTE3','МУЖИК'
 UNION  SELECT 'ATTRIBUTE3','НЕ МУЖИК'
			

-- КЛИЕНТЫ
INSERT INTO CLIENT
        SELECT 1 AS SERIALKEY, 'ПЕТРОВ В.В.' AS FIO, 'ХОРОШИЙ' AS ATTRIBUTE1, 'ТОЛСТЫЙ' AS ATTRIBUTE2, 'МУЖИК' AS ATTRIBUTE3
 UNION  SELECT 2 AS SERIALKEY, 'ИВАНОВ Г.Г.' AS FIO, 'ПЛОХОЙ'  AS ATTRIBUTE1, 'ТОЛСТЫЙ' AS ATTRIBUTE2, 'МУЖИК' AS ATTRIBUTE3
 


Предположим, загрузили очередной файл CSV, на основании этих данных необходимо изменить атрибуты в справочнике, при этом новые значения записать в таблицу истории

DECLARE @MARGE_DATA TABLE (ACTIONS VARCHAR(12), SERIALKEY INT, ATTRIBUTE1 VARCHAR(36), ATTRIBUTE2 VARCHAR(36), ATTRIBUTE3 VARCHAR(36))

SELECT X.SERIALKEY, X.FIO, X.ATTRIBUTE1, X.ATTRIBUTE2, X.ATTRIBUTE3  INTO #NEW_DATA
FROM	(
        SELECT 1 AS SERIALKEY, 'ПЕТРОВ В.В.' AS FIO, 'ХОРОШИЙ'  AS ATTRIBUTE1, 'ХУДОЙ'  AS ATTRIBUTE2, 'НЕ МУЖИК' AS ATTRIBUTE3
 UNION  SELECT 2 AS SERIALKEY, 'ИВАНОВ Г.Г.' AS FIO, 'ХОРОШИЙ'  AS ATTRIBUTE1, 'ХУДОЙ'  AS ATTRIBUTE2, 'НЕ МУЖИК' AS ATTRIBUTE3	
 UNION  SELECT 3 AS SERIALKEY, 'ЛАПИН Р.Р.'  AS FIO, 'ХОРОШИЙ'  AS ATTRIBUTE1, 'ТОЛСТЫЙ' AS ATTRIBUTE2,'МУЖИК'	  AS ATTRIBUTE3	)X

BEGIN TRAN
INSERT INTO @MARGE_DATA

SELECT  ACTIONS,SERIALKEY, ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3
FROM (	
       MERGE DBO.CLIENT AS TARGET
       USING  (SELECT SERIALKEY, FIO, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3 FROM #NEW_DATA)  AS SOURCE
               ON TARGET.SERIALKEY = SOURCE.SERIALKEY
       WHEN MATCHED THEN 
               UPDATE SET   TARGET.ATTRIBUTE1 = SOURCE.ATTRIBUTE1,
                            TARGET.ATTRIBUTE2 = SOURCE.ATTRIBUTE2,
                            TARGET.ATTRIBUTE3 = SOURCE.ATTRIBUTE3
       WHEN NOT MATCHED BY TARGET THEN
               INSERT (SERIALKEY, FIO, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3)
               VALUES (SOURCE.SERIALKEY, SOURCE.FIO, SOURCE.ATTRIBUTE1, SOURCE.ATTRIBUTE2, SOURCE.ATTRIBUTE3)
       OUTPUT $action,  Inserted.SERIALKEY,
             (CASE WHEN Deleted.ATTRIBUTE1 != Inserted.ATTRIBUTE1 THEN Inserted.ATTRIBUTE1 ELSE NULL END) AS ATTRIBUTE1,
             (CASE WHEN Deleted.ATTRIBUTE2 != Inserted.ATTRIBUTE2 THEN Inserted.ATTRIBUTE2 ELSE NULL END) AS ATTRIBUTE2,
             (CASE WHEN Deleted.ATTRIBUTE3 != Inserted.ATTRIBUTE3 THEN Inserted.ATTRIBUTE3 ELSE NULL END) AS ATTRIBUTE3 
      ) 
      m (ACTIONS, SERIALKEY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3)
      WHERE	 ATTRIBUTE1 IS NOT NULL OR ATTRIBUTE2 IS NOT NULL OR ATTRIBUTE3 IS NOT NULL;

-- ВСЕ ИМЕЮЩИЕСЯ ИЗМЕНЕНИЯ К ОБЩЕМУ ВИДУ DBO.ATTRIBUTE_HISTORY_CLIENT
INSERT INTO DBO.ATTRIBUTE_HISTORY_CLIENT (SERIALKEY_CLIENT,SERIALKEY_ATTRIBUTE,DATE_ATTRIBUTE)
SELECT X.SERIALKEY_US, X.SERIALKEY_AT, GETDATE()
FROM (
      SELECT  MD.SERIALKEY AS SERIALKEY_US, AT.SERIALKEY AS SERIALKEY_AT 
      FROM    @MARGE_DATA AS MD	INNER JOIN DBO.ATTRIBUTE AS AT ON MD.ATTRIBUTE1 = AT.ATTRIBUTE_VALUE AND AT.ATTRIBUTE_NAME = 'ATTRIBUTE1'
      UNION
      SELECT  MD.SERIALKEY AS SERIALKEY_US, AT.SERIALKEY AS SERIALKEY_AT 
      FROM    @MARGE_DATA AS MD	INNER JOIN DBO.ATTRIBUTE AS AT ON MD.ATTRIBUTE2 = AT.ATTRIBUTE_VALUE AND AT.ATTRIBUTE_NAME = 'ATTRIBUTE2'
      UNION
      SELECT  MD.SERIALKEY AS SERIALKEY_US, AT.SERIALKEY AS SERIALKEY_AT 
      FROM    @MARGE_DATA AS MD	INNER JOIN DBO.ATTRIBUTE AS AT ON MD.ATTRIBUTE3 = AT.ATTRIBUTE_VALUE AND AT.ATTRIBUTE_NAME = 'ATTRIBUTE3' )X

ROLLBACK TRAN;
DROP TABLE #NEW_DATA


Все отлично, но настораживает SELECT ... UNION ..... SELECT, в живую атрибутов то не мало, данных грузится примерно 1к строк за раз по каждому справочнику =\
18 дек 15, 15:57    [18579086]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
Все отлично, но настораживает SELECT ... UNION ..... SELECT, в живую атрибутов то не мало, данных грузится примерно 1к строк за раз по каждому справочнику =\

Вас не настораживает, что в таблице у вас аттрибуты расположены горизонтально, а истории изменения - вертикально ?
18 дек 15, 15:59    [18579098]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Glory
СТУДЕНТ123
Все отлично, но настораживает SELECT ... UNION ..... SELECT, в живую атрибутов то не мало, данных грузится примерно 1к строк за раз по каждому справочнику =\

Вас не настораживает, что в таблице у вас аттрибуты расположены горизонтально, а истории изменения - вертикально ?


Glory, настораживает, даже очень как настораживает, но наставники говорят - что все нормуль, с этим я крайне не согласен, плевать все равно увольняюсь =)
Это таблица истории, и необходимо хранить дату изменения каждого атрибута (их достаточно много). Если хранить данные как положено будет достаточно много дублей - именно так аргументируют наставники и блокировок во все не бояться т.к. к ней редко будут обращаться, но зная потребности бизнеса, разумеется это не так.

Для меня данный пример больше как учебный, а как с ним потом будут работать, сами решат!
18 дек 15, 16:14    [18579192]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
Glory, настораживает, даже очень как настораживает, но наставники говорят - что все нормуль, с этим я крайне не согласен, плевать все равно увольняюсь =)

Тогда почему вас удвиляют UNION-ы, если все "нормуль" ?
18 дек 15, 16:20    [18579242]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Потому что я студент =), и в MSSQL тоже, мало ли в будущем возникнут подобные ситуации (не в плане архитектуры таблиц), когда нужно аргументированно заменить кукуеву тучу UNION на что то более разумное, ведь время выполнения запроса - нервы пользователя!
18 дек 15, 16:26    [18579280]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
Потому что я студент =), и в MSSQL тоже, мало ли в будущем возникнут подобные ситуации (не в плане архитектуры таблиц), когда нужно аргументированно заменить кукуеву тучу UNION на что то более разумное, ведь время выполнения запроса - нервы пользователя!

Ваша проблема как раз "в плане архитектуры таблиц"
18 дек 15, 16:27    [18579290]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Glory
СТУДЕНТ123
Потому что я студент =), и в MSSQL тоже, мало ли в будущем возникнут подобные ситуации (не в плане архитектуры таблиц), когда нужно аргументированно заменить кукуеву тучу UNION на что то более разумное, ведь время выполнения запроса - нервы пользователя!

Ваша проблема как раз "в плане архитектуры таблиц"


Ну ладно, спорить не буду, не дорос мозгами и это факт! архитектура выше приведенных таблиц, настоятельная рекомендация наставника, спорить уже не хочу, да и слушать меня не будут, все равно сделают как считают нужным =) (это не нытье, мне как пингвину с горки, тем более скоро новое вакантное место), меня интересует только как можно заменить много кратный UNION на более простой запрос для сервера. В полне возможно что с подобной задачей столкнусь. В голову приходит только UNPIVOT из нормальной формы (то что вернул MERGE) в не нормальную
18 дек 15, 16:34    [18579364]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
меня интересует только как можно заменить много кратный UNION на более простой запрос для сервера.

Простой - это там, где меньше буковок в тексте ?
18 дек 15, 16:37    [18579378]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
СТУДЕНТ123
Guest
Glory
СТУДЕНТ123
меня интересует только как можно заменить много кратный UNION на более простой запрос для сервера.

Простой - это там, где меньше буковок в тексте ?
Нет, это там где стоимость меньше, я понимаю что, без конкретного плана запроса толка не будет, да и сравнивать не счет, данных то нет еще =). Наверное тему можно закрыть, глупый вопрос с моей стороны был. Glory спасибо большое!!!
18 дек 15, 16:49    [18579465]     Ответить | Цитировать Сообщить модератору
 Re: MARGE 2 инструкции в одном предложении  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
СТУДЕНТ123,

ну, так не делайте union, напишите
SELECT  MD.SERIALKEY AS SERIALKEY_US, AT.SERIALKEY AS SERIALKEY_AT 
      FROM    @MARGE_DATA AS MD	INNER JOIN DBO.ATTRIBUTE AS AT ON MD.ATTRIBUTE3 = AT.ATTRIBUTE_VALUE AND AT.ATTRIBUTE_NAME in  (select ATTRIBUTE_NAME from ATTRIBUTE)
18 дек 15, 17:57    [18579771]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить