Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
Всем привет!


Есть задачка.

Есть две таблички source_table, destination_table

данные из source_table (по выборке) вставляются в destination_table.

необходимо отметить в source_table.[status] = 'S' строки, которые были перенесены обработаны.


Я знаю как решить задачку с помощью двух запросов и промежуточной таблицы, но хотелось бы решить задачку одним запросом с помощью output.
2 ноя 12, 11:32    [13413099]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
update [source_table]
 set
  [status] = 'S'
output
 inserted.field1, ... inserted.fieldN into [destination_table] (field1, ... fieldN)
where
 <условия выборки>;
2 ноя 12, 11:43    [13413211]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
invm
update [source_table]
 set
  [status] = 'S'
output
 inserted.field1, ... inserted.fieldN into [destination_table] (field1, ... fieldN)
where
 <условия выборки>;


Может не совсем понял, а где сам Insert Into destination_table ? нужно сначала insert сделать, а потом update
2 ноя 12, 11:46    [13413236]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
Testor1
Может не совсем понял, а где сам Insert Into destination_table ?
update [source_table]
 set
  [status] = 'S'
output
 inserted.field1, ... inserted.fieldN into [destination_table] (field1, ... fieldN)
where
 <условия выборки>;
2 ноя 12, 11:52    [13413271]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
		INSERT INTO [LinkedServerTOServer].[test].[dbo].[destination_table](cl1, cl2, cl3, cl4, cl5, cl6)
		OUTPUT inserted.Id INTO @list_id
		SELECT cl1, cl2, cl3, cl4, cl5, cl6
		FROM  source_table  a 
                         [status] = 'Q';
					
		UPDATE source_table
			SET [status] = 'S'
		WHERE EXISTS(SELECT * FROM @list_id a WHERE id = a.id ) 	
2 ноя 12, 11:53    [13413278]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
invm
Testor1
Может не совсем понял, а где сам Insert Into destination_table ?
update [source_table]
 set
  [status] = 'S'
output
 inserted.field1, ... inserted.fieldN into [destination_table] (field1, ... fieldN)
where
 <условия выборки>;


Если я правильно понимаю тебя, ты предлагаешь вначале менять статус, и те записи которые сменили статус вставлять в destination_table
2 ноя 12, 11:54    [13413291]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Glory
Member

Откуда:
Сообщений: 104751
update a set
from source_table a
inner join( insert destination_table ... output ) b on a.someid = b.someid
2 ноя 12, 11:55    [13413297]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
Testor1
Если я правильно понимаю тебя, ты предлагаешь вначале менять статус, и те записи которые сменили статус вставлять в destination_table
Именно так.
2 ноя 12, 11:57    [13413308]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
update a set
from source_table a
inner join( insert destination_table ... output ) b on a.someid = b.someid


спасибо

а что делать в случае linked сервера. если вставка идет в удаленную таблицу

Msg 405, Level 16, State 1, Procedure stored_procedure Line 35
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

:(
2 ноя 12, 11:59    [13413318]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Glory
Member

Откуда:
Сообщений: 104751
INSERT INTO [LinkedServerTOServer].[test].[dbo].[destination_table](cl1, cl2, cl3, cl4, cl5, cl6)
SELECT ...
FROM( UPDATE source_table ... OUTPUT ...) a
2 ноя 12, 12:03    [13413354]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
INSERT INTO [LinkedServerTOServer].[test].[dbo].[destination_table](cl1, cl2, cl3, cl4, cl5, cl6)
SELECT ...
FROM( UPDATE source_table ... OUTPUT ...) a


Один только уточняющий вопрос

какова будет последовательность действий - он сначала обновит все строки, а потом будет делать вставку или он после обновления каждой строки будет делать вставку ?
2 ноя 12, 12:15    [13413465]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
Testor1,

еще один момент - таблица output не может быть частью FK констрейнта:
"output_table cannot participate on either side of a FOREIGN KEY constraint" (с) MSDN...
5 ноя 12, 17:27    [13423069]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
SirMix
Testor1,

еще один момент - таблица output не может быть частью FK констрейнта:
"output_table cannot participate on either side of a FOREIGN KEY constraint" (с) MSDN...


Спасибо за внимание и поддержку

Я после переписки начал активно использовать WIH и OUTPUT

вот такой вопрос еще возник

Хочу значения, которые были вставлены table1, чтобы они были вставлены в table2

Синтаксис корректный ?

INSERT INTO table2
SELECT col1, col2
FROM (
with query (col1, col2) as,
(
select col1, col2
from table1
)
INSERT INTO table1
OUTPUT
select col1, col2 from query) t
5 ноя 12, 18:08    [13423193]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Glory
Member

Откуда:
Сообщений: 104751
Testor1
Синтаксис корректный ?

В студии в окне запроса в меню есть специальная кнопка проверки синтаксиса
5 ноя 12, 18:11    [13423206]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
Testor1,

нет, не правильный...
Glory подсказывает - в студии можно прочекать пимпочкой "Parse" (выглядит как галочка).

правильный синтаксис следующий:

WITH query (col1, col2) AS
( select col1, col2 from dbo.table1 )

INSERT INTO
	dbo.table2
	OUTPUT INSERTED.col1, INSERTED.col2 INTO dbo.table1
	SELECT col1, col2
	FROM query t


но, честно говоря, не совсем понимаю этой логики... - выполните следующий запрос (это исходит из Вашего задания, насколко я понимаю):

DECLARE @T1 TABLE (col1 INT, col2 INT)
DECLARE @T2 TABLE (col1 INT, col2 INT)
INSERT INTO @T1
        ( col1, col2 )
VALUES (1, 1)

;WITH query (col1, col2) AS
( select col1, col2 from @T1 )

INSERT INTO
	@T2
	OUTPUT INSERTED.col1, INSERTED.col2 INTO @T1
	SELECT col1, col2
	FROM query t

SELECT * FROM @T1
SELECT * FROM @T2


или Вам не это нужно?
5 ноя 12, 18:57    [13423318]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
SirMix
Testor1,

нет, не правильный...
Glory подсказывает - в студии можно прочекать пимпочкой "Parse" (выглядит как галочка).

правильный синтаксис следующий:

WITH query (col1, col2) AS
( select col1, col2 from dbo.table1 )

INSERT INTO
	dbo.table2
	OUTPUT INSERTED.col1, INSERTED.col2 INTO dbo.table1
	SELECT col1, col2
	FROM query t


но, честно говоря, не совсем понимаю этой логики... - выполните следующий запрос (это исходит из Вашего задания, насколко я понимаю):

DECLARE @T1 TABLE (col1 INT, col2 INT)
DECLARE @T2 TABLE (col1 INT, col2 INT)
INSERT INTO @T1
        ( col1, col2 )
VALUES (1, 1)

;WITH query (col1, col2) AS
( select col1, col2 from @T1 )

INSERT INTO
	@T2
	OUTPUT INSERTED.col1, INSERTED.col2 INTO @T1
	SELECT col1, col2
	FROM query t

SELECT * FROM @T1
SELECT * FROM @T2


или Вам не это нужно?


Первый Insert это детализация транзакций (история транзакций клиента), второй Insert эта группировка этих транзакции по клиентам (кол-во транзакций на клиента)

Возможно ли два инсерта и одного селекта выполнить без использования промежуточных таблиц @t1, @t2, а использую output ?

И если да, то на сколько такой подход правильный ?
5 ноя 12, 22:23    [13423891]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Glory
Member

Откуда:
Сообщений: 104751
Testor1
Возможно ли два инсерта и одного селекта выполнить без использования промежуточных таблиц @t1, @t2, а использую output ?

И в чем проблема то ?

<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_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.
6 ноя 12, 10:16    [13424812]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
Testor1,

я просто заменил в вашем скрипте dbo.table1 на @T1 и dbo.table2 на @T2 - это не промежуточные таблицы.
верните обратно и выполните скрипт
6 ноя 12, 11:27    [13425218]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
Помогите разобраться с подзапросом.  Хочу сделать селект из набора  OUTPUT. Не получается. Почему ?


SELECT * 
FROM (

WITH query( col1, col2, row_id)
AS
(
 SELECT col1, col2, ROW_NUMBER() OVER( ORDER BY @a )
 FROM  OPENROWSET(BULK  'E:\Import\DATA_FILES\test.csv',  FORMATFILE='e:\Import\FORMAT_FILES\test.XML',  ERRORFILE='E:\Import\Log\test.log', ROWS_PER_BATCH=670972) as a 
 WHERE dbo.fnc_CLR_remove_unused_info(col2) IS NOT NULL
)
,
query1(max_row_id) as (SELECT TOP 1 row_id FROM query ORDER BY row_id DESC)
INSERT INTO tb_test  (col1,  col2) 
OUTPUT query1.row_id 
SELECT col1, col2 
FROM query, query1 

) AS fff





Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'ORDER'.

15 дек 12, 22:23    [13637434]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
Testor1
Не получается.

А так?
WITH query( col1, col2, row_id)
AS
(
 SELECT col1, col2, ROW_NUMBER() OVER( ORDER BY @a )
 FROM  OPENROWSET(BULK  'E:\Import\DATA_FILES\test.csv',  FORMATFILE='e:\Import\FORMAT_FILES\test.XML',  ERRORFILE='E:\Import\Log\test.log', ROWS_PER_BATCH=670972) as a 
 WHERE dbo.fnc_CLR_remove_unused_info(col2) IS NOT NULL
)
,
query1(max_row_id) as (SELECT TOP 1 row_id FROM query ORDER BY row_id DESC)
INSERT INTO tb_test  (col1,  col2) 
OUTPUT query1.row_id 
SELECT col1, col2 
FROM query, query1 
15 дек 12, 22:47    [13637515]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
alexeyvg
Testor1
Не получается.

А так?
WITH query( col1, col2, row_id)
AS
(
 SELECT col1, col2, ROW_NUMBER() OVER( ORDER BY @a )
 FROM  OPENROWSET(BULK  'E:\Import\DATA_FILES\test.csv',  FORMATFILE='e:\Import\FORMAT_FILES\test.XML',  ERRORFILE='E:\Import\Log\test.log', ROWS_PER_BATCH=670972) as a 
 WHERE dbo.fnc_CLR_remove_unused_info(col2) IS NOT NULL
)
,
query1(max_row_id) as (SELECT TOP 1 row_id FROM query ORDER BY row_id DESC)
INSERT INTO tb_test  (col1,  col2) 
OUTPUT query1.row_id 
SELECT col1, col2 
FROM query, query1 


Не знаю почему но в оутпут разрешает использовать только inserted

и ты не совсем понял мою идею

я хочу сохранить max_row_id в переменную.
для этого мне нужно сделать две вещи
1. сделать инсерт запроса в конечную таблицу
2. сделать селект max_row_id из output без использования временной таблицы
15 дек 12, 22:52    [13637529]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
Testor1
и ты не совсем понял мою идею

я хочу сохранить max_row_id в переменную
А, вот оно что, не заметил сразу.

Просто синтаксис использования WITH неправильный, я в общем про него сразу написал.
И вообще весь запрос странный, трудно его понять.
Допустим, поля query1.row_id вообще нету, есть поле query1.max_row_id
Кросс-джойн непонятно что означает: FROM query, query1

Testor1
Не знаю почему но в оутпут разрешает использовать только inserted
Да, так устроен OUTPUT, ничего не поделать :-(

Хотя конкретно для этих запросов max_row_id равен количеству вставленных строк, его можно получить другими способами, например, из переменной @@ROWCOUNT :-)
15 дек 12, 23:59    [13637742]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
alexeyvg
Testor1
и ты не совсем понял мою идею

я хочу сохранить max_row_id в переменную
А, вот оно что, не заметил сразу.

Просто синтаксис использования WITH неправильный, я в общем про него сразу написал.
И вообще весь запрос странный, трудно его понять.
Допустим, поля query1.row_id вообще нету, есть поле query1.max_row_id
Кросс-джойн непонятно что означает: FROM query, query1

Testor1
Не знаю почему но в оутпут разрешает использовать только inserted
Да, так устроен OUTPUT, ничего не поделать :-(

Хотя конкретно для этих запросов max_row_id равен количеству вставленных строк, его можно получить другими способами, например, из переменной @@ROWCOUNT :-)


Вся затея с бубнами в том, чтобы получить @@ROWCOUNT до фильтрации. То есть кол-во строк в исходном файле, а не столько сколько было вставлено в таблицу.

В моем скрипте несколько ошибок.

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

Склоняюсь к схеме, что мою задачу лучше реализовывать через Instead of триггер. В нем делать фильтрацию. В этом случае @@ROWCOUNT покажет ко-во записей как-бы вставленных в таблицу

 
DECLARE @a INT = 1;
DECLARE @b INT = 1;


WITH query( col1, col2, row_id)
AS
(
 SELECT col1, dbo.fnc_CLR_remove_unused_info(col2), ROW_NUMBER() OVER( ORDER BY @a )
 FROM  OPENROWSET(BULK  'E:\Import\DATA_FILES\test.csv',  FORMATFILE='e:\Import\FORMAT_FILES\test.XML',  ERRORFILE='E:\Import\Log\test.csv.log', ROWS_PER_BATCH=670972) as a 
)
,
query1(col1, col2,  max_row_id) 
AS
(
	SELECT col1, col2, MAX(row_id) OVER( )
	FROM query
)

INSERT INTO tb_test(col1, col2, max_row_id)
SELECT a.col1,col2, max_row_id
FROM query1 a 
WHERE col2 IS NOT NULL;
16 дек 12, 10:46    [13638215]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
Testor1
По этой схеме мне нужно в конечной таблице добавлять столбец чтобы записать в него кол-во строк файле. Но такая схема все равно требует создания промежуточной таблицы для SQL сервера, чтобы вычислить max_row_id.
Если в конечной таблице добавлять столбец, то тогда как раз промежуточной таблицы не нужно, вас пример ведь как раз это и делает, только OUTPUT добавить.
Testor1
Склоняюсь к схеме, что мою задачу лучше реализовывать через Instead of триггер.
Можно и так, но ИМХО проще тогда через временную таблицу, чем городить триггеры.
16 дек 12, 11:40    [13638251]     Ответить | Цитировать Сообщить модератору
 Re: использование output  [new]
Testor1
Member

Откуда:
Сообщений: 679
alexeyvg
Testor1
По этой схеме мне нужно в конечной таблице добавлять столбец чтобы записать в него кол-во строк файле. Но такая схема все равно требует создания промежуточной таблицы для SQL сервера, чтобы вычислить max_row_id.
Если в конечной таблице добавлять столбец, то тогда как раз промежуточной таблицы не нужно, вас пример ведь как раз это и делает, только OUTPUT добавить.

Если в конечной таблице делать столбец, то и OUTPUT не нужно.

Загвозка в этой части кода. Сервер сначала должен загрузить весь файл, чтобы вычислить MAX(row_id), а это значит он использует внутреннюю временную таблицу. Чудес не бывает.

query1(col1, col2,  max_row_id) 
AS
(
	SELECT col1, col2, MAX(row_id) OVER( )
	FROM query
)




Testor1
Склоняюсь к схеме, что мою задачу лучше реализовывать через Instead of триггер.
Можно и так, но ИМХО проще тогда через временную таблицу, чем городить триггеры.


Я не спорю, но сможешь реально доказать, что таблица INSERTED менее эффективна чем временная таблица ?

Вот пока что нашел на тему Inserted
Инструкции триггеров DML используют две особые таблицы: Таблицы deleted и inserted. SQL Server автоматически создает эти таблицы и управляет ими. Эти временные таблицы, находящиеся в оперативной памяти, используются для проверки результатов изменений данных и для установки условий срабатывания триггеров DML. Нельзя в этих таблицах изменять данные напрямую или выполнять над ними операции языка DDL, например инструкцию CREATE INDEX.


http://msdn.microsoft.com/ru-ru/library/ms191300(v=sql.105).aspx
16 дек 12, 12:39    [13638309]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить