Select insert into ms sql: SQL INSERT INTO SELECT Statement

Импорт CSV тремя простыми способами

Сегодня существуют более простые способы импорта CSV в SQL Server. Вы можете писать меньше кода или использовать графические инструменты. Вы даже можете получить CSV-файл из облачного хранилища на локальный SQL Server. Этот пост будет посвящен тому, как вы можете сделать это тремя простыми способами.

Содержание

  1. Зачем импортировать CSV в SQL Server
  2. Как импортировать файл CSV в SQL Server (3 простых способа)
  3. ОБЪЕМНАЯ ВСТАВКА
  4. SQL Server Management Studio Инструменты импорта CSV
  5. Инструменты ETL — облачное решение Skyvia
  6. Заключение

Зачем импортировать CSV в SQL Server

Я читал о значениях, разделенных запятыми, или CSV, в Википедии. Можете ли вы поверить, что CSV поддерживался еще в 1972 году? По какой причине?

Для обмена данными между 2 и более системами разных платформ и архитектур. Сегодня причина все та же. Представьте, что у вас есть 2 проприетарные системы. Один из них использует проприетарную базу данных NoSQL, такую ​​как у PayPal. А другой использует SQL Server. Это как японец разговаривает с эстонцем. Понять почти невозможно. Каково решение, спросите вы?

Экспорт файла CSV из базы данных NoSQL. Затем импортируйте файл CSV в SQL Server. Вместо того, чтобы изобретать новое программное обеспечение, обменяйтесь зрелым и распространенным форматом файлов. Это будет проще и займет меньше времени.

Вот распространенные сценарии, когда CSV может быть полезен:

  • Клиенты платят обслуживающей компании через банк. Затем банк предоставляет платежные записи обслуживающей компании с помощью файла CSV.
  • Операционная система, созданная собственными силами, должна быть интегрирована в систему ERP.
  • Несколько списков SharePoint необходимо синхронизировать с базой данных SQL Server для анализа данных.
  • Биометрическая система должна быть интегрирована в систему управления персоналом для контроля посещаемости.

Хотя это можно сделать с помощью JSON или XML, CSV проще создать из исходного кода. Если требования к данным не являются иерархическими, CSV может подойти. А как насчет целевой системы, использующей SQL Server? Рейтинг Gartner Peer Insights составляет 4,5 из 5 возможных. SQL Server в облаке или Azure SQL также входит в тройку лучших СУБД 2020 года. Таким образом, обучение импорту CSV в SQL Server стоит затраченных усилий. Вы спросите, как это сделать. Ответ подробно описан ниже.

Как импортировать файл CSV в SQL Server (3 простых способа)

Прежде чем мы начнем, нам нужен образец CSV и целевая таблица в SQL Server. Проверьте это на скриншоте ниже.

Это будет использоваться для загрузки в SQL Server с использованием 3 различных способов импорта CSV. Итак, загрузите копию файла Actor.csv отсюда. Вспомните, где вы его сохранили. Он понадобится вам позже в этом уроке.

Между тем, целевая таблица имеет такую ​​же структуру в SQL Server.

В этом руководстве я использую имя сервера MS-SQLSERVER. И имя базы данных CSV-MSSQL-TEST.

Использование BULK INSERT

BULK INSERT — это команда SQL Server для импорта файлов данных в таблицу базы данных. Его можно использовать для загрузки файлов различных форматов, в том числе CSV. Если вам нравится немного программировать, это путь. Вот код массовой вставки CSV в SQL Server:

 -- сначала усеките таблицу
TRUNCATE TABLE dbo.Actors;
ИДТИ
 
-- импортируем файл
МАССОВАЯ ВСТАВКА dbo.Actors
ИЗ 'C:\Documents\Skyvia\csv-to-mssql\actor.csv'
С
(
        ФОРМАТ='CSV',
        ПЕРВЫЙ УРОВЕНЬ=2
)
ВПЕРЕД

Первая команда просто удаляет все записи в целевой таблице. Затем команда BULK INSERT включает целевую таблицу и файл CSV. Расположение файла CSV должно соответствовать правилам универсального соглашения об именах (UNC). Вы также должны указать SQL Server, с каким файлом он имеет дело. В этом случае FORMAT=CSV, поскольку файл имеет формат CSV. Наконец, укажите, с какой строки начинаются данные. FIRSTROW = 2, так как первая строка содержит имена столбцов.

Вы можете выполнить команду BULK INSERT из SQL Server Management Studio или любого другого инструмента SQL Server. Другой способ сделать это — в PowerShell.

ЗА И ПРОТИВ ИСПОЛЬЗОВАНИЯ BULK INSERT

Плюсы:

  • Если вы знаете синтаксис, ввод текста может быть быстрее, чем при использовании интерфейса GUI;
  • Нет необходимости анализировать данные. BULK INSERT сделает это за вас. Если в целевом столбце используется тип данных, слишком маленький для данных, произойдет ошибка;
  • Возможно планирование выполнения в агенте SQL Server;
  • Также отлично подходит для одноразового импорта.

Минусы:

  • Вы не можете указать CSV из облачного хранилища, такого как Google Drive или OneDrive;
  • Разрешает только SQL Server в качестве целевой базы данных;
  • Требуется технический специалист для кодирования, запуска и мониторинга.

Использование SQL Server Management Studio Import CSV Tools

Если вы не предпочитаете кодирование, другим полезным инструментом является Import Data из SQL Server Management Studio (SSMS). Вот как импортировать CSV в таблицу MSSQL с помощью SSMS.

Шаг 1. В обозревателе объектов разверните папку баз данных

Во-первых, вам нужно перейти в обозреватель объектов и выбрать целевую базу данных. Разверните папку Базы данных. Проверьте это ниже.

Шаг 2. Выберите целевую базу данных

В этом примере целевой базой данных является CSV-MSSQL-TEST. Щелкните правой кнопкой мыши эту базу данных и выберите  Tasks . Затем нажмите  Импорт данных .

Появится окно мастера импорта и экспорта SQL Server с экраном приветствия. Здесь нажмите  Далее .

Шаг 3. Выберите источник плоского файла

Эта часть позволит вам выбрать источник данных. Для этого щелкните раскрывающийся список и выберите Flat File Source . После этого нажмите Далее .

Шаг 4. Укажите файл CSV

Теперь вам нужно указать файл. Нажмите  Browse  и укажите путь к CSV-файлу, как показано на снимке экрана. Если вы скачали его, укажите место загрузки. Затем в типе файла выберите файлов CSV (*.csv) . А потом — acter.csv . Продолжайте, нажав Открыть .

Шаг 5. Настройка столбцов

SSMS не может определить правильные типы данных для каждого столбца? В наших выборочных данных обнаружение правильных типов и размеров отключено. Итак, нам нужно настроить его самостоятельно.

Совет для профессионалов:  Всякий раз, когда вы импортируете данные с одной платформы на другую, лучше всего сопоставлять правильные типы и размеры. Почему? Чтобы не было ошибок и головной боли.

Начнем с нажатия  Дополнительно . Затем вы увидите список столбцов. Нажмите на каждый и установите тип и размер.

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

Имя столбца DataType OutputColumnWidth
id четырехбайтовое целое число со знаком [DT_I4] 90 150

 Н/Д
фамилия строка [DT_STR] 20
имя строка [DT_STR] 20
отчество строка [DT_STR] 20
суффикс 90 150

 строка [DT_STR] 3

Шаг 6.

Выберите место назначения (SQL Server)

Теперь вам нужно указать SQL Server в качестве цели процесса импорта. Для этого выберите Microsoft OLE DB Provider for SQL Server . Затем выберите имя SQL Server и введите необходимые учетные данные. Выберите Имя базы данных и нажмите Далее .

Шаг 7. Укажите таблицу базы данных и проверьте сопоставления столбцов

После выбора базы данных вам также необходимо указать таблицу. Выберите таблицу Актеры .

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

Когда появится окно «Сопоставление столбцов», проверьте, совпадают ли столбцы из источника и цели. Вы можете выбрать удаление строк в целевой таблице или добавить строки. В нашем случае мы хотим начать очистку. Если вы пытались импортировать с помощью BULK INSERT ранее, в целевой таблице уже есть данные. Так и будет ошибка. Чтобы избежать этого, выберите Удалить строки в целевой таблице . Затем нажмите OK .

Наконец, чтобы завершить настройку целевой таблицы, нажмите Далее .

Шаг 8. При необходимости сохраните в пакете служб SSIS или запустите немедленно

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

Затем выберите Далее , чтобы просмотреть сводку выбранных вами настроек. Или нажмите Готово , чтобы запустить процесс импорта. Вы увидите ход выполнения в следующем окне, если нажмете Готово .

Вот и все для импорта CSV в SQL Server с помощью Import Data в SSMS. Вы также можете выбрать Import Flat File , но он всегда будет выгружаться в новую таблицу. И вы не можете сохранить его в пакет SSIS.

ЗА И ПРОТИВ ИСПОЛЬЗОВАНИЯ ИНСТРУМЕНТОВ ИМПОРТА SQL SERVER MANAGEMENT STUDIO

Плюсы:

  • Кодирование не требуется;
  • Если сопоставления столбцов совпадают с исходным и целевым, это просто работает;
  • Разрешает множество источников данных и мест назначения, а не только SQL Server;
  • Сохранение в каталоге SSIS и планирование возможно, но ограничено тем, что было определено;
  • Отлично подходит для одноразового импорта.

Минусы:

  • Если у вас нет спецификаций типов и размеров столбцов в CSV-файле, сопоставление столбцов будет громоздким;
  • Невозможно получить CSV-файл с Google Диска, OneDrive или аналогичного облачного хранилища.

Использование инструментов ETL — облачное решение Skyvia

Наконец, мы будем использовать облачный инструмент ETL для импорта CSV-файла в SQL Server. Один из возможных способов импорта CSV в SQL Server — из облачного хранилища, такого как Google Диск. В этом разделе вы увидите, как использовать Skyvia для импорта CSV-файла с Google Диска на SQL Server на локальном ПК. Прежде чем вы сможете использовать его, вам нужна учетная запись в Skyvia и Google. Оба бесплатны для регистрации.

Элементы успешного импорта с использованием Skyvia следующие:

  • Агент  — вам нужен агент, чтобы разрешить Skyvia подключаться к удаленному серверу SQL.
  • Соединения — вам нужно определить 2 соединения: одно для Google Диска и другое для удаленного SQL Server.
  • Пакет — Skyvia использует пакеты для определения задач для процесса импорта (например, SSIS).

Для начала вам необходимо авторизоваться в Skyvia. Вы будете перенаправлены в рабочее пространство по умолчанию. Затем отсюда вы можете создать все 3 вещи выше. Вот как.

Шаг 1. Создайте агент

Чтобы создать агент или туннель к удаленному серверу SQL, нажмите  NEW . Затем выберите  Agent .

Когда вы окажетесь на следующей странице, назовите своего агента  Skyvia-MyPC . Затем загрузите и установите агент Skyvia. Пожалуйста, обратите внимание на то, где вы устанавливаете агент. Он понадобится вам при загрузке ключа агента. Наконец, загрузите файл ключа агента и сохраните его там, где вы устанавливаете агент Skyvia.

Теперь настройка вашего агента завершена. Но вам нужно протестировать его. Итак, запускаем установленное приложение агента Skyvia. Вот скриншот установленных файлов Skyvia Agent и Key.

Вот как это выглядит после запуска агента.

После этого вы можете проверить статус на Skyvia. Вот скриншот хорошего соединения. Это означает, что теперь можно подключиться к вашему SQL Server из Skyvia.

Шаг 2. Создайте два подключения для источника и назначения

Сначала давайте создадим подключение к Google Диску, где находится CSV. Для этого нажмите  NEW  и нажмите  Connection .

На следующей странице нужно выбрать разъем. Нажмите Google Диск .

Затем назовите подключение MyGDrive и войдите в свою учетную запись Google. Нажмите  Создать подключение  , чтобы создать подключение. Вот завершенный скриншот подключения к Google Диску.

Во-вторых, давайте создадим соединение с SQL Server.

Нажмите NEW еще раз и выберите Соединение . Обратитесь к скриншоту выше. Затем нажмите  SQL Server . Назовите свое подключение CSV-MSSQL-TEST . После этого нажмите Агент и выберите созданный ранее агент Skyvia-MyPC . Затем введите имя сервера, учетные данные и имя базы данных. Вот скриншот завершенного подключения к SQL Server.

Шаг 3. Создайте пакет Skyvia для импорта файла CSV в SQL Server

Мы почти закончили. Заключительная часть — создание пакета Skyvia. Итак, начните с нажатия NEW , а затем Import . Это создаст пакет импорта.

В открывшемся редакторе пакетов назовите свой пакет  CSV-SQL-Test  и укажите источник и цель. Наш источник — MyGDrive подключение к Google Диску. И целью является CSV-MSSQL-TEST , подключение к SQL Server.

На что еще обратить внимание:

  • Установите флажок Использовать новую среду выполнения . В противном случае подключение к SQL Server не будет отображаться в раскрывающемся списке целей;
  • Выберите  CSV из службы хранения . В противном случае подключение к Google Диску не будет выбрано;
  • Установите флажок  Сохранить заказ задачи . Это заставит задачи выполняться последовательно.

Шаг 4. Создание задач в пакете импорта

Пришло время создать задачи. Во-первых, любая существующая запись в таблице «Актеры» должна быть удалена. В разделе Задачи нажмите Добавить новый . Появится новое окно. Затем выберите файл act.csv на Google Диске. Затем сделайте Text Qualifier пустым, а Кодовая страница Западноевропейская Windows (1252) . Наконец, установите для столбца id значение DT_I4 (целое число).

Нажмите Следующий шаг , чтобы продолжить. На второй странице в разделе Операция нажмите Удалить . Это приведет к удалению записей в цели. Нажмите Следующий шаг еще раз, чтобы перейти к настройкам сопоставления. Вы увидите сопоставление между столбцом идентификатора источника и цели. Нажмите  Сохранить .

Наконец, первая задача выполнена.

Вторая и последняя задача — вставить строки файла CSV в SQL Server. Шаги почти такие же, за исключением того, что вам нужно определить все типы и размеры столбцов на основе таблицы 1 ранее. Затем нажмите Следующий шаг и выберите операцию Вставить вместо Удалить . Наконец, Сохраните задачу.

Ниже вы можете увидеть скриншот готового пакета. Нажмите Сохранить , чтобы сохранить пакет.

Шаг 5. Запустите пакет

Чтобы запустить пакет, нажмите кнопку Запустить в правом верхнем углу страницы. Затем нажмите Отслеживать , чтобы увидеть ход выполнения. См. пример скриншота ниже.

ПЛЮСЫ И ПРОТИВ ИСПОЛЬЗОВАНИЯ SKYVIA

Плюсы:

  • Множество источников и направлений данных, включая облачные хранилища;
  • Опытные специалисты по ETL легко освоятся;
  • Запланировать автоматическое выполнение пакетов;
  • Гибкое ценообразование в зависимости от текущих потребностей и использования;
  • Нет необходимости устанавливать средства разработки (за исключением случаев, когда требуется агент).
  • получил рейтинг 4,8 в рейтинге G2 Best ETL Tools и 4,9 в рейтинге Gartner Peer Insights.