Access экспорт в sql
Конвертация файлов Access в SQL Server
Есть большие базы данных, сделанные в Ассеss, можно ли перенести их на платформу Microsoft SQL Server. Причем не просто перенести, а добавить новые поля. Или есть какие-нибудь другие варианты.
в SQL Server есть несколько утилит для импорта /экспорта данных.
Просто я не работал с SQL. Там реально можно импортировать данные?
реально.
можно все делать через export / import wizard — это совсем просто
или же через DTS (data transformation service) — в этом случае
будет чуть сложнее, но зато можно полностью контролировать поток данных
А добавить новые поля?
DTS пакете делаешь-> DTS Task -> destination-> table name-> Create new -> он сгенерит sql. по дефолту структура этой таблицы = исходной. sql скрипт поправляется (добавляются новые столбцы потом делается маппинг между source & destination
Ставишь MS SQL Server, дальше лезешь Start->Programs->Microsoft SQL Server->Import and Export Data. Это такой wizard, там указываешь, откуда брать данные. В твоём случае DataSource будет Microsoft Access, видимо. Указываешь путь к базе, логин/пароль, если надо. Там ещё есть кнопка Advanced, там настройки провайдера MS Jet, подстраиваешь их, опять таки, если нужно. На след. вкладке указываешь, куда импортировать данные, в твоём случае это MS OLE DB Provider for MS SQL Server, указываешь там свой сервер, логин/пароль, в качестве имени базы , тебя спросят, как её назвать. На след. вкладке укажи, какие данные передавать. Либо все таблицы/view, либо только результат запроса. Допустим, ты хочешь тащить всё, там будет список, где надо будет поставить галки около объектов, которые нужно перенести. Жми кнопу Select All, доводи wizard до finisha и будет тебе счастье, т. е миграция данных из Access в SQL Server. Все подробности работы Data Transformation Services в Books Online (это help к MS SQL).
Упс, окажывается, тут уже всё рассказали. 
Не сочтите идиотом, , но могу ли я добавить новые поля дополнительно к существующим при конвертации.
могу ли я добавить новые поля дополнительно к существующим при конвертации.
Да. И уже сказал, как это делается. Та же идея работает, если пользуешься не пакетами DTS, а wizard’ом. Там в шаге Specify Table Copy or Query надо выбрать не «Copy table(s) and view(s). «, а «Use a query to specify data to transfer», потом с помощь Query Builder там построить запрос, который просто выберет все строки из таблицы Access, потом нажимая Next добратся до раздела «Select source tables and views» и там в таблице в колонке Transform будет такая кнопка «. «. Её нажать, вылезет диалог «Column mappings and transforms», там будет кнопа «Edir SQL», нажать её тоже, откроется окошко. где будет SQL, которым создаётся destination таблица. Правишь его как хочется, можно добавить поле, в частности. Добиваешь wizard до финиша и всё хорошо.
Перенос базы данных Microsoft Office Access в Microsoft SQL Server
Со временем в ходе может потребоваться перенос файл-серверной базы данных Microsoft Office Access (Access) в формат клиент-серверной СУБД. Обычно для этой цели используется ODBC. Однако для переноса в Microsoft SQL Server (MS SQL) СУБД Access и MS SQL имеют удобные специализированные средства.
Всего существует три способа переноса базы данных из Access в MS SQL. Рассмотрим их все на примере простой базы данных состоящей из двух таблиц и одного запроса.
Перенос базы данных средствами Access («Мастер преобразования в формат SQL Server»)
Для того чтобы начать перенос нужно нажать кнопку «SQL Server» в области «Перемещение данных» вкладки «Работа с базами данных».
В открывшемся окне нужно выбрать, куда будут перенесены данные.
Возможны два варианта:
- Экспорт в уже существующую базу данных MS SQL;
- Создание новой базы данных (по умолчанию).
Задаём имя сервера, на который будем переносить базу данных, имя создаваемой базы данных и указываем имя пользователя и пароль для подключения.
Далее предстоит выбрать таблицы, которые будут перенесены.
Для выбора одной таблицы служит кнопка «>», а для выбора всех таблиц кнопка «>>». Для того чтобы отказаться от переноса предназначены кнопки « «Импорт данных».
Откроется «Мастер импорта и экспорта данных»
После нажатия кнопки «Далее» необходимо выбрать источник, из которого будут импортированы данные.
В раскрывающемся списке «Источник данных» необходимо выбрать «.Net Framework Data Provider for Odbc» (если не выбрано по умолчанию) и в строке Dsn в таблице указать имя созданного выше источника данных для базы данных Access. Строка подключения («Connection String») будет сформирована автоматически.
Далее необходимо указать в какую базу данных, какого экземпляра MS SQL импорт. Для этого после нажатия кнопки «Далее» выбираем в раскрывающемся списке «Назначение» либо «Microsoft SQL Server Native Client» (как показано на скриншоте ниже) либо «Microsoft OLE DB Provider for SQL Server», указываем нужную базу данных имя пользователя и пароль для подключения.
Далее будет показано окно, в котором нужно указать способ импорта. Для импорта из Access лучше оставить значение по умолчанию.
Затем нужно выбрать таблицы, которые будут импортированы. Как уже было отмечено выше, при использовании ODBC импорт запросов Accessне доступен. Поэтому, в отличие от предыдущего скриншота со списком объектов для импорта в этом списке будут исключительно таблицы.
С помощью флажков можно выбрать как все таблицы сразу (что и сделано в этом примере), так и некоторые из них в отдельности.
После нажатия на кнопку далее будет показано окно предупреждением. Его можно просто проигнорировать и нажать кнопку «Далее».
Затем будет показано окно с окончательными настройками процесса импорта. Оставим все значения по умолчанию.
Далее отобразится завершающее окно с общей итоговой сводкой информации о предстоящем процессе импорта.
После нажатия на кнопку «Готово» процесс импорта будет выполнен. Если всё сделано правильно и импорт выполнен успешно, окно с информацией о выполнении импорта не будет содержать ошибок (см. скриншот ниже).
Для завершения работы мастера достаточно нажать кнопку «Закрыть».
Результат можно увидеть SQL Server Management Studio.
Перенос базы данных со стороны средствами ODBC
Этот способ является универсальным для экспорта данных из Access в любую другую СУБД. Достаточно только чтобы она поддерживала работу с ODBC.
Пример такого экспорта уже был ранее рассмотрен в статье «Работа с базой данных SQLite средствами Access»
Для MS SQL данный способ переноса базы данных из Access не желателен, так как экспортируются только таблицы с данными, а запросы экспортируются только в виде обычных таблиц.
Однако такая возможность переноса до сих пор имеется (версия 2014 не стала исключением). Поэтому, рассмотрим и её тоже.
Для начала создадим источник данных для работы с MS SQL (пусть это будет пользовательский DSN).
Укажем драйвер для источника.
После этого будет запущен процесс его создания и настройки.
Зададим имя источника данных и укажем имя экземпляра MS SQL, к которому требуется выполнить подключение.
Далее введём имя пользователя и пароль для подключения.
После этого укажем базу данных, в которую планируется перенести таблицу или запрос. Перенос с помощью ODBC можно выполнить только в уже существующую базу данных. Поэтому, если данные требуется перенести в новую базу данных, её необходимо предварительно создать.
Далее, при необходимости, можно задать дополнительные настройки, но, в подавляющем большинстве случаев подходят и настройки по умолчанию.
После нажатия кнопки «Готово» будет показано окно с итоговой информацией о создаваемом источнике данных.
Для того чтобы источник данных был окончательно создан, достаточно нажать кнопку «Ok». Но, лучше предварительно проверить его работоспособность, нажав на кнопку «Проверить источник данных».
Если всё сделано правильно, будет показано сообщение об успешной проверке.
Теперь, когда есть источник данных, можно приступать непосредственно к процессу переноса.В качестве примера экспортируем из базы данных единственный запрос «Contacts Запрос».
Для этого выделим его мышью и нажмём на кнопку «Дополнительно» в области «Экспорт» вкладки «Внешние данные». В раскрывшемся меню выберем «База данных ODBC».
Далее в диалоговом окне нужно ввести, как будет назваться экспортируемый объект (в данном случае запрос) после того, как он будет экспортирован.
В данном случае оставлено исходное значение.
После нажатия кнопки «Ok» необходимо выбрать созданный источник данных.
Затем ввести имя пользователя и пароль для подключения к серверу.
После нажатия кнопки «Ok» экспорт будет выполнен.
Однако, как уже говорилось выше, результат экспорта в случае запроса не корректный.
Вместо представления «Contacts Запрос» в базе данных MS SQL была создана одноимённая таблица.
Кроме того, даже если экспортировать только таблицы, после экспорта необходимо выполнить множество дополнительных действий (повторное создание связей и т.д.). Поэтому, описанный способ переноса баз данных из Access в MS SQL уже практически не применяется.
Вместо него настоятельно рекомендуется использовать либо встроенный «Мастер преобразования в формат SQL Server» либо импортировать базу данных Access со стороны MS SQL его средствами.
Access экспорт в sql
Для того чтобы перенести базу данных из Access 2016 в MS SQL Server необходимо выполнить ряд действий.
Перенос производится отдельными таблицами. Перенос связей не проверялся. Задача состоит в переносе данных на сервер MS SQL Server, для последующей обработки скриптами.
Открываем вашу базу Access. Выделяем нужную нам таблицу для переноса. (Если необходимо переносить несколько таблиц из Access, то потребуется несколько раз выполнить инструкцию). Переходим на вкладку Внешние данные, в разделе экспорт ищем Дополнительно.
Щелкаем по кнопке Дополнительно, откроется меню выбора , выбираем База данных ODBC
Откроется окно. Таблица, которую переносим из Access, называется test. В поле ввода необходимо написать название таблицы, которая будет создана на SQL Server
Откроется окно , Переходим на вкладку как на скрине
Если настройка выполняется первый раз то, Нажимаем Создать и выбираем подходящую вам настройку. Если вы переносите вторую и последующие таблицы, то выбираем подключение и жмем ОК.
Жмем далее и выбираем нужный нам драйвер
Жмем далее и Готово.
Откроется настройка подключения к серверу. Заполняем поля Имя – оно будет отображаться в списке выбора (удобное для вас) и жмем на галочку для выбора сервера. Будет долго думать, потом в выпадающем списке выбираем название экземпляра. Скорее всего у вас будет один.
Жмем Далее. Выбираем проверку подлинности, такую же как на сервере. Хотя может быть и так и так.
Далее указываем базу по умолчанию (потом при экспорте таблиц ее можно будет менять) , остальные настройки оставляем как есть.
Готово, делаем проверку источника данных. ТЕСТ УСПЕШЕН! Жмем везде ОК
Дальше, выбираем наш источник данных Жмем ОК
Открывается окно, вводим пароль и жмем Параметры
На этом шаге мы выбираем базу данных, в которую необходимо перенести данные (создастся новая таблица , которую мы указывали в начале — Tablenew) Жмем ОК Вылезет окно, шаги можно сохранить , а лучше не стоит.
На сервере создалась наша таблица со всеми данными.
Перенос успешно завершен. При переносе следующих таблиц не нужно выполнять шаги по созданию подключения.
Импорт в Access БД MS SQL;
Язык QBE
Лабораторная работа 3. Разработка запросов на языке QBE
Варианты заданий
Содержание отчета
— схема БД (если изменялась);
— наборы данных, содержащихся в таблицах БД;
— примеры использования insert, update и delete для корректных и некорректных данных (нарушающих ограничения и ссылочную целостность);
— примеры update и delete, вызывающих каскадные изменения и удаление данных;
— примеры использования alter table для корректировки структуры таблиц.
Варианты заданий приведены в ПРИЛОЖЕНИИ.
Язык QBE (Query By Example) является языком запросов к базам данных, предназначенным для составления запросов пользователями, не являющимися специалистами в области разработки программного обеспечения. В настоящее время реализации языка можно найти в форме графических конструкторов запросов в таких продуктах как Microsoft Access (далее по тексту — Access), Microsoft Power Query для Excel или OpenOffice Base.
Для подключения Access к БД MS SQL необходимо выполнить следующие действия:
(i) Запустить Access — ПускВсе программыMicrosoft OfficeMicrosoft Access 2010.
(ii) Создать БД Access — ФайлСоздатьИмя файла — УниверситетСоздать.
(iii) Создать источник данных ODBC — ПускПанель управленияАдминистрированиеИсточники данных (ODBC)Добавить и ответить на вопросы мастера (сервер — SQL Server, имя источника — Университет, БД по умолчанию — Университет).
(iv) Импортировать БД в Access — Внешние данныеБаза данных ODBCИсточник данных компьютераУниверситет и выбрать таблицы для импорта.
При импорте БД может происходить потеря части определений таблиц. Для восстановления первичных ключей необходимо открыть таблицу в конструкторе (команда Конструктор контекстного меню таблицы в окне Все объекты Access), выбрать столбец и выполнить команду Ключевое поле контекстного меню.
Для восстановления внешних ключей необходимо:
(i) Пометить их как индексируемые поля — выбрать столбец в конструкторе таблицы и установить значение свойства Индексированное поле в Да (Допускаются совпадения).
(ii) Проверить соответствие типов, аналогом identity в Access являются столбцы Счетчик, реализуемые на основе типа данных Длинное целое. Если внешние ключи, ссылающиеся на первичные ключи типа Счетчик будут иметь тип, отличный от Длинное целое, при создании связей таблиц могут происходить ошибки.
(iii) Создать схему БД (рис. 1) — Работа с базами данныхСхема данных и выбрать все имеющиеся таблицы. Далее необходимо создать связи путем перетаскивания первичного ключа на внешний или наоборот.
Средство импорта и экспорта данных в Microsoft SQL Server 2008
В СУБД Microsoft SQL Server 2008 существует отличный функционал по импорту и экспорту данных, причем в разные форматы и разные базы данных. Его можно также использовать для простого переноса данных из одной базы в другую или с одного сервера на другой. Сегодня мы рассмотрим примеры использования данного средства, и, как мне кажется, это очень удобно.
Мы с Вами уже не раз затрагивали тему импорта и экспорта данных в MS SQL Server 2008, например, в статьях:
Но так или иначе, это было связанно с клиентским приложением, т.е. Access, другими словами, мы это делали для пользователей, чтобы они могли выгружать или загружать данные в базу. А теперь пришло время поговорить о том, чем может, и, наверное, должен пользоваться системный администратор или программист для подобного рода задач.
Примечание! Далее подразумевается, что у Вас уже установлена СУБД Microsoft SQL Server 2008 и средство импорта и экспорта данных, так как оно идет в комплекте, и на примере Windows 7 Вы можете наблюдать в меню «Пуск-> Все программы-> Microsoft SQL Server 2008 R2-> Импорт и экспорт данных (32-разрядная версия)». У меня это выглядит следующим образом:
И для того чтобы рассмотреть функционал по импорту и экспорту данных, давайте разберем два примера, первый по импорту данных, а второй по экспорту данных.
Импорт данных из Excel документа в MSSql 2008
Создаем тестовые данные в документе Excel
Мы будем использовать старый, но проверенный Excel 2003 и формат файла у нас будет xls.
Данные будут вот такие, файл я назвал test_file.xls:
Сразу скажу, что в данном примере мы будем импортировать данные в новую таблицу, поэтому на название полей мы не обращаем внимания. Но если бы мы импортировали уже в существующую таблицу, то нам в процессе импорта пришлось бы задавать соответствие этих полей или изначально в файле создать столько полей с тем же названием и в той же последовательности, как и в таблице. А если этого не сделать, то те поля, которые отсутствуют в таблице в базе, будут со значением null. Как задать соответствие этих полей и как импортировать данные в уже существующую таблицу я покажу в процессе импорта.
Примечание! Сервер располагается локально, база данных называется test.
Шаг 1
Итак, приступим, у нас есть файл, теперь запускаем средство импорта, и у нас открывается следующее окно:
Шаг 2
Нажимаем далее, где нам предлагают выбрать источник данных, в нашем случае это Excel, мы выбираем файл, версию excel и ставим галочку, что первая строка — это заголовок:
Шаг 3
Жмем далее, нам предлагают выбрать назначение, куда копировать эти данные, мы выбираем SQL Server, указываем имя сервера, т.е. его адрес, в нашем случае, как я уже сказал, он расположен локально. Также не забудьте про проверку подлинности, выбирайте тот метод, который у Вас настроен на сервере, и, конечно же, про базу данных, в которую копировать:
Шаг 4
Снова жмем далее, где мы укажем все ли данные копировать, в нашем случае мы говорим что все:
Шаг 5
Жмем далее, и попадаем на окно выбора листа с данными и задания названия таблицы в нашей базе, я выбрал лист 1 и назвал таблицу test_table:
И, кстати, вот на этом этапе можно задать соответствие полей. В случае если Вы импортируете данные в существующую таблицу, для соответствия полей Вам необходимо выбрать таблицу из выпадающего списка и нажать изменить, где Вы также можете задать такие параметры как назначение, тип данных и другие, для примера вот это окно:
Шаг 6
Это было небольшое отступление, а в нашем примере мы жмем далее, и попадаем в окно, в котором можно сохранить все наше действия в пакет, но мы этого делать не будем, а сразу же нажмем готово:
Шаг 7
После появится окно, где мы все проверяем и жмем готово:
Шаг 8
И в заключение у нас появится еще одно окно, так сказать результат наших действий, жмем закрыть:
Как видно, передано 3 строки, т.е. импортировано — это означает, что все наши данные, которые были в файле, импортировались.
И для того, чтобы проверить какие данные у нас импортировались, выполним в Management Studio простой запрос select.
И как видите все хорошо!
Экспорт данных из Microsoft SQL Server 2008 в файл Excel
А теперь давайте рассмотрим пример экспорта данных из нашей только что созданной таблицы в Excel документ.
Шаг 1
Для этого делаем практически то же самое, открываем средство экспорта, но уже здесь в качестве источника указываем нашу базу:
Шаг 2
Жмем далее, где нам предлагают указать назначение экспорта, мы соответственно выбираем Excel, и задаем путь и название выгружаемого файла:
Шаг 3
После того как Вы нажмете далее, Вы попадете в окно выбора данных, т.е. какие именно данные мы будем выгружать, и здесь давайте укажем — выгружать данные на основе запроса. Так как, когда мы импортировали данные, мы выбрали все, а теперь для примера выберем не все, а на основе запроса, можно также выбрать все и указать таблицу или представление VIEWS, в котором уже будут отфильтрованные данные, но мы напишем SQL запрос:
Шаг 4
И в следующем окне вставляем свой запрос, например, я написал вот такой:
Также в этом окне Вы можете выполнить анализ своего запроса, на предмет наличия ошибок или выбрать файл, который содержит текст запроса, и, если все хорошо, жмем далее.
Шаг 5
На следующем окне сразу же можем нажимать далее, если конечно Вы не хотите задать свои названия полей в выгружаемом файле.
Шаг 6
Затем на следующем окне все проверяем и жмем готово.
Шаг 7
Далее, как и в импорте, жмем последний раз готово. И все, после этого у Вас в той папке, которую Вы указали, появится документ Excel с Вашими данными.
После рассмотрения этих примеров, я думаю стало понятно, как можно осуществлять импорт и экспорт данных в MS SQL Server 2008. Использовать можно не только Excel, но и другие источники данных, ну я думаю, дальше Вы разберетесь сами, так как это не так сложно, но если у Вас все равно возникают вопросы, можете задавать их в комментариях.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую пройти наши курсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
