Как загрузить файлы в таблицу sql
Для импорта существующей таблицы из различных файлов баз данных следует:
- В диалоговом окне База данных | Таблицы сделать щелчок правой кнопкой мыши и выбрать пункт Импорт или в меню Таблица выбрать пункт Импорт таблицы .
- В стандартном окне выбора файла справа от строки Имя файла выбрать формат импортируемого файла: Базы данных SQLite (*.sqlite, *.sqlitedb) Базы данных Microsoft Access (*.mdb, *.accdb) Базы данных Microsoft SQL Server (*.mdf)
Рисунок 537. Импорт таблицы из базы данных
Рисунок 538. Выбор источника для записи
Рисунок 539. Выбор таблиц для импорта
выполнить загрузку файлов в таблицу FileTables
Описывает процедуру загрузки или переноса файлов в таблицы FileTable.
загрузить или перенести файлы в таблицу FileTable
Выбор метода загрузки или переноса файлов в таблицу FileTable зависит от того, где хранятся файлы в настоящее время.
Затем нужно обновить существующую таблицу метаданных, чтобы они указывали на новое расположение файлов.
Практическое руководство. Загрузка файлов в таблицу FileTable
Ниже перечислены методы, которые можно использовать для загрузки файлов в таблицу FileTable.
- Перетаскивание файлов из исходной папки в новую папку FileTable в проводнике Windows.
- Применение параметров командной строки, таких как MOVE, COPY, XCOPY или ROBOCOPY, из командной строки или пакетного файла или скрипта.
- Написание пользовательского приложения для перемещения или копирования файлов в C# или Visual Basic.NET. Вызов методов из пространства имен System.IO.
Пример. Перенос файлов из файловой системы в таблицу FileTable
В этом сценарии файлы хранятся в файловой системе, а в SQL Server есть таблица метаданных, содержащая указатели на файлы. Необходимо переместить файлы в таблицу FileTable, затем заменить исходный путь UNC для каждого файла в метаданных на путь UNC таблицы FileTable. Функция GetPathLocator (Transact-SQL) помогает достичь этой цели.
Например, предположим, что в базе данных имеется таблица PhotoMetadata, содержащая данные о фотографиях. В этой таблице также имеется столбец UNCPath типа varchar(512), содержащий фактический UNC-путь к JPG-файлу.
Чтобы перенести файлы изображений из файловой системы в таблицу FileTable, нужно выполнить указанные ниже действия.
- Создайте новую таблицу FileTable для хранения файлов. В этом примере используется имя таблицы dbo.PhotoTable, но не показан код для создания самой таблицы.
- Для копирования JPG-файлов с их структурой каталогов в корневой каталог таблицы FileTable можно использовать программу xcopy или аналогичное средство.
- Исправьте метаданные в таблице PhotoMetadata с помощью кода, похожего на следующий:
-- Add a path locator column to the PhotoMetadata table. ALTER TABLE PhotoMetadata ADD pathlocator hierarchyid; -- Get the root path of the Photo directory on the File Server. DECLARE @UNCPathRoot varchar(100) = '\\RemoteShare\Photographs'; -- Get the root path of the FileTable. DECLARE @FileTableRoot varchar(1000); SELECT @FileTableRoot = FileTableRootPath('dbo.PhotoTable'); -- Update the PhotoMetadata table. -- Replace the File Server UNC path with the FileTable path. UPDATE PhotoMetadata SET UNCPath = REPLACE(UNCPath, @UNCPathRoot, @FileTableRoot); -- Update the pathlocator column to contain the pathlocator IDs from the FileTable. UPDATE PhotoMetadata SET pathlocator = GetPathLocator(UNCPath); массовая загрузка файлов в таблицу FileTable
FileTable ведет себя как обычная таблица для массовых операций с указанными ниже квалификациями.
Таблица FileTable имеет системные ограничения, гарантирующие целостность пространства имен файлов и каталогов. Эти ограничения должны быть проверены на массовых данных, загружаемых в FileTable. Так как часть операций массовой вставки разрешает игнорировать табличные ограничения, следующие меры применяются принудительно.
- В настоящее время операции массовой загрузки в таблицу FileTable, принудительно применяющие ограничения, можно выполнять, как с любой другой таблицей. В эту категорию входят следующие операции:
- bcp с предложением CHECK_CONSTRAINTS;
- BULK INSERT с предложением CHECK_CONSTRAINTS;
- ВСТАВКА В . SELECT * FROM OPENROWSET(BULK . ) без предложения IGNORE_CONSTRAINTS.
- bcp без предложения CHECK_CONSTRAINTS;
- BULK INSERT без предложения CHECK_CONSTRAINTS;
- ВСТАВКА В . SELECT * FROM OPENROWSET(BULK . ) с предложением IGNORE_CONSTRAINTS.
Практическое руководство. Массовая загрузка файлов в таблицу FileTable
Для массовой загрузки файлов в таблицу FileTable можно использовать различные способы.
- bcp
- Вызвать с предложением CHECK_CONSTRAINTS .
- Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
- Вызвать с предложением CHECK_CONSTRAINTS .
- Отключить пространство имен FileTable и вызвать без предложения CHECK_CONSTRAINTS . Затем снова включить пространство имен FileTable.
- Вызвать с предложением IGNORE_CONSTRAINTS .
- Отключить пространство имен FileTable и выполнить вызов без предложения IGNORE_CONSTRAINTS . Затем снова включить пространство имен FileTable.
Сведения об отключении ограничений FileTable см. в разделе Управление таблицами FileTable.
Практическое руководство. Отключение ограничений FileTable для массовой загрузки
Для массовой загрузки файлов в таблицу FileTable без издержек по применению определенных в системе ограничений, можно временно отключить ограничения. Дополнительные сведения см. в статье Управление таблицами FileTable.
Основы 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.
Импорт CSV в SQL Server
CSV представляет собой компактный текстовый формат для хранения табличных данных. С файлами в формате CSV способны работать (экспортировать/импортировать данные) все современные приложения для работы с таблицами (например, Excel). Не так давно передо мной возникла задача выгрузить данные из CSV-файла в базу данных. Решение этой проблемы я приведу в данной статье.
Использование оператора BULK INSERT
Оператор BULK INSERT в SQL Server позволяет осуществлять импорт данных из файла в таблицу. В предложении WITH для данного оператора можно задавать множество опций, но нам нужны только две из них:
FIELDTERMINATOR
Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (\t). В CSV разделителем по умолчанию является запятая.
ROWTERMINATOR
Указывает разделитель для строк.
Давайте попробуем выгрузить данные для простого CSV-Файла. Этот файл содержит 10 строк для простого отчета и вы можете скачать его по следующей ссылке. Используйте следующий код:
IF (OBJECT_ID('CSV_Export') IS NOT NULL) DROP TABLE dbo.CSV_Export; CREATE TABLE CSV_Export ( DateReport VARCHAR(15), CountView INT, CountClicks INT, CTR FLOAT, RPM FLOAT, Profit FLOAT ) BULK INSERT dbo.CSV_Export FROM 'D:\Report.csv' WITH (fieldterminator = ' ', rowterminator = '\n');В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.
Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:
select top 100 * from CSV_Export;
На рисунке ниже показан результат:
Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:
В следующем примере показано, как можно обойти эту ошибку и получить дату:
-- Создаем временную таблицу и импортируем данные из CSV IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp; CREATE TABLE #csv_temp ( DateReport VARCHAR(15), CountView INT, CountClicks INT, CTR FLOAT, RPM FLOAT, Profit FLOAT ); BULK INSERT #csv_temp FROM 'D:\Report.csv' WITH (fieldterminator = ' ', rowterminator = '\n'); -- Создаем основную таблицу IF (OBJECT_ID('CSV_Export') IS NOT NULL) DROP TABLE dbo.CSV_Export; CREATE TABLE CSV_Export ( DateReport DATETIME, CountView INT, CountClicks INT, CTR FLOAT, RPM FLOAT, Profit FLOAT ) -- Заполняем данными из временной таблицы INSERT INTO CSV_Export SELECT CONVERT(datetime, DateReport, 105), -- Строку в дату CountView, CountClicks, CTR, RPM, Profit FROM #csv_temp; -- Проверить select top 100 * from CSV_Export;Теперь в основной таблице столбец DateReport будет храниться в формате даты:
Использование SQL Server Management Studio
Импортировать данные из CSV можно стандартными средствами программы SQL Server Management Studio. Для этого выполните следующие шаги:
-
В обозревателе объектов Object Explorer выберите целевую базу данных, щелкните по ней правой кнопкой мыши и выберите из контекстного меню команду Tasks —> Import Data.
На следующем рисунке отображена сгенерированная таблица report в окне Object Explorer:
Теперь выполните следующую инструкцию:
INSERT INTO CSV_Export SELECT CONVERT(datetime, DateReport, 105), -- Строку в дату CountView, CountClicks, CTR, RPM, Profit FROM report; select top 100 * from CSV_Export;
В результате мы получим результаты, аналогичные подходу с использованием кода.
- C# тест (легкий)
- .NET тест (средний)