Azure-docs.ru-ru/concepts-migrate-import-export.md at master · microsoftdocs/azure-docs.ru-ru

Импорт

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

Рис. 5. Импорт данных из источников данных в базу MDS

Сначала данные из источника необходимо загрузить в промежуточную область базы MDS. Преобразование данных из источника к виду таблиц промежуточной области можно осуществить с использованием скрипта T-SQL или пакета SQL Server Integration Services. Когда записи из источника загружены в промежуточную область, из интерфейса Master Data Manager необходимо запустить команду загрузки в определенную модель определенной версии.

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

Работа с дампами базы

Работа с резервными копиями базы из интерфейса программы

В интерфейсе программы доступно сохранение и загрузка дампов в двух форматах:

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

Резервная копия в sql формате

Создание и загрузка резервных копий базы данных в sql формате доступна через пункт Администрирование

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

Сохранение и загрузку дампа можно выполнять через консольную утилиту: для сохранения для загрузки

Резервная копия в виде информационной базы

Сохранение и загрузка информационно базы доступно в пункте Данные. При этом, доступен выбор складов и товаров, которые буду сохранены в дампе. Сохранение и загрузка информационной базы, не зависит от версии используемой СУБД.

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

Импорт данных из Excel документа в MSSql 2008

Создаем тестовые данные в документе Excel

Мы будем использовать старый, но проверенный Excel 2003 и формат файла у нас будет xls.

Данные будут вот такие, файл я назвал test_file.xls:

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

Примечание! Сервер располагается локально, база данных называется test.

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

Шаг 2

Нажимаем далее, где нам предлагают выбрать источник данных, в нашем случае это Excel, мы выбираем файл, версию excel и ставим галочку, что первая строка — это заголовок:

Шаг 3

Жмем далее, нам предлагают выбрать назначение, куда копировать эти данные, мы выбираем SQL Server, указываем имя сервера, т.е. его адрес, в нашем случае, как я уже сказал, он расположен локально. Также не забудьте про проверку подлинности, выбирайте тот метод, который у Вас настроен на сервере, и, конечно же, про базу данных, в которую копировать:

Снова жмем далее, где мы укажем все ли данные копировать, в нашем случае мы говорим что все:

Шаг 5

Жмем далее, и попадаем на окно выбора листа с данными и задания названия таблицы в нашей базе, я выбрал лист 1 и назвал таблицу test_table:

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

Шаг 6

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

После появится окно, где мы все проверяем и жмем готово:

Шаг 8

И в заключение у нас появится еще одно окно, так сказать результат наших действий, жмем закрыть:

Как видно, передано 3 строки, т.е. импортировано — это означает, что все наши данные, которые были в файле, импортировались.

И для того, чтобы проверить какие данные у нас импортировались, выполним в Management Studio простой запрос select.

И как видите все хорошо!

Безопасность

Для предоставления доступа к веб-приложению «Master Data Manager» в нем необходимо добавить существующие группы или пользователей, которые могут быть из домена Active Directory. В программе можно создавать доменные группы и добавлять к ним пользователей. Затем в веб-приложении «Master Data Manager» можно задать права для групп и пользователей.

Когда пользователь открывает «Master Data Manager» в веб-браузере, он автоматически входит под своей учетной записью в Active Directory.

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

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

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

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

FIELDTERMINATOR: Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (\t). В CSV разделителем по умолчанию является запятая.
ROWTERMINATOR: Указывает разделитель для строк.

В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.

Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:

На рисунке ниже показан результат:

Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:

В следующем примере показано, как можно обойти эту ошибку и получить дату:

Теперь в основной таблице столбец DateReport будет храниться в формате даты:

Access экспорт в sql

Для того чтобы перенести базу данных из Access 2016 в MS SQL Server необходимо выполнить ряд действий.

Перенос производится отдельными таблицами. Перенос связей не проверялся. Задача состоит в переносе данных на сервер MS SQL Server, для последующей обработки скриптами.

Открываем вашу базу Access. Выделяем нужную нам таблицу для переноса. (Если необходимо переносить несколько таблиц из Access, то потребуется несколько раз выполнить инструкцию). Переходим на вкладку Внешние данные, в разделе экспорт ищем Дополнительно.

Щелкаем по кнопке Дополнительно, откроется меню выбора , выбираем База данных ODBC

Откроется окно. Таблица, которую переносим из Access, называется test. В поле ввода необходимо написать название таблицы, которая будет создана на SQL Server

Откроется окно , Переходим на вкладку как на скрине

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

Жмем далее и выбираем нужный нам драйвер

Жмем далее и Готово.

Откроется настройка подключения к серверу. Заполняем поля Имя – оно будет отображаться в списке выбора (удобное для вас) и жмем на галочку для выбора сервера. Будет долго думать, потом в выпадающем списке выбираем название экземпляра. Скорее всего у вас будет один.

Жмем Далее. Выбираем проверку подлинности, такую же как на сервере. Хотя может быть и так и так.

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

Готово, делаем проверку источника данных. ТЕСТ УСПЕШЕН! Жмем везде ОК

Дальше, выбираем наш источник данных Жмем ОК

Открывается окно, вводим пароль и жмем Параметры

На этом шаге мы выбираем базу данных, в которую необходимо перенести данные (создастся новая таблица , которую мы указывали в начале — Tablenew) Жмем ОК Вылезет окно, шаги можно сохранить , а лучше не стоит.

На сервере создалась наша таблица со всеми данными.

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

Экспорт данных из Microsoft SQL Server 2008 в файл Excel

А теперь давайте рассмотрим пример экспорта данных из нашей только что созданной таблицы в Excel документ.

Шаг 1

Для этого делаем практически то же самое, открываем средство экспорта, но уже здесь в качестве источника указываем нашу базу:

Шаг 2

Жмем далее, где нам предлагают указать назначение экспорта, мы соответственно выбираем Excel, и задаем путь и название выгружаемого файла:

Шаг 3

После того как Вы нажмете далее, Вы попадете в окно выбора данных, т.е. какие именно данные мы будем выгружать, и здесь давайте укажем — выгружать данные на основе запроса. Так как, когда мы импортировали данные, мы выбрали все, а теперь для примера выберем не все, а на основе запроса, можно также выбрать все и указать таблицу или представление VIEWS, в котором уже будут отфильтрованные данные, но мы напишем SQL запрос:

Шаг 4

И в следующем окне вставляем свой запрос, например, я написал вот такой:

Также в этом окне Вы можете выполнить анализ своего запроса, на предмет наличия ошибок или выбрать файл, который содержит текст запроса, и, если все хорошо, жмем далее.

Шаг 5

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

Шаг 7

Далее, как и в импорте, жмем последний раз готово. И все, после этого у Вас в той папке, которую Вы указали, появится документ Excel с Вашими данными.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Распространенные ошибки

«Microsoft.ACE.OLEDB.12.0» не зарегистрирован

Эта ошибка возникает, так как не установлен поставщик OLE DB. Установите его через Распространяемый пакет ядра СУБД Microsoft Access 2010. Не забудьте установить 64-разрядную версию, если Windows и SQL Server — 64-разрядные.

Полный текст ошибки.

Это означает, что Microsoft OLEDB не был настроен должным образом. Чтобы устранить проблему, выполните приведенный ниже код Transact-SQL.

Полный текст ошибки.

Это происходит, когда 32-разрядная версия поставщика OLD DB устанавливается вместе с 64-разрядной версией SQL Server. Чтобы устранить эту проблему, удалите 32-разрядную версию и вместо нее установите 64-разрядную версию поставщика OLE DB.

Полный текст ошибки.

Прямые запросы

Прямые запросы для импорта можно использовать, когда таблица (или таблицы) с исходными данными доступны в текущей БД. Самый простой вариант, когда все таблицы находятся в одной БД, однако это, согласитесь, странный вариант для импорта.

Прямые запросы хорошо подходят для ситуаций, когда данные находятся на том же MSSQL-сервере. Данные можно копировать запросами и в случае необходимости трансформации данных (как правило, при достаточном знании SQL можно реализовать большинство преобразований, а для экзотики написать функции на .NET).

Чаще всего импорт с помощью прямых запросов используется при интеграции, реже при периодическом импорте для отчётности и ещё реже при первоначальном импорте (в силу того, что в этих случаях редко все данные бывают на одном MSSQL-сервере).

OPENROWSET и связанные серверы

Важно!

В базе данных SQL Azure невозможно импортировать данные непосредственно из Excel. Сначала необходимо экспортировать данные в текстовый файл (CSV). Примеры см. в разделе Пример.

Примечание

Поставщик ACE (прежнее название — поставщик Jet), который подключается к источникам данных Excel, предназначен для интерактивного клиентского использования. Если поставщик ACE используется на сервере SQL Server, особенно в автоматизированных процессах или процессах, выполняющихся параллельно, вы можете получить непредвиденные результаты.

Распределенные запросы

Импортируйте данные напрямую из файлов Excel в SQL Server с помощью функции Transact-SQL OPENROWSET или OPENDATASOURCE. Такая операция называется распределенный запрос.

Важно!

Перед выполнением распределенного запроса необходимо включить параметр ad hoc distributed queries в конфигурации сервера, как показано в примере ниже. Дополнительные сведения см. в статье ad hoc distributed queries Server Configuration Option (Параметр конфигурации сервера “ad hoc distributed queries”).

sp_configure ‘show advanced options’, 1;RECONFIGURE;GOsp_configure ‘Ad Hoc Distributed Queries’, 1;RECONFIGURE;GO

В приведенном ниже примере кода данные импортируются из листа Excel Sheet1 в новую таблицу базы данных с помощью OPENROWSET.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; Database=C:TempData.xlsx’, );GO

Ниже приведен тот же пример с OPENDATASOURCE.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENDATASOURCE(‘Microsoft.ACE.OLEDB.12.0’, ‘Data Source=C:TempData.xlsx;Extended Properties=Excel 12.0’)…;GO

Чтобы добавить импортированные данные в существующую таблицу, а не создавать новую, используйте синтаксис INSERT INTO … SELECT … FROM … вместо синтаксиса SELECT … INTO … FROM … из предыдущих примеров.

Для обращения к данным Excel без импорта используйте стандартный синтаксис SELECT … FROM ….

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

Распределенные запросы (Распределенные запросы по-прежнему поддерживаются в SQL Server 2016, но документация по этой функции не обновлена.)
OPENROWSET
OPENDATASOURCE

Связанные серверы

Кроме того, можно настроить постоянное подключение от SQL Server к файлу Excel как к связанному серверу. В примере ниже данные импортируются из листа Excel Data на существующем связанном сервере EXCELLINK в новую таблицу базы данных SQL Server с именем Data_ls.

USE ImportFromExcel;GOSELECT * INTO Data_ls FROM EXCELLINK…;GO

Вы можете создать связанный сервер в SQL Server Management Studio или запустить системную хранимую процедуру sp_addlinkedserver, как показано в примере ниже.

DECLARE @RC intDECLARE @server nvarchar(128)DECLARE @srvproduct nvarchar(128)DECLARE @provider nvarchar(128)DECLARE @datasrc nvarchar(4000)DECLARE @location nvarchar(4000)DECLARE @provstr nvarchar(4000)DECLARE @catalog nvarchar(128)– Set parameter valuesSET @server = ‘EXCELLINK’SET @srvproduct = ‘Excel’SET @provider = ‘Microsoft.ACE.OLEDB.12.0’SET @datasrc = ‘C:TempData.xlsx’SET @provstr = ‘Excel 12.0’EXEC @RC = .. @server, @srvproduct, @provider,@datasrc, @location, @provstr, @catalog

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

Создание связанных серверов
OPENQUERY

Примеры и дополнительные сведения о связанных серверах и распределенных запросах см. указанных ниже разделах.

Использование Excel со связанными серверами SQL Server и распределенными запросами
Импорт данных из Excel в SQL Server

Integration Services

Все перечисленные выше технологии предполагают написание программного кода или скриптов для импорта. SQL Server Integration Services (SSIS), конечно, не является той заветной технологией, позволяющей осуществлять весь импорт с помощью инструмента «волшебная палочка». Однако она позволяет сделать процесс более наглядным за счёт визуализации.

SSIS содержит набор компонентов для импорта, которого обычно хватает для большинства задач. Для остальных можно использовать скрипт на C# или VB.NET. В отличие от прямых запросов к БД и linked servers SSIS предлагает доступ к источникам данных, к которым затруднительно обращаться из T-SQL (например, MSMQ и Analysis Services).

Правда, прямые запросы будут работать быстрее в случае когда и источник и приёмник данных находятся на одном MSSQL-сервере (потому что в случае с SSIS данные нужно будет передать за пределы процесса). Если, конечно, в SSIS не написать выполнение тех же самых запросов.

Есть довольно много дополнительных компонентов, одни из которых относятся непосредственно к импорту (например, «Data Mining Query Task»), другие выполняют стандартные действия, относящиеся к инфраструктуре (например, «Shrink Database Task»). Далее будут рассмотрены только несколько компонентов из базового набора.

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

Разумеется, в SSIS есть поддержка BULK INSERT, так что с точки зрения быстрого импорта большого объёма данных всё в порядке. Можно использовать скрипты на .NET, поэтому варианты, реализуемые программным кодом, реализуемы и здесь.

С моей точки зрения у SSIS есть несколько недостатков, которые и стоит учитывать при выборе технологии импорта (в остальном эта технология вполне хороша):

Иногда необъяснимо медленное сохранение проекта (даже на SSD), но с этим, в принципе, можно жить.
Странная обработка ошибок. Это сложно описать, с этим просто нужно несколько раз столкнуться.
Необъяснимо корявый интерфейс для конфигурирования. Особенно на контрасте с неплохим интерфейсом для диаграмм и отслеживания выполнения.
Кривая обучения местами весьма кривая, видимо потому что пытались сделать инструмент для аналитиков, а не разработчиков и некоторые вещи запрятали подальше, посчитав слишком продвинутыми. Постараюсь далее немного исправить этот недостаток.

Export SQL Server data to an Excel file using T-SQL code

The Transact-SQL OPENROWSET can be used to export SQL Server data to an Excel file via SSMS. In a query editor type and execute the following code:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ') SELECT * FROM dbo.DimScenario

But, when executing the above code, the following error may occur:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

To resolve this error the Ad Hoc Distributed Queries option should be enabled This can be done by using the sp_configure procedure and executing the following SQL code in a query editor:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

After executing the above code, the following message will appear that indicate that the show advanced options and Ad Hoc Distributed Queries options are enabled:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Now, when again the code is executed:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ') SELECT * FROM dbo.DimScenario

The following error may appear:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)

This usually happens because of inadequate permissions.

More about how to resolving these issues can be found in the How to query Excel data using SQL Server linked servers page.

One way for resolving this issue is to open SSMS as an administrator and execute the code again. But this time, another error may appear:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

To resolved this, open excel file (e.g. SQL Data.xlsx) for which is planned to store the data from SQL Server table (e.g. dbo.DimScenario) and enter the column names which will represent the column names from the DimScenario table:

Close the SQL Data.xlsx file and once again execute the code:

Now, the following message will appear:

(3 rows affected)

Finally, the data from the SQL Server table are copied into the Excel file:

Экспорт данных из Microsoft SQL Server 2008 в файл Excel

А теперь давайте рассмотрим пример экспорта данных из нашей только что созданной таблицы в Excel документ.

Шаг 1

Шаг 2

Шаг 3

Шаг 4

И в следующем окне вставляем свой запрос, например, я написал вот такой:

Шаг 5

Шаг 7

Пока!

Нравится2Не нравится

2ответа

Есть много статей о написании кода для импорта файла Excel, но это ручная / сокращенная версия:

Если вам не нужно импортировать файл Excel программно с помощью кода, вы можете сделать это очень быстро, используя меню в SQL Management Studio.

Самый быстрый способ получить файл Excel в SQL – воспользоваться мастером импорта:

Откройте SSMS (Sql Server Management Studio) и подключитесь к базе данных, в которую вы хотите импортировать свой файл.
Импорт данных: в SSMS в обозревателе объектов в разделе “Базы данных” щелкните правой кнопкой мыши базу данных назначения, выберите ” Задачи”, “Импорт данных”. Откроется мастер импорта (обычно вы можете просто нажать “Далее” на первом экране).

В следующем окне “Выберите источник данных” выберите Excel:
- В раскрывающемся списке “Источник данных” выберите Microsoft Excel (эта опция должна появиться автоматически, если вы установили Excel).
- Нажмите кнопку “Обзор”, чтобы выбрать путь к файлу Excel, который вы хотите импортировать.
- Выберите версию файла Excel (97-2003 обычно подходит для файлов с расширением.XLS или используйте 2007 для новых файлов с расширением.XLSX).
- Установите флажок “Первая строка имеет заголовки”, если ваш файл Excel содержит заголовки.
- Нажмите кнопку “Далее.

На экране “Выберите место назначения” выберите базу данных назначения
Выберите “Имя сервера”, Аутентификация (обычно ваше имя пользователя и пароль sql) и выберите базу данных в качестве места назначения. Нажмите кнопку “Далее.

:

В окне “Указать копию таблицы или запрос“:

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