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

Откуда:
Сообщений: 34
Первоначально используется AdventureWorks2012.
Сначала создается таблица:
CREATE TABLE [Orders](
	[ID] int IDENTITY (1,1) NOT NULL,
	[BusinessEntityID] INT NOT NULL,
	[FirstName] nvarchar(255) NOT NULL,
	[LastName] nvarchar(255) NOT NULL,
	[JobTitle] nvarchar(255) NOT NULL,
	[Bonus] float DEFAULT(0.00) NOT NULL,
	[OrdersCount] int NOT NULL,
	[Freight] float NOT NULL,
	[TotalDue] float NOT NULL
);

--- заполнение данными таблицы
WITH [TempTable]
AS
(
SELECT [SalesPersonID],
	   SUM([Bonus]) as [Bonus],
	   COUNT([CustomerID]) as [OrdersCount], 	
	   SUM([Freight]) as [Freight],
	   SUM([TotalDue]) as [TotalDue]
FROM [Sales].[SalesOrderHeader]
INNER JOIN [Sales].[SalesPerson] ON [Sales].[SalesPerson].[BusinessEntityID] = [Sales].[SalesOrderHeader].[SalesPersonID]
WHERE [SalesPersonID] is not NULL
GROUP BY [SalesPersonID]
)

INSERT INTO [dbo].[Orders]
SELECT [Person].[Person].[BusinessEntityID],
	   [FirstName],
	   [LastName],
	   [JobTitle],
	   [Bonus],
	   [OrdersCount],
	   [Freight],
	   [TotalDue]
FROM [Person].[Person]
INNER JOIN [HumanResources].[Employee] ON [HumanResources].[Employee].[BusinessEntityID] = [Person].[Person].[BusinessEntityID]
INNER JOIN [TempTable] ON [TempTable].[SalesPersonID] = [Person].[Person].[BusinessEntityID]

SELECT * FROM  [Orders]
ORDER BY [BusinessEntityID]

ALTER TABLE [dbo].[Orders]
ADD [WithoutFreight] AS [TotalDue]-[Freight]

ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]


Затем формирую вьюшку, копирую туда 2 записи:

CREATE VIEW [dbo].[vw_Orders]
AS
	SELECT * FROM [dbo].[Orders] 
	WHERE [dbo].[Orders].[ID] = 1 OR [dbo].[Orders].[ID] = 2
GO

UPDATE [dbo].[vw_Orders]
SET OrdersCount = 121
WHERE [BusinessEntityID] = 281


При этом не получается сделать прямую вставку данных во вьюшке (такой записи. которой нет в изначальной таблице):
INSERT INTO [dbo].[vw_Orders] ([BusinessEntityID], [FirstName], [LastName], [JobTitle], [Bonus], [OrdersCount], [Freight], [TotalDue]) VALUES
(999, 'Vasilyi', 'Pupkin', 'European Sales Manager', 0, 500, 15000.00, 125000.00) 


Ну и собственно операция Merge:

MERGE [dbo].[vw_Orders] AS TARGET
USING [dbo].[Orders] AS SOURCE
ON (TARGET.[BusinessEntityID] = SOURCE.[BusinessEntityID] AND SOURCE.[Bonus] > 0)
WHEN MATCHED AND SOURCE.[OrdersCount] > 100 THEN
	UPDATE SET TARGET.[OrdersCount] = SOURCE.[OrdersCount]
WHEN NOT MATCHED BY TARGET THEN 
	INSERT ([BusinessEntityID], [FirstName], [LastName], [JobTitle], [Bonus], [OrdersCount], [Freight], [TotalDue])
	VALUES (SOURCE.[BusinessEntityID], SOURCE.[FirstName], SOURCE.[LastName], SOURCE.[JobTitle], SOURCE.[Bonus], SOURCE.[OrdersCount], SOURCE.[Freight], SOURCE.[TotalDue])
WHEN NOT MATCHED BY SOURCE THEN 
	DELETE;


Но я никак не могу понять - почему мерж не срабатывает? Хоть в SQL Managment Studio скрипт отрабатывает, но изменения во вьюшке не вижу. В чем проблема?
13 окт 13, 22:41    [14963895]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
Если кому интересен что делает мерж:
Цель/target - это вьюшка vw_Orders
Источник/source - таблица Orders

Выборка записей происходит с учетом того по BusinessEntityID и в Orders.Bonus > 0
Если таких данных в target нет — происходит вставка, если таких данных нет в source — удаление. А если данные совпадают и количество заказов у продавца > 100 — обновляется представление
13 окт 13, 22:46    [14963913]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Relrin
При этом не получается сделать прямую вставку данных во вьюшке (такой записи. которой нет в изначальной таблице):
А что вы ожидаете получить при
Relrin
CREATE TABLE [Orders](

	[ID] int IDENTITY (1,1) NOT NULL,
	[BusinessEntityID] INT NOT NULL,
	[FirstName] nvarchar(255) NOT NULL,
	[LastName] nvarchar(255) NOT NULL,
	[JobTitle] nvarchar(255) NOT NULL,
	[Bonus] float DEFAULT(0.00) NOT NULL,
	[OrdersCount] int NOT NULL,
	[Freight] float NOT NULL,
	[TotalDue] float NOT NULL
);
CREATE VIEW [dbo].[vw_Orders]
AS
	SELECT * FROM [dbo].[Orders] 
	WHERE [dbo].[Orders].[ID] = 1 OR [dbo].[Orders].[ID] = 2
GO
?
Запись-то добавляется, только во вью ее не видно.
13 окт 13, 23:15    [14963982]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
invm
Relrin
При этом не получается сделать прямую вставку данных во вьюшке (такой записи. которой нет в изначальной таблице):
А что вы ожидаете получить при
Relrin
CREATE TABLE [Orders](

	[ID] int IDENTITY (1,1) NOT NULL,
	[BusinessEntityID] INT NOT NULL,
	[FirstName] nvarchar(255) NOT NULL,
	[LastName] nvarchar(255) NOT NULL,
	[JobTitle] nvarchar(255) NOT NULL,
	[Bonus] float DEFAULT(0.00) NOT NULL,
	[OrdersCount] int NOT NULL,
	[Freight] float NOT NULL,
	[TotalDue] float NOT NULL
);
CREATE VIEW [dbo].[vw_Orders]
AS
	SELECT * FROM [dbo].[Orders] 
	WHERE [dbo].[Orders].[ID] = 1 OR [dbo].[Orders].[ID] = 2
GO

ID я не указываю, т.к. это первичный ключ, да и сам инкрементируется
?
Запись-то добавляется, только во вью ее не видно.
13 окт 13, 23:17    [14963986]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
o-o
Guest
Relrin,

у Вас неправильное понимание вьюшки.
вьюха, перевод типа "вид", это какой-то взгляд на данные.
это ни разу ни копия, Ваша фраза "Затем формирую вьюшку, копирую туда 2 записи" ошибочна,
ничто и никуда не копируется.
вот у Вас есть таблица в 17 записей.
из них Вы кому-то хотите показывать всего 2 записи оттуда, с id = 1 и с id= 2.
т.е. вьюха это такой код, отбирающий только эти 2 записи.
две, и никогда не больше (может, меньше, если кто-то удалит из таблицы записи с этими id).
у вас id это ПК, значит, больше записей с такими id в таблице не будет.
т.е. вьюха, как Вы ее определили, ну никогда больше двух записей не покажет.
13 окт 13, 23:20    [14963990]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
ID я не указываю, т.к. это первичный ключ, да и сам инкрементируется
13 окт 13, 23:20    [14963991]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
o-o
Relrin,

у Вас неправильное понимание вьюшки.
вьюха, перевод типа "вид", это какой-то взгляд на данные.
это ни разу ни копия, Ваша фраза "Затем формирую вьюшку, копирую туда 2 записи" ошибочна,
ничто и никуда не копируется.
вот у Вас есть таблица в 17 записей.
из них Вы кому-то хотите показывать всего 2 записи оттуда, с id = 1 и с id= 2.
т.е. вьюха это такой код, отбирающий только эти 2 записи.
две, и никогда не больше (может, меньше, если кто-то удалит из таблицы записи с этими id).
у вас id это ПК, значит, больше записей с такими id в таблице не будет.
т.е. вьюха, как Вы ее определили, ну никогда больше двух записей не покажет.


Окей. А как тогда добавить записи, без селекта?
13 окт 13, 23:23    [14963998]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
o-o
Guest
во блин!
записи добавляются, но не во вьюху, а в таблицу.
хочется увидеть запись с BusinessEntityID = 999,
перепишите вьюху, добавив условие в where:
WHERE [dbo].[Orders].[ID] = 1 OR [dbo].[Orders].[ID] = 2 or BusinessEntityID = 999

но Вы добиться-то чего хотите?
13 окт 13, 23:27    [14964014]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
o-o
во блин!
записи добавляются, но не во вьюху, а в таблицу.
хочется увидеть запись с BusinessEntityID = 999,
перепишите вьюху, добавив условие в where:
WHERE [dbo].[Orders].[ID] = 1 OR [dbo].[Orders].[ID] = 2 or BusinessEntityID = 999

но Вы добиться-то чего хотите?


Чтобы я смог сделать пару вещей:
1) добавить 3 записи (две из изначальной таблицы Orders, и одну некую произвольную)
2) при мерже видеть изменения при добавлении/удалении/обновлении вьюшки
13 окт 13, 23:30    [14964026]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
kalimba
Member

Откуда:
Сообщений: 297
Relrin,

Тогда вам нужна другая таблица : ) Плюс использование OUTPUT.
13 окт 13, 23:32    [14964033]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
kalimba
Relrin,

Тогда вам нужна другая таблица : ) Плюс использование OUTPUT.


Неее, мне по заданию нужно работать именно с вьюшкой
13 окт 13, 23:34    [14964037]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
o-o
Guest
а определение этой вьюхи тоже было дано в задании и менять вьюху нельзя?

подозреваю, это проверка на понимание вьюх и ПК.
а все остальное -- отвлекающий маневр.
из серии загадок типа:
ВЫ - пилот самолета.
самолет летит на высоте..., со скоростью ... и т.д.
...а в конце вопрос: сколько лет пилоту самолета?
13 окт 13, 23:40    [14964055]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
А задание такое про мерж:
Напишите Merge выражение, использующее vw_Orders как target, а в качестве source выберите данные из таблицы Orders, с условием что Bonus > 0.
Для соединения target и source используйте BusinessEntityID. Если таких данных в target нет — осуществите вставку, если таких данных нет в source — удаление, если данные совпадают и сумма заказов у продавца > 100 — обновите представление.
13 окт 13, 23:40    [14964060]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Relrin,

Огласите ваше задание. Весьма похоже, что на каком-то этапе вы от этого задания существенно отклонились.
13 окт 13, 23:41    [14964066]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Relrin
добавить 3 записи (две из изначальной таблицы Orders, и одну некую произвольную)
В представлении не содержится никаких записей. Даже если вы сделаете индексированное представление, оно будет содержать записи, удовлетворяющие базовому запросу, произвольные записи туда добавить невозможно.
13 окт 13, 23:42    [14964068]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
o-o
а определение этой вьюхи тоже было дано в задании и менять вьюху нельзя?

подозреваю, это проверка на понимание вьюх и ПК.
а все остальное -- отвлекающий маневр.
из серии загадок типа:
ВЫ - пилот самолета.
самолет летит на высоте..., со скоростью ... и т.д.
...а в конце вопрос: сколько лет пилоту самолета?


Что относительно вьюхи было дано:
1) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы
2)добавьте в представление vw_Orders три записи. Записи должны быть добавлены таким образом, чтобы оператор Merge из следующего задания одну запись удалил (она должна быть полностью вымышленной), одну запись обновил (она должна отличаться хотя бы одним полем) и одну запись оставил такой, как она есть
13 окт 13, 23:43    [14964075]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Relrin
Напишите Merge выражение, использующее vw_Orders как target

И vw_Orders была описана ровно тем скриптом, который был приведён в первом посте темы?
13 окт 13, 23:44    [14964077]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
invm
Relrin
добавить 3 записи (две из изначальной таблицы Orders, и одну некую произвольную)
В представлении не содержится никаких записей. Даже если вы сделаете индексированное представление, оно будет содержать записи, удовлетворяющие базовому запросу, произвольные записи туда добавить невозможно.

Т.е. получается, что во вьюшке должен быть типа

SELECT * FROM [dbo].[Orders]


А работать "по факту" должны с исходной таблицей Orders
13 окт 13, 23:44    [14964080]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
Гость333
Relrin
Напишите Merge выражение, использующее vw_Orders как target

И vw_Orders была описана ровно тем скриптом, который был приведён в первом посте темы?


Да, именно им
13 окт 13, 23:45    [14964081]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Relrin
1) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы
Т.е. в задании не было готового представления?
13 окт 13, 23:49    [14964099]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
invm
Relrin
1) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы
Т.е. в задании не было готового представления?


Не было. Его нужно самому создать
13 окт 13, 23:50    [14964103]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Relrin
Гость333
И vw_Orders была описана ровно тем скриптом, который был приведён в первом посте темы?

Да, именно им

Relrin
invm
Т.е. в задании не было готового представления?

Не было. Его нужно самому создать

Эти два утверждения противоречат друг другу.
13 окт 13, 23:57    [14964124]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
Гость333
Relrin
пропущено...

Да, именно им

Relrin
пропущено...

Не было. Его нужно самому создать

Эти два утверждения противоречат друг другу.


А что тут противоречащего? Код я описал в первом посте, которым формировал вьюшку. По заданию его нужно сформировать на основе таблицы Orders, включающего все поля этой таблицы.
14 окт 13, 00:00    [14964136]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Relrin
1) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы
2)добавьте в представление vw_Orders три записи. Записи должны быть добавлены таким образом, чтобы оператор Merge из следующего задания одну запись удалил (она должна быть полностью вымышленной), одну запись обновил (она должна отличаться хотя бы одним полем) и одну запись оставил такой, как она есть
Relrin
Напишите Merge выражение, использующее vw_Orders как target, а в качестве source выберите данные из таблицы Orders, с условием что Bonus > 0.
Для соединения target и source используйте BusinessEntityID. Если таких данных в target нет — осуществите вставку, если таких данных нет в source — удаление, если данные совпадают и сумма заказов у продавца > 100 — обновите представление.
ИМХО, бред какой-то.
Или это изложение задания в вашем вольном пересказе?
14 окт 13, 00:02    [14964145]     Ответить | Цитировать Сообщить модератору
 Re: Не отрабатывает Merge и вставка во вьюшку данных  [new]
Relrin
Member

Откуда:
Сообщений: 34
invm
Relrin
1) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы
2)добавьте в представление vw_Orders три записи. Записи должны быть добавлены таким образом, чтобы оператор Merge из следующего задания одну запись удалил (она должна быть полностью вымышленной), одну запись обновил (она должна отличаться хотя бы одним полем) и одну запись оставил такой, как она есть
Relrin
Напишите Merge выражение, использующее vw_Orders как target, а в качестве source выберите данные из таблицы Orders, с условием что Bonus > 0.
Для соединения target и source используйте BusinessEntityID. Если таких данных в target нет — осуществите вставку, если таких данных нет в source — удаление, если данные совпадают и сумма заказов у продавца > 100 — обновите представление.
ИМХО, бред какой-то.
Или это изложение задания в вашем вольном пересказе?


Окей. Раз так просите полное задание:

a) создайте таблицу dbo.Orders включающую информацию о продавце (таблица Person поля BusinessEntityID, FirstName и LastName), должность работника (таблица Employee), бонус с заказов (таблица SalesPerson), количество заказов, сумму, уплаченную за отгрузки и общую сумму [TotalDue] (три последних поля нужно посчитать для каждого продавца, используя таблицу SalesOrderHeader);
b) добавьте в таблицу вычисляемое поле, которое будет содержать сумму из [TotalDue] за вычетом суммы, уплаченной за отгрузку;
с) добавьте первичный ключ identity(1,1);
d) создайте для таблицы представление dbo.vw_Orders. Представление должно быть создано для одной таблицы dbo.Orders, включающее все поля этой таблицы;
e) добавьте в представление vw_Orders три записи. Записи должны быть добавлены таким образом, чтобы оператор Merge из следующего задания одну запись удалил (она должна быть полностью вымышленной), одну запись обновил (она должна отличаться хотя бы одним полем) и одну запись оставил такой, как она есть;
f) напишите Merge выражение, использующее vw_Orders как target, а в качестве source выберите данные из таблиц, указанных в задании а), с условием что Bonus > 0.
Для соединения target и source используйте BusinessEntityID. Если таких данных в target нет — осуществите вставку, если таких данных нет в source — удаление, если данные совпадают и сумма заказов у продавца > 100 — обновите представление.
14 окт 13, 00:03    [14964149]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить