Sql server заполнение таблицы: использование инструкции INSERT INTO, синтаксис и примеры

Содержание

Разработка таблиц и ограничений SQL — ПРАКТИЧЕСКАЯ РАБОТА No3. РАЗРАБОТКА ТАБЛИЦ И ОГРАНИЧЕНИЙ 3.

ПРАКТИЧЕСКАЯ РАБОТА №3. РАЗРАБОТКА ТАБЛИЦ И

ОГРАНИЧЕНИЙ

3.1. Цель практической работы

Изучить способы создания, изменения и удаления таблиц. Получить навыки

использования приложения » SQL Server Management Studio » для создания, удаления и

изменения структуры таблиц. Изучить SQL-операторы для работы с таблицами и

индексами. Изучить используемые в SQL Server типы ограничений. Получить навыки

использования программы » SQL Server Management Studio » для создания, изменения

и удаления ограничений. Изучить SQL-операторы для работы с ограничениями.

3.2. Исходные данные

Исходными данными является индивидуальное задание и результат предыдущих

практических работ.

3.3. Используемые программы

Программа » SQL Server Management Studio » и установленный сервер Microsoft

SQL Server .

Часть 1. Создание таблиц

3.4. Теоретические сведения

3.4.1. Логическая структура и физическая реализация баз данных MS SQL Server

Данные в сервере SQL Server хранятся в базах данных. Структуру баз данных

необходимо рассматривать на двух уровнях: логическом и физическом.

Логическая структура базы данных определяет структуру таблиц, взаимосвязи

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

объекты базы данных.

Физическая структура базы данных включает в себя описание файлов и групп файлов

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

данных, ее максимальный размер, параметры конфигурации и другие физические

характеристики.

Все логические компоненты базы данных SQL Server называются объектами и

подразделяются на 11 типов.

Для объектов можно устанавливать различные правила доступа со стороны

пользователей.

Функциональное назначение объектов базы данных сервера SQL Server можно

кратко определить следующим образом:

1. Table — таблица, представляющая собой матрицу из строк и столбцов.

Каждая строка (или запись) состоит из значений атрибутов конкретного объекта.

Столбец (или поле записи) содержит совокупность атрибутов рассматриваемых

объектов некоторой предметной области. Некоторые столбцы таблицы могут быть

вычисляемыми. В этих случаях для них задается расчетная формула.

2. User-defined data type — пользовательский тип данных, создаваемый на основе

системных. Имя нового типа должно быть уникальным в пределах владельца.

3.View — представление, являющееся виртуальной таблицей, содержимое которой

определяется запросом. Эта таблица не содержит данных, а только их представляет,

возможно из нескольких таблиц. Данные из представления не сохраняются в базе

данных. Физически представление реализуется в виде запроса SELECT. Представления

используются в следующих случаях:

a) Для ограничения доступа пользователей к определенным строкам таблицы;

b) Для ограничения доступа пользователей к определенным столбцам таблицы;

c) Для представления данных столбцов разных таблиц в виде одного объекта;

Целочисленные, символьные и GUID-ключи в MS SQL

Сравнение производительности некоторых SQL-операций при использовании целочисленных, символьных и GUID-ключей. Тесты проводились на Microsoft Server 2000 и 2005.

Обновление от 2012-11-01. Более развернутый тест для SQL Server 2012 с учетом параметров хранения.

Тест создает в базе данных 6 таблиц. Таблицы Т1 и Т11 имеют целочисленные первичные
ключи типа int, Т1 связана с Т11 в отношении «один-ко-многим». Имеются аналогичные
таблицы Т2 и Т21 с символьными ключами типа char(15). В каждой таблице имеется индекс
на первичный ключ. Таблицы Т11 и Т21 имеют также индексы на внешние ключи. Для GUID-ключей были добавлены таблицы T3 и T31. Этот тест проводился только
на MS SQL 2005.

В тесте определятся параметры-переменные:

@ParentCount определяет количество записей в главной таблице
@ChildCount определяет количество записей в связанной таблице на каждую
запись в главной
@StagesCount количество циклов повторения этапа выполнения запросов для
получения усредненных результатов
@MaxNum максимальное значение для диапазона значений поля Num (0,
@MaxNum]

В первой части теста выполняется создание и заполнение таблиц данными. Ключи
генерируются последовательным образом, однако для символьных полей порядковый номер
добавляемой записи преобразуется в «NKEY00000…», где N — порядковый номер, KEY0000
— символьное выражение, дополняющее длину преобразованного в строку номера до 15
(Это выполняет процедура GetCharKey).

Для полей Num в таблицах T1 и Т2 генерируются случайные значения в диапазоне
(0, @MaxNum]. Значение @MaxNum следует выбирать меньшим в разы, чем количество записей
в главной таблице.

Поля Title всех таблиц заполняются фиксированными значениями вида: «‘Title T:
KEY'», где Т — название таблицы, KEY — значение ее ключа, преобразованного в строку.
Имеется возможность заполнять строки случайным образом, однако это резко увеличивает
время, требуемое для заливки первоначальных данных. Тем не менее, для включения
этого режима достаточно удалить комментарии в тексте вызов процедуры GetRandomString
и установить комментарии на строку присвоения переменной @Title фиксированного значения.

Во второй части теста производятся запросы к обеим таблицам, вида:

SELECT @j = count(*)
FROM T1 JOIN T11 ON T1.T1ID = T11.T1ID
WHERE T1.Num = @k

Значение @k выбирается случайным образом
из диапазона (0, @MaxNum]. Запрос представляет собой подсчет количества записей,
попавших в запрос после эквисоединения двух таблиц по ключу с условием фильтрации
по случайно выбираемым значением поля Num. При первоначально выбранных значениях
@MaxNum меньших, чем @ParentsCount обеспечивается выборка более чем одной записи
из главной таблицы и их соединение с записями из связанной таблицы. Поле, по которому
происходит фильтрация также имеет индекс, избирательность которого напрямую зависит
от соотношения @ParentsCount к @MaxNum. К каждой групп таблиц производится @ParentsCount
запросов.

В третьей части теста производятся простые поисковые запросы в связанных таблицах
по ключу, значение которого выбирается случайным образом из диапазона ([email protected]].
К каждой групп таблиц также производится @ParentsCount запросов.

Текст SQL-скрипта теста вы можете скачать по ссылке на файл mssql_keys_test.sql в конце страницы

В разное время для проведения теста использовались две конфигурации.

Конфигурация 1: MS SQL Server 2000 Evaluation, установленный на ПК Pentium-II-300,
RAM 256, HDD 20 Gb UltraATA-33. Операционная система: Windows NT Workstation 4 SP5.

Конфигурация 2: MS SQL Server 2005 Developer, установленный на ПК AMD Athlon
2400, RAM 1 Gb, HDD WesternDigital 240 Gb Serial ATA 300. Операционная система:
Windows XP Pro SP2.

Под SQL Server в обоих случаях выделялось не более 192 Мб физической памяти.
Размер тестовой БД — 500 Мб (исключает динамическое увеличение), журнала — 50 Мб,
Размер временной БД (tempdb) — 300 Мб.

Параметры:

Параметр Значение на прогоне
Прогон 1 Прогон 2 Прогон 3
@ParentsCount 10 000 10 000 10 000
@ChildsCount 30 40 50
@StagesCount 10 10 10
@MaxNum 1 000 1 000 1 000

Таким образом, на каждом прогоне в группу таблиц заносится: 10 000 записей в
главную и 300 000 (400 000 и 500 000) — в связанную. Выполняется 10 циклов выполнения
запросов, по 10 000 к каждой группе таблиц в цикле (всего 100 000 запросов к каждой
группе). Так как соотношение @ParentsCount к @MaxNum составляет на всех прогонах
10, то в среднем за каждый запрос с эквисоединением выбирается 10*@ChildsCount записей
(300, 400 и 500, соответственно).

Наиболее интересны несколько выявленных наблюдений:

  • Поиск по ключу символьного поля производится примерно так же быстро или
    даже быстрее, чем по целочисленному
  • MS SQL 2005 производит соединение по целочисленным ключам примерно в 2 раза
    быстрее своего предшественника (относительно скорости соединения по символьным
    ключам)
  • При использование GUID-ключей быстродействие сравнимо или даже лучше такового
    при целочисленных ключах
  • Мощность компьютера возросла: в 8 раз по частоте процессора, в 10 раз по
    частоте шины контролера диска (ОЗУ в 4 раза можно не учитывать, т.к. объемы
    данных не изменились). При этом скорость обработки запросов выросла только в
    5-6 раз.
Тест и этап Результаты
Таблицы с целочисленными
ключами, мсек
Таблицы с символьными ключами,
мсек
Таблицы с GUID-ключами, мсек Соотношение времени (2):(3):(4),
%
(1) (2) (3) (4) (5)
Прогон 1 Прогон 2 Прогон 3(1) Прогон 1 Прогон 2 Прогон 3 Прогон 1

 

Прогон 2 Прогон 3 Прогон 1 Прогон 2 Прогон 3
MS SQL 2000 (конфигурация 1)                        
Заполнение данными 321003 396870 504393 355280 459153 665026 н/д н/д н/д 90 : 100 86 : 100 76 : 100
Выборка эквисоединением 7123 9555 10352 10042 13147 14743 н/д н/д н/д 70 : 100 73 : 100 70 : 100
Поиск по ключу 1475 1921 1899 1298 1546 1414 н/д н/д н/д 100 : 88 100 : 80 100 : 75
       
MS SQL 2005 (конфигурация 2)                        
Заполнение данными 64080 86670 100860 70233 102923 125313 61623 96703 107343 91 : 100 : 88 84 : 100 : 94 80 : 100 : 86
Выборка эквисоединением 821 996 1445 2312 2860 4388 1053 1135 1274 36 : 100 : 46 35 : 100 : 40 33 : 100 : 29
Поиск по ключу 244 265 263 241 198 213 214 180 202 100 : 99 : 88 100 : 75 : 68 100 : 81 : 77

С некластерными индексами
(конфигурация 2)

                       
Заполнение данными 67313 90470 108280 75906 113436 124986 72483 98063 133280 89 : 100 : 95 80 : 100 : 86 81 : 94 : 100
Выборка эквисоединением 1397 1882 3554 2199 2744 6086 970 1169 1855 64 : 100 : 44 69 : 100 : 43 58 : 100 : 30
Поиск по ключу 244 293 274 197 237 239 191 199 152 100 : 81 : 78 100 : 81 : 68 100 : 87 : 55

Примечание:

1 — для конфигурации с некластерными индексами доступная физическая
память для SQL Server была увеличена до 256 Мб

Сергей Тарасов, июнь 2001. С дополнениями, февраль-апрель 2007

set nocount on
go
 
-- таблицы с целочисленными ключами
create table T1
(
	T1ID int not null,
	Num int not null,
	Title varchar(100) null
	constraint PK_T1 primary key (T1ID),
)
go
create index IX_T1 on T1(Num)
go
 
create table T11
(
	T11ID int not null,
	T1ID int not null,
	Title varchar(100) null,
	constraint PK_T11 primary key (T11ID),
	foreign key(T1ID) references T1(T1ID)
)
go
create index IX_T11 on T11(T1ID)
go
 
-- таблицы с символьными ключами
create table T2
(
	T2ID char(15) not null,
	Num int not null,
	Title varchar(100) null
	constraint PK_T2 primary key (T2ID),
)
go
create index IX_T2 on T2(Num)
go
 
create table T21
(
	T21ID char(15) not null,
	T2ID char(15) not null,
	Title varchar(100) null,
	constraint PK_T21 primary key (T21ID),
	foreign key(T2ID) references T2(T2ID)
)
go
create index IX_T21 on T21(T2ID)
go
 
-- служебные процедуры и функции
create procedure GetRandomString
	@Length int = 0,
	@Result varchar(255) output
as
begin
	if @Length > 255
		set @Length = 255
 
	declare @i int
	declare @ch char(1)
 
	set @i = 0
	set @Result = ''
	while (@i < @Length) begin
		set @ch = char(convert(int, (rand() * (128 - 33)) ) + 33)
		set @Result = @Result + @ch
		set @i = @i + 1
	end
end
go
 
 
create procedure GetCharKey
	@Key int,
	@Result char(15) output
as
begin
	declare @s varchar(15)
	declare @i int
	set @s = cast(@Key as varchar(15))
	set @i = 15 - len(@s)
	set @Result = @s + 'KEY' + replicate('0', @i - 3)
end
go
 
-- заполнение таблиц данными
declare @ParentCount	int		-- общее количество записей в главной таблице
declare @ChildCount int			-- количество дочерних записей для каждой родительской
declare @StagesCount int		-- оличество циклов повторения этапа выполнения запросов для получения усредненных результатов
declare @MaxNum int					-- максимальное значение для диапазона значений поля Num (0, @MaxNum]
declare @i int, @j int, @k int, @Stage int
declare @Title varchar(100)
declare @ParentKey char(15), @ChildKey char(15)
declare @StartTime datetime
declare @Time1 int, @Time2 int
 
set @ParentCount = 10000
set @MaxNum = @ParentCount / 10
set @ChildCount = 30
 
-- таблицы с целочисленными ключами
set @i = 1
set @k = 1
select @StartTime = getdate()
while (@i <= @ParentCount) begin
-- вызов формирования строки из случайного набора символов закомментарен для быстроты прохождения этапа заливки данных
-- вместо этого используем строку, включающую ключи и фиксированный текст
--	exec GetRandomString 100, @Title output
	exec GetCharKey @i, @ParentKey output  -- для уравнивания количества требуемых операция при заливке
	set @Title = 'Title T1: ' + cast(@i as varchar)
	insert into T1 (T1ID, Num, Title) values (@i, cast((rand() * @MaxNum) as int)+ 1, @Title)
	set @j = 1
	while (@j <= @ChildCount) begin
--		exec GetRandomString 100, @Title output
		set @Title = 'Title T11: ' + cast(@k as varchar) + ' ' + cast(@i as varchar)
		exec GetCharKey @k, @ChildKey output
		insert into T11 (T11ID, T1ID, Title) values (@k, @i, @Title)
		set @j = @j + 1
		set @k = @k + 1
	end
	set @i = @i + 1
end
print N'Закачка данных (таблицы с целочисленными ключами) завершена за ' + cast(datediff(ms, @StartTime, getdate()) as varchar) + N' мсек'
 
 
-- таблицы с символьными ключами
set @i = 1
set @k = 1
select @StartTime = getdate()
while (@i <= @ParentCount) begin
--	exec GetRandomString 100, @Title output
	set @Title = 'Title T2: ' + cast(@i as varchar)
	exec GetCharKey @i, @ParentKey output
	insert into T2 (T2ID, Num, Title) values (@ParentKey, cast((rand() * @MaxNum) as int)+ 1, @Title)
	set @j = 1
	while (@j <= @ChildCount) begin
--		exec GetRandomString 100, @Title output
		set @Title = 'Title T21: ' + cast(@k as varchar) + ' ' + cast(@i as varchar)
		exec GetCharKey @k, @ChildKey output
		insert into T21 (T21ID, T2ID, Title) values (@ChildKey, @ParentKey, @Title)
		set @j = @j + 1
		set @k = @k + 1
	end
	set @i = @i + 1
end
print N'Закачка данных (таблицы с символьными ключами) завершена за ' + cast(datediff(ms, @StartTime, getdate()) as varchar) + N' мсек'
 
 
set @StagesCount = 10
set @Stage = 1
set @Time1 = 0
set @Time2 = 0
while (@Stage <= @StagesCount) begin
	-- выполнение запросов со связью по ключу
	set @i = 1
	set @j = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@MaxNum * rand()) as int) + 1
		select @j = count(*)
			from T1 join T11 on T1. T1ID = T11.T1ID
			where T1.Num = @k
		set @i = @i + 1
	end
	set @Time1 = @Time1 + datediff(ms, @StartTime, getdate())
 
	set @i = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@MaxNum * rand()) as int) + 1
		select @j = count(*)
			from T2 join T21 on T2.T2ID = T21.T2ID
			where T2.Num = @k
		set @i = @i + 1
	end
	set @Time2 = @Time2 + datediff(ms, @StartTime, getdate())
	set @Stage = @Stage + 1
end
 
print N'Проведено циклов: ' + cast(@StagesCount as varchar)
print N'Запросы выполнены (таблицы с целочисленными ключами) за ' + cast((@Time1/@StagesCount) as varchar) + N' мсек'
print N'Запросы выполнены (таблицы с символьными ключами) за ' + cast((@Time2/@StagesCount) as varchar) + N' мсек'
 
set @Stage = 1
set @Time1 = 0
set @Time2 = 0
while (@Stage <= @StagesCount) begin
	-- выполнение запросов со связью по ключу
	set @i = 1
	set @j = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@ChildCount * rand()) as int) + 1
		exec GetCharKey @k, @ParentKey output
		select @j = count(*)
			from T11
			where T11. T1ID = @k
		set @i = @i + 1
	end
	set @Time1 = @Time1 + datediff(ms, @StartTime, getdate())
 
	set @i = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@ChildCount * rand()) as int) + 1
		exec GetCharKey @k, @ParentKey output
		select @j = count(*)
			from T21
			where T21.T21ID = @ParentKey
		set @i = @i + 1
	end
	set @Time2 = @Time2 + datediff(ms, @StartTime, getdate())
	set @Stage = @Stage + 1
end
 
print N'Проведено циклов: ' + cast(@StagesCount as varchar)
print N'Запросы выполнены (таблицы с целочисленными ключами) за ' + cast((@Time1/@StagesCount) as varchar) + N' мсек'
print N'Запросы выполнены (таблицы с символьными ключами) за ' + cast((@Time2/@StagesCount) as varchar) + N' мсек'
go
 
-- очистка БД после теста
drop table T11
drop table T1
drop table T21
drop table T2
 
drop procedure GetRandomString
drop procedure GetCharKey
go
 
set nocount off
go
  • SQL Server
  • Testing

Урок 2: Создание данных в иерархической таблице и управление ими — SQL Server

  • Статья
  • 10 минут на чтение

Применимо к:
SQL Server (все поддерживаемые версии)
База данных SQL Azure
Управляемый экземпляр Azure SQL

На уроке 1 вы изменили существующую таблицу, чтобы использовать иерархический идентификатор типа данных и заполнил столбец иерархический идентификатор представлением существующих данных. На этом уроке вы начнете с новой таблицы и вставите данные, используя иерархические методы. Затем вы будете запрашивать и обрабатывать данные, используя иерархические методы.

Предварительные требования

Для выполнения этого руководства вам потребуется SQL Server Management Studio, доступ к серверу, на котором работает SQL Server, и база данных AdventureWorks.

  • Установите SQL Server Management Studio.
  • Установите SQL Server 2017 Developer Edition.
  • Загрузите образцы баз данных AdventureWorks2017.

Инструкции по восстановлению баз данных в SSMS находятся здесь: Восстановите базу данных.

Создайте таблицу, используя тип данных иерархии

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

  • OrgNode — это столбец иерархического идентификатора , в котором хранятся иерархические отношения.
  • OrgLevel — это вычисляемый столбец, основанный на столбце OrgNode, в котором хранится каждый уровень узлов в иерархии. Он будет использоваться для индекса ширины.
  • EmployeeID содержит типичный идентификационный номер сотрудника, который используется для таких приложений, как расчет заработной платы. При разработке новых приложений приложения могут использовать столбец OrgNode, и этот отдельный столбец EmployeeID не требуется.
  • EmpName содержит имя сотрудника.
  • Должность содержит должность сотрудника.

Создайте таблицу EmployeeOrg

  1. В окне редактора запросов выполните следующий код, чтобы создать таблицу EmployeeOrg . Указание столбца OrgNode в качестве первичного ключа с кластеризованным индексом создаст индекс в глубину:

     ИСПОЛЬЗОВАТЬ AdventureWorks2017 ;
    ИДТИ
    если OBJECT_ID('HumanResources.EmployeeOrg') не равен нулю
     удалить таблицу HumanResources.EmployeeOrg
    СОЗДАТЬ ТАБЛИЦУ HumanResources.EmployeeOrg
    (
       Иерархический идентификатор OrgNode PRIMARY KEY CLUSTERED,
       OrgLevel AS OrgNode.GetLevel(),
       ID сотрудника int UNIQUE NOT NULL,
       EmpName varchar(20) НЕ NULL,
       Заголовок varchar(20) NULL
    ) ;
    ИДТИ
     
  2. Запустите следующий код, чтобы создать составной индекс для столбцов OrgLevel и OrgNode для поддержки эффективного поиска в ширину:

     СОЗДАТЬ УНИКАЛЬНЫЙ ИНДЕКС EmployeeOrgNc1
    ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;
    ИДТИ
     

Теперь таблица готова для данных. Следующая задача заполнит таблицу с помощью иерархических методов.

Заполнение иерархической таблицы с использованием иерархических методов

AdventureWorks2017 имеет 8 сотрудников, работающих в отделе маркетинга. Иерархия сотрудников выглядит следующим образом:

Дэвид , EmployeeID 6, менеджер по маркетингу. Три специалиста по маркетингу подчиняются Дэвиду :

  • Сария , EmployeeID 46

  • Джон , Идентификатор сотрудника 271

  • Джилл , ID сотрудника 119

Ассистент по маркетингу Ванида ( Идентификатор сотрудника 269), подчиняется Сария и Ассистент по маркетингу Мэри ( Идентификатор сотрудника 272), подчиняется Джон .

Вставка корня дерева иерархии

  1. В следующем примере Дэвид Менеджер по маркетингу вставляется в таблицу в корне иерархии. Столбец OrdLevel является вычисляемым столбцом. Поэтому он не является частью оператора INSERT. Эта первая запись использует метод GetRoot() для заполнения этой первой записи в качестве корня иерархии.

     INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Должность)
    ЗНАЧЕНИЯ (hierarchyid::GetRoot(), 6, 'Дэвид', 'Менеджер по маркетингу') ;
    ИДТИ
     
  2. Выполните следующий код, чтобы проверить начальную строку в таблице:

     ВЫБРАТЬ OrgNode.ToString() AS Text_OrgNode,
    OrgNode, OrgLevel, EmployeeID, EmpName, Должность
    ОТ HumanResources.EmployeeOrg ;
     

    Вот результирующий набор.

     Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Заголовок
    ------------ ------- -------- ---------- ------- ------ -----------
    / Ox 0 6 Дэвид Менеджер по маркетингу
     

Как и в предыдущем уроке, мы используем метод ToString() для преобразования типа данных иерархии в более понятный формат.

Вставить подчиненного сотрудника

  1. Сария подчиняется Давиду . Чтобы вставить узел Сарии, необходимо создать соответствующее значение OrgNode с типом данных иерархический идентификатор . Следующий код создает переменную типа данных иерархический идентификатор и заполняет его корневым значением OrgNode таблицы. Затем использует эту переменную с методом GetDescendant() для вставки строки, которая является подчиненным узлом. GetDescendant принимает два аргумента. Просмотрите следующие варианты значений аргументов:

    .

    • Если parent имеет значение NULL, GetDescendant возвращает NULL.
    • Если значение parent не равно NULL, а значения child1 и child2 равны NULL, GetDescendant возвращает дочерний элемент parent.
    • Если parent и child1 не равны NULL, а child2 имеют значение NULL, GetDescendant возвращает дочерний элемент родителя, больший, чем child1.
    • Если parent и child2 не равны NULL, а child1 имеет значение NULL, GetDescendant возвращает дочерний элемент родителя меньше, чем child2.
    • Если parent, child1 и child2 не равны NULL, GetDescendant возвращает дочерний элемент родителя, больший, чем child1, и меньший, чем child2.

    В следующем коде используется (NULL, NULL) аргументы корневого родителя, поскольку в таблице еще нет строк, кроме корневой. Выполните следующий код, чтобы вставить Сария :

     DECLARE Иерархический идентификатор @Manager
    ВЫБЕРИТЕ @Manager = иерархический идентификатор:: GetRoot ()
    ОТ HumanResources.EmployeeOrg ;
    ВСТАВЬТЕ HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Должность)
    ЗНАЧЕНИЯ
    (@Manager.GetDescendant(NULL, NULL), 46 лет, «Сария», «Специалист по маркетингу») ;
     
  2. Повторите запрос из первой процедуры, чтобы запросить таблицу и посмотреть, как появляются записи:

     ВЫБРАТЬ OrgNode. ToString() AS Text_OrgNode,
    OrgNode, OrgLevel, EmployeeID, EmpName, Должность
    ОТ HumanResources.EmployeeOrg ;
     

    Вот результирующий набор.

     Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Заголовок
    ------------ ------- -------- ---------- ------- ------ -----------
    / Ox 0 6 Дэвид Менеджер по маркетингу
    /1/ 0x58 1 46 Сария Специалист по маркетингу
     

Создать процедуру ввода новых узлов

  1. Чтобы упростить ввод данных, создайте следующую хранимую процедуру для добавления сотрудников в таблицу EmployeeOrg . Процедура принимает входные данные о добавляемом сотруднике. Это включает в себя EmployeeID менеджера нового сотрудника, номер нового сотрудника EmployeeID , а также его имя и должность. Процедура использует GetDescendant() , а также метод GetAncestor(). Выполните следующий код, чтобы создать процедуру:

     CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20))
    В КАЧЕСТВЕ
    НАЧИНАТЬ
       DECLARE @mOrgNode иерархический идентификатор, @lc иерархический идентификатор
       ВЫБЕРИТЕ @mOrgNode = OrgNode
       ОТ HumanResources. EmployeeOrg
       ГДЕ Сотрудник ID = @mgrid
       УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ SERIALIZABLE
       НАЧАТЬ СДЕЛКУ
          ВЫБЕРИТЕ @lc = max (OrgNode)
          ОТ HumanResources.EmployeeOrg
          ГДЕ OrgNode.GetAncestor(1) [email protected] ;
          ВСТАВЬТЕ HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Должность)
          ЗНАЧЕНИЯ(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)
       СОВЕРШИТЬ
    КОНЕЦ ;
    ИДТИ
     
  2. В следующем примере добавляются оставшиеся 4 сотрудника, которые прямо или косвенно подчиняются Дэвиду .

     EXEC AddEmp 6, 271, 'Джон', 'Специалист по маркетингу' ;
    EXEC AddEmp 6, 119, «Джилл», «Специалист по маркетингу»;
    EXEC AddEmp 46, 269, «Ванида», «Помощник по маркетингу»;
    EXEC AddEmp 271, 272, «Мэри», «Ассистент по маркетингу»;
     
  3. Снова выполните следующий запрос, проверьте строки в таблице EmployeeOrg :

     ВЫБРАТЬ OrgNode.ToString() AS Text_OrgNode,
    OrgNode, OrgLevel, EmployeeID, EmpName, Должность
    ОТ HumanResources. EmployeeOrg ;
    ИДТИ
     

    Вот результирующий набор.

     Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Заголовок
    ------------ ------- -------- ---------- ------- ------ -----------
    / Ox 0 6 Дэвид Менеджер по маркетингу
    /1/ 0x58 1 46 Сария Специалист по маркетингу
    /1/1/ 0x5AC0 2 269 Помощник по маркетингу компании Wanida
    /2/ 0x68 1 271 Джон Специалист по маркетингу
    /2/1/ 0x6AC0 2 272 Мэри Ассистент по маркетингу
    /3/ 0x78 1 119Джилл Специалист по маркетингу
     

Теперь таблица полностью заполнена маркетинговой организацией.

Запрос иерархической таблицы с использованием методов иерархии

Теперь, когда таблица HumanResources.EmployeeOrg полностью заполнена, эта задача покажет вам, как выполнять запросы к иерархии с использованием некоторых иерархических методов.

Поиск подчиненных узлов

  1. У Сарии есть один подчиненный сотрудник. Чтобы запросить подчиненных Сарии, выполните следующий запрос, использующий метод IsDescendantOf:

     DECLARE @CurrentEmployee иерархический идентификатор
    ВЫБЕРИТЕ @CurrentEmployee = OrgNode
    ОТ HumanResources. EmployeeOrg
    ГДЕ Сотрудник ID = 46 ;
    ВЫБРАТЬ *
    ОТ HumanResources.EmployeeOrg
    ГДЕ OrgNode.IsDescendantOf(@CurrentEmployee) = 1 ;
     

    В списке указаны Сария и Ванида. Сария указана в списке, потому что она является потомком 0-го уровня. Ванида — потомок на 1 уровне.

  2. Вы также можете запросить эту информацию с помощью метода GetAncestor. GetAncestor принимает аргумент уровня, который вы пытаетесь вернуть. Поскольку Ванида находится на один уровень ниже Сарии, используйте GetAncestor(1) , как показано в следующем коде:

     DECLARE @CurrentEmployee иерархический идентификатор
    ВЫБЕРИТЕ @CurrentEmployee = OrgNode
    ОТ HumanResources.EmployeeOrg
    ГДЕ Сотрудник ID = 46 ;
    ВЫБЕРИТЕ OrgNode.ToString() AS Text_OrgNode, *
    ОТ HumanResources.EmployeeOrg
    ГДЕ OrgNode.GetAncestor(1) = @CurrentEmployee
     

    На этот раз в списке только Ванида.

  3. Теперь измените @CurrentEmployee на David (EmployeeID 6) и уровень на 2. Выполните следующие действия, чтобы также вернуть Wanida:

     DECLARE @CurrentEmployee иерархический идентификатор
    ВЫБЕРИТЕ @CurrentEmployee = OrgNode
    ОТ HumanResources.EmployeeOrg
    ГДЕ Сотрудник ID = 6 ;
    ВЫБЕРИТЕ OrgNode.ToString() AS Text_OrgNode, *
    ОТ HumanResources.EmployeeOrg
    ГДЕ OrgNode.GetAncestor(2) = @CurrentEmployee
     

    На этот раз вы также получаете Мэри, которая также отчитывается перед Дэвидом двумя уровнями ниже.

Используйте GetRoot и GetLevel

  1. По мере роста иерархии становится все труднее определить, где в иерархии находятся элементы. Используйте метод GetLevel, чтобы узнать, на сколько уровней ниже находится каждая строка в иерархии. Выполните следующий код, чтобы просмотреть уровни всех строк:

     ВЫБРАТЬ OrgNode.ToString() AS Text_OrgNode,
    OrgNode.GetLevel() AS EmpLevel, *
    ОТ HumanResources.EmployeeOrg ;
    ИДТИ
     
  2. Используйте метод GetRoot, чтобы найти корневой узел в иерархии. Следующий код возвращает единственную строку, которая является корнем:

     ВЫБРАТЬ OrgNode. ToString() AS Text_OrgNode, *
    ОТ HumanResources.EmployeeOrg
    ГДЕ OrgNode = иерархия::GetRoot() ;
    ИДТИ
     

Изменение порядка данных в иерархической таблице с использованием иерархических методов

Применяется к:
SQL Server (все поддерживаемые версии)

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

Метод GetReparentedValue принимает два аргумента. Первый аргумент описывает изменяемую часть иерархии. Например, если иерархия имеет вид /1/4/2/3/, и вы хотите изменить раздел /1/4/, иерархия станет /2/1/2/3/, оставив последние два узла ( 2/3/ ) не изменились, вы должны указать изменяющиеся узлы ( /1/4/ ) в качестве первого аргумента. Второй аргумент предоставляет новый уровень иерархии, в нашем примере 9.0015/2/1/ . Два аргумента не обязательно должны содержать одинаковое количество уровней.

Переместить одну строку в новое место в иерархии

  1. В настоящее время Ванида отчитывается перед Сарией. В этой процедуре вы перемещаете Ваниду из ее текущего узла /1/1/, , чтобы она подчинялась Джилл. Ее новым узлом станет /3/1/, поэтому /1/ — первый аргумент, а /3/ — второй. Они соответствуют значениям OrgNode Сарии и Джилл. Выполните следующий код, чтобы переместить Ваниду из организации Сарии в организацию Джилл:

     DECLARE @CurrentEmployee иерархический идентификатор, @OldParent иерархический идентификатор, @NewParent иерархический идентификатор
    ВЫБЕРИТЕ @CurrentEmployee = OrgNode FROM HumanResources.EmployeeOrg
      ГДЕ Сотрудник ID = 269 ;
    ВЫБЕРИТЕ @OldParent = OrgNode FROM HumanResources.EmployeeOrg
      ГДЕ Сотрудник ID = 46 ;
    ВЫБЕРИТЕ @NewParent = OrgNode FROM HumanResources. EmployeeOrg
      ГДЕ Сотрудник ID = 119 ;
    ОБНОВЛЕНИЕ HumanResources.EmployeeOrg
    SET OrgNode = @CurrentEmployee. GetReparentedValue(@OldParent, @NewParent)
    ГДЕ OrgNode = @CurrentEmployee ;
    ИДТИ
     
  2. Выполните следующий код, чтобы увидеть результат:

     ВЫБРАТЬ OrgNode.ToString() AS Text_OrgNode,
    OrgNode, OrgLevel, EmployeeID, EmpName, Должность
    ОТ HumanResources.EmployeeOrg ;
    ИДТИ
     

    Теперь Ванида находится в узле /3/1/.

Реорганизация раздела иерархии

  1. Чтобы продемонстрировать, как перемещать большее количество людей одновременно, сначала выполните следующий код, чтобы добавить стажера, отчитывающегося перед Wanida:

     EXEC AddEmp 269, 291, «Кевин», «Стажер по маркетингу»;
    ИДТИ
     
  2. Теперь Кевин подчиняется Ваниде, которая подчиняется Джилл, которая подчиняется Дэвиду. Это означает, что Кевин находится на уровне /3/1/1/. Чтобы переместить всех подчиненных Джилл к новому менеджеру, мы обновим все узлы, у которых /3/ в качестве их OrgNode , до нового значения. Выполните следующий код, чтобы обновить Ваниду, чтобы она отчитывалась перед Сарией, но чтобы Кевин отчитывался перед Ванидой:

     DECLARE @OldParent иерархический идентификатор, @NewParent иерархический идентификатор
    ВЫБЕРИТЕ @OldParent = OrgNode FROM HumanResources.EmployeeOrg
    ГДЕ Сотрудник ID = 119; -- Джилл
    ВЫБЕРИТЕ @NewParent = OrgNode FROM HumanResources.EmployeeOrg
    ГДЕ Сотрудник ID = 46 ; -- Сария
    ОБЪЯВИТЬ children_cursor КУРСОР ДЛЯ
    ВЫБЕРИТЕ OrgNode ИЗ HumanResources.EmployeeOrg
    ГДЕ OrgNode.GetAncestor(1) = @OldParent;
    DECLARE @ChildId иерархический идентификатор;
    ОТКРЫТЬ
    FETCH NEXT FROM children_cursor INTO @ChildId;
    ПОКА @@FETCH_STATUS = 0
    НАЧИНАТЬ
    НАЧАЛО:
        DECLARE @NewId иерархический идентификатор;
        SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
        ОТ HumanResources.EmployeeOrg, ГДЕ OrgNode.GetAncestor(1) = @NewParent;
        ОБНОВЛЕНИЕ HumanResources.EmployeeOrg
        SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
        ГДЕ OrgNode.IsDescendantOf(@ChildId) = 1;
        IF @@error <> 0 GOTO START -- Повторить попытку при ошибке
            FETCH NEXT FROM children_cursor INTO @ChildId;
    КОНЕЦ
    ЗАКРЫТЬ
    DEALLOCATE children_cursor;
     
  3. Выполните следующий код, чтобы увидеть результат:

     ВЫБРАТЬ OrgNode. ToString() AS Text_OrgNode,
    OrgNode, OrgLevel, EmployeeID, EmpName, Должность
    ОТ HumanResources.EmployeeOrg ;
    ИДТИ
     

Вот набор результатов.

 Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Заголовок
------------ ------- -------- ---------- ------- ------ -----------
/ Ox 0 6 Дэвид Менеджер по маркетингу
/1/ 0x58 1 46 Сария Специалист по маркетингу
/1/1/ 0x5AC0 2 269Помощник по маркетингу Ваниды
/1/1/1/ 0x5AD0 3 291 Кевин Стажер по маркетингу
/2/ 0x68 1 271 Джон Специалист по маркетингу
/2/1/ 0x6AC0 2 272 Мэри Ассистент по маркетингу
/3/ 0x78 1 119 Джилл Специалист по маркетингу
 

Все организационное древо, которое подчинялось Джилл (и Ванида, и Кевин), теперь подчиняется Сарии.

Сведения о хранимой процедуре реорганизации раздела иерархии см. в разделе «Перемещение поддеревьев» документа Перемещение поддеревьев.

Импорт таблицы SQL из Excel

Как импортировать электронную таблицу Excel в таблицу базы данных SQL Server с помощью мастера импорта и экспорта SQL Server

Создайте новую таблицу базы данных SQL из электронной таблицы Excel за девять шагов.

Вы можете легко импортировать таблицу Microsoft SQL Server из электронной таблицы Excel,
с помощью мастера импорта и экспорта SQL Server . (Вы также можете использовать Импорт
и мастер экспорта в
экспортировать данные из таблицы SQL Server в электронную таблицу Excel.)

Вы можете использовать мастер в версиях SQL Server Standard, Enterprise, Developer или Evaluation.
издания.

1. Введите данные в электронную таблицу Excel

  • Сначала введите данные в электронную таблицу Excel.
  • В этом примере сохранено имя электронной таблицы по умолчанию, Sheet1 ,
    но если вы переименуете лист (на имя, которое вы хотите для таблицы в базе данных,
    например), это имя будет автоматически использоваться в процессе импорта (на шаге
    6).

Небольшая демонстрационная электронная таблица Excel, готовая к переносу в базу данных SQL

2.

Запустите Мастер импорта и экспорта SQL

  • Далее в Windows запустите Мастер импорта и экспорта по адресу Пуск/Все
    Программы / Microsoft SQL Server 2008/ Импорт и экспорт данных.
  • Появится страница приветствия . Нажмите Далее .

Мастер импорта и экспорта SQL в меню «Пуск» Windows

3. Выберите электронную таблицу Excel в качестве источника данных

  • В раскрывающемся списке Источник данных на странице Выберите источник данных выберите
    Microsoft Excel .
  • В поле Путь к файлу Excel укажите путь к файлу электронной таблицы Excel.
  • Выберите версию в раскрывающемся списке Версия Excel .
  • Убедитесь, что Первая строка имеет имена столбцов .
  • Щелкните Далее .

4.

Выберите базу данных SQL в качестве места назначения

  • . В раскрывающемся списке Назначение примите настройку по умолчанию SQL Server.
    Собственный клиент 10.0
    .
  • В раскрывающемся списке Имя сервера введите имя сервера. Пример
    является удаленным сервером, поэтому были указаны IP-адрес и порт сервера.
  • Выберите тип аутентификации . Примером является удаленный сервер, поэтому SQL Server
    требуется аутентификация с использованием имени пользователя и пароля.
  • В раскрывающемся списке База данных выберите или введите имя базы данных.
  • Щелкните Далее .

5. Укажите способ копирования данных

  • Параметр по умолчанию, Копировать данные из одной или нескольких таблиц или представлений, работает для
    этот пример.
  • Если вы хотите попробовать второй вариант, Напишите запрос, чтобы указать данные для передачи ,
    следующий фрагмент лолкода может быть поучительным:

МОЖЕТ ЕСТЬ SQL?

DBASE ИЗ GETDB(‘db/demo. db’)

FUNNAHS IZ DBUCKET(&DBASE&,»МОГУ Я ПОЖАЛУЙСТА ПОЛУЧИТЬ * ВСЕ В ЛОЛИТЕ»)

IM IN UR FUNNAHS ITZA TITLE

ВОТЕЗ ИЗ &TITLE#ups& — &TITLE#downs&

  • Нажмите Далее .

6. Выберите исходные таблицы и представления

  • Настройки по умолчанию, как показано, работают в этом примере.
  • В столбце Destination можно указать другое имя для таблицы
    в базе данных SQL, если вы выберете.
  • Щелкните Предварительный просмотр , чтобы увидеть, как ваши данные будут отображаться в целевой таблице.
  • Вы можете нажать Изменить сопоставления , чтобы изменить способ назначения ваших данных в месте назначения.
    table, но в этом примере это не обязательно, так как вы ввели данные
    в электронную таблицу Excel самостоятельно.
  • Щелкните Далее .

7. Запускаем «Пакет»

В нижней части страницы Run Package появляется следующее сообщение: In SQL
Server Express, Web или Workgroup, вы можете запустить пакет, который
Мастер создает, но не может сохранить. Чтобы сохранить пакеты, созданные мастером,
необходимо обновить до SQL Server Standard, Enterprise, Developer или Evaluation.

«Пакет» — это все настройки, которые вы уже настроили. В
коммерческих версий SQL Server, вы можете сохранить пакет для повторного использования, чтобы
не нужно вводить все настройки при следующем запуске мастера. В
Express (бесплатная) версия Microsoft SQL Server 2008 Management Studio ,
вы должны повторно вводить все параметры каждый раз, когда запускаете SQL Server Import and
Мастер экспорта.

  • Нажмите Далее .

8.