Как заполнить таблицу в sql server

Основы T-SQL. DML

Для добавления данных применяется команда INSERT , которая имеет следующий формальный синтаксис:

INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, . значениеN)

Вначале идет выражение INSERT INTO , затем в скобках можно указать список столбцов через запятую, в которые надо добавлять данные, и в конце после слова VALUES скобках перечисляют добавляемые для столбцов значения.

Например, пусть ранее была создана следующая база данных:

CREATE DATABASE productsdb; GO USE productsdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL )

Добавим в нее одну строку с помощью команды INSERT:

INSERT Products VALUES ('iPhone 7', 'Apple', 5, 52000)

После удачного выполнения в SQL Server Management Studio в поле сообщений должно появиться сообщение «1 row(s) affected»:

Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым столбцом идет Id. Но так как для него задан атрибут IDENTITY, то значение этого столбца автоматически генерируется, и его можно не указывать. Второй столбец представляет ProductName, поэтому первое значение — строка «iPhone 7» будет передано именно этому столбцу. Второе значение — строка «Apple» будет передана третьему столбцу Manufacturer и так далее. То есть значения передаются столбцам следующим образом:

• ProductName: ‘iPhone 7’
• Manufacturer: ‘Apple’
• ProductCount: 5
• Price: 52000

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

INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ('iPhone 6S', 41000, 'Apple')

Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:

• ProductName: ‘iPhone 6S’
• Manufacturer: ‘Apple’
• Price: 41000

Для неуказанных столбцов (в данном случае ProductCount) будет добавляться значение по умолчанию, если задан атрибут DEFAULT, или значение NULL. При этом неуказанные столбцы должны допускать значение NULL или иметь атрибут DEFAULT.

Также мы можем добавить сразу несколько строк:

INSERT INTO Products VALUES ('iPhone 6', 'Apple', 3, 36000), ('Galaxy S8', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000)

В данном случае в таблицу будут добавлены три строки.

Также при добавлении мы можем указать, чтобы для столбца использовалось значение по умолчанию с помощью ключевого слова DEFAULT или значение NULL:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)

В данном случае для столбца ProductCount будет использовано значение по умолчанию (если оно установлено, если его нет — то NULL).

Если все столбцы имеют атрибут DEFAULT, определяющий значение по умолчанию, или допускают значение NULL, то можно для всех столбцов вставить значения по умолчанию:

INSERT INTO Products DEFAULT VALUES

Но если брать таблицу Products, то подобная команда завершится с ошибкой, так как несколько полей не имеют атрибута DEFAULT и при этом не допускают значение NULL.

Создание таблиц (ядро СУБД)

Вы можете создать новую таблицу, присвоить ей имя и добавить ее в существующую базу данных с помощью конструктора таблиц в SQL Server Management Studio (SSMS) или Transact-SQL.

Разрешения

Эта задача требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.

Если какие-либо столбцы в инструкции CREATE TABLE определены как принадлежащие к определяемому пользователем типу данных CLR, необходимо быть владельцем данного типа либо иметь разрешение REFERENCES на него.

Если какие-либо столбцы в инструкции CREATE TABLE имеют связанную коллекцию схем XML, необходимо быть владельцем этого набора схем или иметь разрешение REFERENCES на него.

Использование конструктора таблиц в SQL Server Management Studio

1. В SSMS в обозревателе объектовподключитесь к экземпляру Компонент Database Engine , который содержит изменяемую базу данных.
2. В обозревателе объектовразверните узел Базы данных , а затем базу данных, в которой будет размещена новая таблица.
3. В обозреватель объектов щелкните правой кнопкой мыши узел Таблицы базы данных и выберите Создать таблицу.
4. Введите имена столбцов, выберите типы данных и определите для каждого столбца, могут ли в нем присутствовать значения NULL, как показано на следующей иллюстрации:
5. Чтобы указать дополнительные свойства столбца, например идентификатор или вычисляемые значения столбца, выберите столбец и на вкладке свойства столбца выберите соответствующие свойства. Дополнительные сведения о свойствах столбца см. в разделе Свойства столбца таблицы (SQL Server Management Studio).
6. Чтобы указать, что столбец является столбцом первичного ключа, щелкните его правой кнопкой мыши и выберите Задать первичный ключ. Дополнительные сведения см. в статье Create Primary Keys.
7. Чтобы создать связи по внешнему ключу, проверочные ограничения или индексы, щелкните правой кнопкой мыши панель конструктора таблиц и выберите в списке объект, как показано на следующей иллюстрации: Дополнительные сведения об этих объектах см. в разделах Create Foreign Key Relationships, Create Check Constraints и Indexes.
8. По умолчанию таблица содержится в схеме dbo . Чтобы указать другую схему для таблицы, щелкните правой кнопкой мыши панель конструктора таблиц и выберите Свойства , как показано на следующей иллюстрации. Выберите нужную схему из раскрывающегося списка Схема . Дополнительные сведения о схемах см. в разделе Create a Database Schema.
9. В меню Файл выберите Сохранитьимя таблицы.
10. В диалоговом окне Выбор имени введите имя таблицы и нажмите кнопку ОК.
11. Чтобы просмотреть новую таблицу, в обозревателе объектовразверните узел Таблицы , а затем нажмите клавишу F5 , чтобы обновить список объектов. Новая таблица будет отображена в списке таблиц.

Использование Transact-SQL

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.
2. На стандартной панели выберите пункт Создать запрос.
3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.

CREATE TABLE dbo.PurchaseOrderDetail ( PurchaseOrderID INT NOT NULL, LineNumber SMALLINT NOT NULL, ProductID INT NULL, UnitPrice MONEY NULL, OrderQty SMALLINT NULL, ReceivedQty FLOAT NULL, RejectedQty FLOAT NULL, DueDate DATETIME NULL ); 

Следующие шаги

Предложение SELECT . INTO (Transact-SQL)

Инструкция SELECT…INTO создает новую таблицу в файловой группе по умолчанию и вставляет в нее результирующие строки из запроса. Полный синтаксис SELECT см. в разделе SELECT (Transact-SQL).

Синтаксис

[ INTO new_table ] [ ON filegroup ] 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

new_table
Указывает имя новой таблицы, создаваемой на основе столбцов, указанных в списке выбора, и строк, выбираемых из источника данных.

Формат аргумента new_table определяется путем расчета выражений, указанных в списке выбора. Столбцы в таблице, указанной в аргументе new_table, создаются в порядке, соответствующем списку выбора. Все столбцы таблицы, указанной в аргументе new_table, получают такие же имена, значения, типы данных и свойства допустимости значений NULL, которые указаны в соответствующем выражении в списке выбора. Свойство IDENTITY столбца переносится за исключением случаев, когда наступают условия, описанные в подразделе «Примечания» раздела «Работа со столбцами идентификаторов».

Чтобы создать таблицу в другой базе данных в том же экземпляре SQL Server, укажите new_table в качестве полного имени в форме database.schema.table_name.

new_table нельзя создать на удаленном сервере, однако new_table можно заполнить из удаленного источника данных. Для создания таблицы new_table из удаленного источника таблицы определите источник таблицы, используя четырехчастное имя в форме linked_server.catalog.schema.object в предложении FROM инструкции SELECT. Для указания удаленного источника данных также можно использовать функцию OPENQUERY или функцию OPENDATASOURCE в предложении FROM.

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

Область применения: SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) и более поздних версий.

Типы данных

Атрибут FILESTREAM не переносится в новую таблицу. Объекты BLOB FILESTREAM копируются и хранятся в новой таблице как объекты BLOB типа varbinary(max). Без атрибута FILESTREAM тип данных varbinary(max) имеет ограничение в 2 ГБ. Если размер большого двоичного объекта FILESTREAM превышает это значение, происходит ошибка 7119 и инструкция прекращает работу.

При выборе существующего столбца идентификаторов в новой таблице новый столбец наследует свойство IDENTITY, если не выполняется ни одно из следующих условий.

• Инструкция SELECT содержит соединение.
• несколько инструкций SELECT соединены при помощи UNION;
• столбец идентификаторов встречается более чем один раз в списке выбора;
• столбец идентификаторов является частью выражения;
• столбец идентификаторов получен из удаленного источника данных.

Если любое из этих условий выполняется, столбец создается как NOT NULL и не наследует свойство IDENTITY. Если в новой таблице необходим столбец идентификаторов, но такой столбец недоступен или необходимо изменить начальное значение или шаг приращения по сравнению с исходным столбцом идентификаторов, определите столбец в списке выбора с помощью функции IDENTITY. См. подраздел «Создание столбца идентификаторов с помощью функции IDENTITY» далее в разделе «Примеры».

Замечания

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

Хранилище в Microsoft Fabric не поддерживает файловые группы. Ссылки и примеры в этой статье для файловых групп не применяются к хранилищу в Microsoft Fabric.

Ограничения

В качестве новой таблицы нельзя указывать табличную переменную или возвращающий табличное значение параметр.

Инструкцию SELECT. INTO нельзя использовать для создания секционированной таблицы, даже если исходная таблица является секционированной. Инструкция SELECT. INTO не использует схему секционирования исходной таблицы. Вместо этого новая таблица создается в файловой группе по умолчанию. Для вставки строк в секционированную таблицу необходимо сначала создать секционированную таблицу, а затем использовать инструкцию INSERT INTO. SELECT. FROM .

Индексы, ограничения и триггеры, определенные в исходной таблице, не переносятся в новую таблицу, их также нельзя указывать в инструкции SELECT. INTO . Если эти объекты нужны для дальнейшей работы, их можно создать после выполнения инструкции SELECT. INTO .

Указание предложения ORDER BY не гарантирует, что строки будут вставлены в указанном порядке.

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

Если в список выбора входит вычисляемый столбец, соответствующий столбец новой таблицы не будет вычисляемым. Значениями нового столбца становятся значения, вычисленные при выполнении инструкции SELECT. INTO .

Режим ведения журнала

Объем информации, записываемой в журнал для операции SELECT. INTO , зависит от модели восстановления, действующей для базы данных. В модели восстановления с неполным протоколированием и в простой модели массовые операции минимально протоколируются. При минимальном ведении журнала использование инструкции SELECT. INTO может оказаться более эффективным, чем создание таблицы и заполнение ее инструкцией INSERT. Дополнительные сведения см. в статье Журнал транзакций (SQL Server).

Инструкции SELECT. INTO , содержащие определяемые пользователем функции (UDF), являются полностью протоколируемыми операциями. Если определяемые пользователем функции, используемые в инструкции SELECT. INTO , не выполняют никакие операции доступа к данным, для таких функций можно указать предложение SCHEMABINDING, которое будет устанавливать для производного свойства UserDataAccess значение 0. После этого изменения инструкции SELECT. INTO будут протоколироваться на минимальном уровне. Если инструкция SELECT. INTO ссылается хотя бы на одну определяемую пользователем функцию, для которой это свойство имеет значение 1, операция полностью протоколируется.

Разрешения

Требуется разрешение CREATE TABLE в целевой базе данных.

Примеры

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

В следующем примере таблица создается dbo.EmployeeAddresses в базе данных AdventureWorks2022, выбрав семь столбцов из различных таблиц, связанных с сотрудниками и адресами.

SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode INTO dbo.EmployeeAddresses FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID; GO 

B. Вставка строк с применением минимального протоколирования

В следующем примере создается таблица dbo.NewProducts , а затем вставляются строки из таблицы Production.Product . В примере предполагается, что для модели восстановления базы данных AdventureWorks2022 задано значение FULL. Чтобы обеспечить использование минимального ведения журнала, модель восстановления базы данных AdventureWorks2022 имеет значение BULK_LOGGED до вставки строк и сброса до full после выбора. Оператор INTO. Эта процедура обеспечивает минимальное использование журнала транзакций инструкцией SELECT. INTO и ее эффективное выполнение.

ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO 

C. Создание столбца идентификаторов с помощью функции IDENTITY

В следующем примере функция IDENTITY используется для создания столбца удостоверений в новой таблице Person.USAddress в базе данных AdventureWorks2022. Это необходимо, поскольку инструкция SELECT, которая определяет таблицу, содержит соединение, и в результате свойство IDENTITY не переносится в новую таблицу. Обратите внимание, что начальное значение и шаг приращения, заданные в функции IDENTITY, отличаются от значений в столбце AddressID исходной таблицы Person.Address .

-- Determine the IDENTITY status of the source column AddressID. SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value FROM sys.identity_columns WHERE name = 'AddressID'; -- Create a new table with columns from the existing table Person.Address. -- A new IDENTITY column is created by using the IDENTITY function. SELECT IDENTITY (int, 100, 5) AS AddressID, a.AddressLine1, a.City, b.Name AS State, a.PostalCode INTO Person.USAddress FROM Person.Address AS a INNER JOIN Person.StateProvince AS b ON a.StateProvinceID = b.StateProvinceID WHERE b.CountryRegionCode = N'US'; -- Verify the IDENTITY status of the AddressID columns in both tables. SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value FROM sys.identity_columns WHERE name = 'AddressID'; 

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

В следующем примере показаны три метода создания новой таблицы на локальном сервере из удаленного источника данных. Пример начинается с создания ссылки на удаленный источник данных. Затем задается имя связанного сервера ( MyLinkServer, ) в предложении FROM первой инструкции SELECT. INTO и в функции OPENQUERY второй инструкции SELECT. INTO. В третьей инструкции SELECT. INTO используется функция OPENDATASOURCE, которая непосредственно задает удаленный источник данных, не указывая имя связанного сервера.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

USE master; GO -- Create a link to the remote data source. -- Specify a valid server name for @datasrc as 'server_name' -- or 'server_name\instance_name'. EXEC sp_addlinkedserver @server = N'MyLinkServer', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'server_name', @catalog = N'AdventureWorks2022'; GO USE AdventureWorks2022; GO -- Specify the remote data source in the FROM clause using a four-part name -- in the form linked_server.catalog.schema.object. SELECT DepartmentID, Name, GroupName, ModifiedDate INTO dbo.Departments FROM MyLinkServer.AdventureWorks2022.HumanResources.Department GO -- Use the OPENQUERY function to access the remote data source. SELECT DepartmentID, Name, GroupName, ModifiedDate INTO dbo.DepartmentsUsingOpenQuery FROM OPENQUERY(MyLinkServer, 'SELECT * FROM AdventureWorks2022.HumanResources.Department'); GO -- Use the OPENDATASOURCE function to specify the remote data source. -- Specify a valid server name for Data Source using the format -- server_name or server_name\instance_name. SELECT DepartmentID, Name, GroupName, ModifiedDate INTO dbo.DepartmentsUsingOpenDataSource FROM OPENDATASOURCE('SQLNCLI', 'Data Source=server_name;Integrated Security=SSPI') .AdventureWorks2022.HumanResources.Department; GO 

Д. Импорт из внешней таблицы, созданной с помощью PolyBase

Вы можете импортировать данные из Hadoop или службы хранилища Azure в SQL Server для постоянного хранения. Чтобы импортировать данные, на которые ссылается внешняя таблица, следует использовать SELECT INTO . Оперативно создайте реляционную таблицу, а затем индекс хранилища столбца на основе таблицы, описанной на втором шаге.

Область применения: SQL Server.

-- Import data for car drivers into SQL Server to do more in-depth analysis. SELECT DISTINCT Insured_Customers.FirstName, Insured_Customers.LastName, Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus INTO Fast_Customers from Insured_Customers INNER JOIN ( SELECT * FROM CarSensor_Data where Speed > 35 ) AS SensorD ON Insured_Customers.CustomerKey = SensorD.CustomerKey ORDER BY YearlyIncome; 

F. Копирование данных из одной таблицы в другую и создание новой таблицы в указанной файловой группе

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

Область применения: SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) и более поздних версий.

ALTER DATABASE [AdventureWorksDW2022] ADD FILEGROUP FG2; ALTER DATABASE [AdventureWorksDW2022] ADD FILE ( NAME='FG2_Data', FILENAME = '/var/opt/mssql/data/AdventureWorksDW2022_Data1.mdf' ) TO FILEGROUP FG2; GO SELECT * INTO [dbo].[FactResellerSalesXL] ON FG2 FROM [dbo].[FactResellerSales]; 

Создание таблиц в MS SQL Server. CREATE TABLE

В статье пойдёт речь о том, как создать таблицу в СУБД SQL Server от Microsoft. Для работы будет использована графическая среда SQL Server Management Studio (SSMS). Также рассмотрим, как создавать таблицу с помощью инструкции CREATE TABLE.

Предусловия

Перед началом работы у вас должен быть установлен как сам MS SQL Server, так и SSMS. Версию сервера следует выбирать, исходя из технических характеристик своего компьютера и установленной операционной системы. В статье используется MS SQL Server 2017 Express.

Также таблицы (tables) не могут существовать без базы данных. Поэтому, прежде чем приступить к созданию таблиц, следует создать пустую БД. Это делается очень просто – достаточно запустить среду SSMS и выполнить несколько простых действий: 1) кликаем правой кнопкой мыши по контейнеру «Базы данных» и выбираем «Создать базу данных»; 2) в появившемся поле прописываем имя БД; 3) нажимаем «ОК».

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

База создана, поэтому можно подумать о её структуре. Планируется создание двух таблиц: 1. Goods – table с информацией о товарах. Будет содержать несколько столбцов: — ProductId. Представляет собой идентификатор товара. Значение не должно быть NULL. Также здесь будет первичный ключ; — Category. Это ссылка на категорию товара. Не NULL. Если товар не распределён в нужную категорию, ему присваивается категория по умолчанию («Не указана», «Не определена»); — ProductName. В этом столбце будет наименование товара. Не NULL; — Price. Речь идёт о стоимости. Если цена ещё не определена, возможен NULL. 2. Categories — вторая table. В ней будет описание категорий реализуемых товаров, представленное двумя столбцами: — CategoryId. Представляет собой идентификатор категории. Не NULL, первичный ключ; — CategoryName. Название категории, не NULL.

Для того чтобы нельзя было внести товар с несуществующей категорией, будет добавлено ограничение внешнего ключа.

Создание table в Management Studio

Когда структура ясна, можно приступать к созданию: 1) откройте контейнер «Базы данных»; 2) откройте только что созданную тестовую БД; 3) щёлкните правой кнопкой мыши по категории «Таблицы» и выберите пункт «Таблица».

В результате будет открыт специальный конструктор таблиц с тремя колонками: • имя столбца; • тип данных (подробнее о типах данных читайте здесь); • значения NULL (если да – ставим галочку).

Колонки заполняются с учётом спроектированной ранее структуры для table Categories.

Далее определяем первичный ключ (primary key), щелкая по требуемому столбцу правой кнопкой мыши (у нас это CategoryId). Также выбираем пункт «Задать первичный ключ».

Теперь надо обеспечить, чтобы в столбце автоматически генерировался уникальный ID-записи. Для этого идентификатору надо задать свойство IDENTITY. Делается путём включения пункта «Спецификация идентификатора».

Теперь пользователь может сохранить table (правая кнопка мыши -> «Сохранить» либо известное сочетание «Ctrl+S»). Останется ввести название таблицы и нажать «OK».

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

А для создания FOREIGN KEY щёлкните правой кнопкой мыши в любой области конструктора и выберите пункт «Отношения…».

Далее жмём кнопку «Добавить».

Теперь зададим спецификацию таблиц и столбцов:

В открывшемся окне указываем: • таблицу первичного ключа – Categories; • таблицу внешнего ключа – это текущая таблица, которая пока не создана, поэтому отображается в качестве Table_1. Тут следует выбирать столбец Category из текущей таблицы — именно он и станет выполнять роль внешнего ключа (сопоставление будет CategoryId = Category); • имя связи — название ограничения. Можно написать FK_Category.

Останется определить правила обновления и удаления. Правило обновления оставляем как есть (изменять идентификатор — не лучшая идея). А чтобы при удалении категории всем товарам присваивалось дефолтное значение, определим правило удаления как «Присвоить значение по умолчанию».

Останется сохранить созданную (created) таблицу, назвав её Goods. Может появиться предупреждающее сообщение — оно не должно вас смутить.

Созданные таблицы станут видны в обозревателе объектов.

Теперь с ними можно работать и добавлять данные, используя инструкцию INSERT.

Инструкция CREATE TABLE

Создать новую таблицу можно и с помощью инструкции CREATE TABLE. CREATE TABLE является ключевым словом, которое сообщает СУБД, что именно вы хотите выполнить. Синтаксис создания в языке (language) SQL следующий:

Как видите, после оператора CREATE TABLE надо указать имя либо идентификатор таблицы, потом список столбцов и тип данных для каждого столбца, а также первичный ключ.

Для примера создадим table CUSTOMERS. В качестве первичного ключа укажем столбец ID. Также установим ограничения для полей, которые не могут иметь нулевое значение при создании записей (NOT NULL):

Увидеть структуру созданной table можно, если сделать соответствующий запрос (query) командой DESC: