INSERT – форма 1. Переходим сразу к практике

Наполним таблицу EmployeesSalaryHistory предоставленными нам данными:

INSERT EmployeesSalaryHistory(EmployeeID,DateFrom,DateTo,Salary)

VALUES

-- Иванов И.И.

(1000,'20131101','20140531',4000),

(1000,'20140601','20141230',4500),

(1000,'20150101',NULL,5000),

-- Петров П.П.

(1001,'20131101','20140630',1300),

(1001,'20140701','20140930',1400),

(1001,'20141001',NULL,1500),

-- Сидоров С.С.

(1002,'20140101',NULL,2500),

-- Андреев А.А.

(1003,'20140601',NULL,2000),

-- Николаев Н.Н.

(1004,'20140701','20150131',1400),

(1004,'20150201','20150131',1500),

-- Александров А.А.

(1005,'20150101',NULL,2000)

 

Таким образом мы вставили в таблицу EmployeesSalaryHistory 11 новых записей.

SELECT *

FROM EmployeesSalaryHistory

 

EmployeeID	DateFrom	DateTo	Salary
1000	2013-11-01	2014-05-31	4000.00
1000	2014-06-01	2014-12-30	4500.00
1000	2015-01-01	NULL	5000.00
1001	2013-11-01	2014-06-30	1300.00
1001	2014-07-01	2014-09-30	1400.00
1001	2014-10-01	NULL	1500.00
1002	2014-01-01	NULL	2500.00
1003	2014-06-01	NULL	2000.00
1004	2014-07-01	2015-01-31	1400.00
1004	2015-02-01	2015-01-31	1500.00
1005	2015-01-01	NULL	2000.00

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

INSERT EmployeesSalaryHistory

VALUES

-- Иванов И.И.

(1000,'20131101','20140531',4000),

(1000,'20140601','20141230',4500),

(1000,'20150101',NULL,5000),

…

 

Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.

Несколько заметок про INSERT:

·Порядок перечисления полей не имеет значения, вы можете написать и (EmployeeID,DateFrom,DateTo,Salary) и (DateFrom,DateTo, EmployeeID,Salary). Здесь важно только то, чтобы он совпадал с порядком значений, которые вы перечисляете в скобках после ключевого слова VALUES.

·Так же важно, чтобы при вставке были заданы значения для всех обязательных полей, которые помечены в таблице как NOT NULL.

·Можно не указывать поля у которых была указана опция IDENTITY или же поля у которых было задано значение по умолчанию при помощи DEFAULT, т.к. в качестве их значения подставится либо значение из счетчика, либо значение, указанное по умолчанию. Такие вставки мы уже делали в первой части.

·В случаях, когда значение поля со счетчиком нужно задать явно используйте опцию IDENTITY_INSERT.

 

В предыдущих частях мы периодически использовали опцию IDENTITY_INSERT. Давайте и здесь воспользуемся данной опцией для создания строк в таблице BonusTypes, у которой поле ID указано с опцией IDENTITY:

-- даем разрешение на добавление/изменение IDENTITY значения

SET IDENTITY_INSERT BonusTypes ON

 

INSERT BonusTypes(ID,Name)VALUES

(1,N'Ежемесячный'),

(2,N'Годовой'),

(3,N'Индивидуальный')

 

-- запрещаем добавление/изменение IDENTITY значения

SET IDENTITY_INSERT BonusTypes OFF

 

Давайте вставим информацию по начислению сотрудникам ЗП, любезно предоставленную нам бухгалтером:

-- Иванов И.И. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1000,'20131130',4000,NULL), (1000,'20131231',4000,NULL), (1000,'20140115',2000,N'Аванс'), (1000,'20140131',2000,NULL), (1000,'20140228',4000,NULL), (1000,'20140331',4000,NULL), (1000,'20140430',4000,NULL), (1000,'20140531',4000,NULL), (1000,'20140630',6500,N'ЗП + Аванс 2500 за 2014.07'), (1000,'20140731',2000,NULL), (1000,'20140831',4500,NULL), (1000,'20140930',4500,NULL), (1000,'20141031',4500,NULL), (1000,'20141130',4500,NULL), (1000,'20141230',4500,NULL), (1000,'20150131',5000,NULL), (1000,'20150228',5000,NULL), (1000,'20150331',5000,NULL)	-- Петров П.П. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1001,'20131130',2600,N'ЗП + ЗП за 2013.12'), (1001,'20140228',2600,N'За 2 месяца 2014.01, 2014.02'), (1001,'20140331',1300,NULL), (1001,'20140430',1300,NULL), (1001,'20140510',300,N'Аванс'), (1001,'20140520',500,N'Аванс'), (1001,'20140531',500,NULL), (1001,'20140630',1300,NULL), (1001,'20140731',1400,NULL), (1001,'20140831',1400,NULL), (1001,'20140930',1400,NULL), (1001,'20141031',1500,NULL), (1001,'20141130',1500,NULL), (1001,'20141230',3000,N'ЗП + ЗП за 2015.01'), (1001,'20150228',1500,NULL), (1001,'20150331',1500,NULL)
-- Сидоров С.С. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1002,'20140131',2500,NULL), (1002,'20140228',2500,NULL), (1002,'20140331',2500,NULL), (1002,'20140430',2500,NULL), (1002,'20140531',2500,NULL), (1002,'20140630',2500,NULL), (1002,'20140731',2500,NULL), (1002,'20140831',2500,NULL), (1002,'20140930',2500,NULL), (1002,'20141031',2500,NULL), (1002,'20141130',2500,NULL), (1002,'20141230',2500,NULL), (1002,'20150131',2500,NULL), (1002,'20150228',2500,NULL), (1002,'20150331',2500,NULL)	-- Андреев А.А. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1003,'20140630',2000,NULL), (1003,'20140731',2000,NULL), (1003,'20140831',2000,NULL), (1003,'20140930',2000,NULL), (1003,'20141031',2000,NULL), (1003,'20141130',2000,NULL), (1003,'20141230',2000,NULL), (1003,'20150131',2000,NULL), (1003,'20150228',2000,NULL), (1003,'20150331',2000,NULL)
-- Николаев Н.Н. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1004,'20140731',1400,NULL), (1004,'20140831',1400,NULL), (1004,'20140930',1400,NULL), (1004,'20141031',1400,NULL), (1004,'20141130',1400,NULL), (1004,'20141212',400,N'Аванс'), (1004,'20141230',1400,NULL), (1004,'20150131',1400,NULL), (1004,'20150228',1500,NULL), (1004,'20150331',1500,NULL)	-- Александров А.А. INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES (1005,'20150131',2000,NULL), (1005,'20150228',2000,NULL), (1005,'20150331',2000,NULL)

Думаю, приводить содержимое таблицы уже нет смысла.

INSERT – форма 2

Данная форма позволяет вставить в таблицу данные полученные запросом.

Для демонстрации наполним таблицу с начислениями бонусов одним большим запросом:

INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent)

-- расчет ежемесячных бонусов

SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent,1 BonusTypeID,emp.BonusPercent

FROM EmployeesSalaryHistory hist

JOIN

(

VALUES -- весь период работы компании - последние дни месяцев

('20131130'),

('20131231'),

('20140131'),

('20140228'),

('20140331'),

('20140430'),

('20140531'),

('20140630'),

('20140731'),

('20140831'),

('20140930'),

('20141031'),

('20141130'),

('20141230'),

('20150131'),

('20150228'),

('20150331')

) bdate(BonusDate)

ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')

JOIN Employees emp ON hist.EmployeeID=emp.ID

WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0

AND NOT EXISTS(-- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период

         SELECT *

         FROM

           (

             VALUES

               (1001,'20140115'),

               (1001,'20140430'),

               (1001,'20141031'),

               (1001,'20141130'),

               (1001,'20150228')

          ) exclude(EmployeeID,BonusDate)

         WHERE exclude.EmployeeID=emp.ID

           AND exclude.BonusDate=bdate.BonusDate

      )

 

UNION ALL

 

-- годовой бонус за 2014 год - всем кто проработал больше полугода

SELECT

hist.EmployeeID,

'20141231' BonusDate,

hist.Salary/100*

CASE DepartmentID

WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusAmount,

2 BonusTypeID,

CASE DepartmentID

WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам

WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам

ELSE 5 -- всем остальным по 5%

END BonusPercent

FROM EmployeesSalaryHistory hist

JOIN Employees emp ON hist.EmployeeID=emp.ID

WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')

AND emp.HireDate<='20140601'

 

UNION ALL

 

-- индивидуальные бонусы

SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent

FROM

(

VALUES

(1001,'20140930',300),

(1002,'20140331',500),

(1002,'20140630',500),

(1002,'20140930',500),

(1002,'20141230',500),

(1002,'20150331',500),

(1004,'20140831',200)

) indiv(EmployeeID,BonusDate,BonusAmount)

 

В таблицу EmployeesBonus должно было вставиться 50 записей.

Результат каждого запроса объединенных конструкциями UNION ALL вы можете проанализировать самостоятельно. Если вы хорошо изучили базовые конструкции, то вам должно быть все понятно, кроме возможно конструкции с VALUES (конструктор табличных значений), которая появилась с MS SQL 2008.