Как экспортировать данные из таблицы sql

(5-2) Способа перенести большую SQL таблицу

Всем привет! Это моя первая статья и пишу я ее от лица младшего инженера-разработчика на языке C#. Так что здесь не будет каких-то подробных сведений о SQL, лишь практические сведения и размышления по решению довольно не очевидной задачи, с которой мне пришлось столкнуться, для таких же новичков, как и я сам.

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

Итак, представим, что у вас есть web-сервис и SQL (MS-SQL) база данных с таблицей html-писем, которые ваш сервис рассылает пользователям. Письма хранятся за некоторое количество лет и удалить их нельзя, так как они нужны для сбора статистики и аналитики. Однако, с каждым годом количество писем растет, база разрастается, а места на SQL-сервере все меньше (в нашем случае еще одним фактором было восстановление базы на тестовую площадку, т.к. его время пропорционально росло) и с этим нужно что-то делать. Благо, в нашем случае есть свободный сервер с кучей свободного места (в реальности его может не быть и конечно это временное решение, но это выходит за рамки статьи). Так возникла задача по переносу большой таблицы (и говоря «большой», я имею в виду реально большую таблицу, все что я видел, пока искал похожие решения, было в районе 60-100Гб, в нашем случае таблица весила более 300 Гб).

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

Способ -1. Data

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

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

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

Способ 0. SELECT INTO

Шутка =) Так вы разве что положите себе базу. Однако если речь идет о небольших размерах таблицы (тогда что вы тут делаете) можно попытаться перенести базу с помощью этой инструкции. Также, если вы располагаете тестовой базой, можно провести эксперимент, чтобы оценить общее время переноса данным методом «в лоб».

Способ 1. Backup

Самый «каноничный» способ, именно это стало решением моей задачи. Делаем бэкап базы, содержащей нашу таблицу, и восстанавливаем его на другом сервере и очищаем от всего лишнего. Далее, если есть возможность остановить web-сервис, можно его передеплоить, настроив запись в перенесенную таблицу, а старую удаляем* (тут скорее всего может возникнуть момент того, что необходимо будет писать к ней запросы с джойнами, для этого гуглите как линковать sql-серверы). Если нет такой возможности, фиксируем id последнего письма (для синхронизации), затем надо будет удалить* все перенесенные письма (писать продолжим в старую таблицу).

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

Способ 2. MS-SQL Management Studio

Если у вас есть данная студия, можете попробовать воспользоваться встроенным инструментом для экспорта и импорта данных. Лично я прочитал на stack overflow, что это штука зависла на таблице в 60 гигов и рисковать не стал.

Способ 3. Partition

Улучшенный метод «в лоб». Идея в том, чтобы переносить данные обычным способом с таймером между итерациями. Вы разбиваете все строки на порции (например, по 100к) переносите порцию (и тут же ее можно удалять, однако не уверен, насколько это безопасно), затем засыпаете и так до победного конца. Переносить лучше с конца, чтобы не пришлось синхронизировать данные по окончанию. Способ, очевидно, очень медленный, однако таким образом вы перенесете все без остановки web-сервиса. Скорее всего это будет удобнее реализовать не SQL-скриптом, а с помощью какого-нибудь ORM.

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

Еще в конце хотелось бы добавить 2 важных замечания.

Любой процесс переноса-удаления строк в SQL логируется в transaction log для возможности все откатить в случае ошибки (я ранее предполагал, что это осуществляется только в рамках транзакции). Причем размер лога получается даже чуть больше объема данных. Убедитесь, что располагаете необходимым количеством места либо отключите логирование, однако это небезопасно.

Перед переносом нужно удостовериться, что файл данных и файл лога имеют необходимый размер, т.к. операции по их расширению тратят значительное количество времени, и сконфигурировать их соответствующим образом Таким образом вы оптимизируете перенос.
Спасибо всем прочитавшим! Буду рад любой критике, замечаниям и уточнениям. Делитесь своими способами и техниками работы с большими данными, т.к. зачастую это очень важная и нужная информация, которую не так-то просто найти.

How to export all data from table to an insertable sql format?

I have a Table (call it A_table ) in a database (call it A_db ) in Microsoft SQL Server Management Studio, and there are 10 rows.

I have another database (call it B_db ), and it has a Table (call it B_table ), which has the same column settings as A_table has. But the B_table is empty.

• Copy every rows from A_table to B_table .

Is there any option in Microsoft SQL Server Management Studio 2012, to create an insert SQL from a table? Or is there any other option to do that?

7 Answers 7

Quick and Easy way:

1. Right click database
2. Point to tasks In SSMS 2017 you need to ignore step 2 — the generate scripts options is at the top level of the context menu Thanks to Daniel for the comment to update.
3. Select generate scripts
4. Click next
5. Choose tables
6. Click next
7. Click advanced
8. Scroll to Types of data to script — Called types of data to script in SMSS 2014 Thanks to Ellesedil for commenting
9. Select data only
10. Click on ‘Ok’ to close the advanced script options window
11. Click next and generate your script

I usually in cases like this generate to a new query editor window and then just do any modifications where needed.

I know this is an old question, but victorio also asked if there are any other options to copy data from one table to another. There is a very short and fast way to insert all the records from one table to another (which might or might not have similar design).

If you dont have identity column in table B_table:

If you have identity column in table B_table, you have to specify columns to insert. Basically you select all except identity column, which will be auto incremented by default.

Как экспортировать данные таблицы в файл PDF из SQL?

База данных

SQL Server — универсальная база данных. Он используется во многих отраслях промышленности. Мы можем использовать студию данных AZURE, а также SQL Server Management Studio для выполнения различных операций (DDL, DML, хранимая процедура, подготовка триггера) и т. д. SQL Server поддерживает переносимость данных с помощью параметра EXPORT. По умолчанию оба инструмента поддерживают экспорт данных в форматы CSV, XSL или плоские файлы. В этой статье мы увидим, как экспортировать данные таблицы в файл PDF.

Давайте начнем с создания базы данных, таблицы и продолжим

Шаг 1: Сначала мы создаем базу данных.

Шаг 2: Теперь мы создаем таблицу.

Шаг 3: После создания таблицы мы вставляем значения в таблицу.

Шаг 4: Чтобы просмотреть вставленные значения, мы используем следующие запросы.

Шаг 5: По умолчанию студия данных Azure и SQL Server Management Studio поддерживают экспорт данных в форматы CSV, XLS и неструктурированные файлы. После завершения этого процесса мы сможем экспортировать данные таблицы в PDF. В студии управления SQL Server мы получим дополнительные параметры, как показано ниже:

• Сначала нажмите Data Pump, а затем выберите параметр » Экспорт данных «.
• После этого выберите формат экспорта PDF в окне.
• На этом шаге выберите подключение к серверу, базу данных и ее схему, таблицы и представления, которые вы хотите экспортировать, и нажмите «Далее».
• На странице настроек вывода у вас есть два основных параметра:
  • Экспортируйте данные в отдельные файлы, где вы указываете путь к папке, в которой они будут сохранены.
  • Экспорт данных в один файл, где вы указываете путь и имя файла. Мы найдем список файлов для экспорта в поле предварительного просмотра экспортированных файлов.

  SQL Базовый №4. Импорт и экспорт данных

  Если ваши данные находятся в текстовых CSV-файлах, то их можно разом импортировать в базу данных. В PostgreSQL для этого есть команда COPY. Этой командой можно как импортировать данные, так и экспортировать.

  3 шага для импорта данных из CSV:

  1. Подготовить CSV файл
  2. Создать таблицу в базе данных
  3. Выполнить импорт данных из CSV файла в заготовленную таблицу с использованием команды COPY

  Работа с CSV-файлами

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

  Каждая строка CSV-файла — это строка таблицы. В каждой строке значения столбцов разделены каким-то символом. Это может быть любой символ. В России в роли разделителя чаще всего используется двоеточие. На западе чаще всего применяется запятая.

  Обычная строка CSV-файла выглядит примерно так:

  Разделители отделяют данные разных столбцов друг от друга. Используется одна запятая без пробела после нее.

  Кавычки

  Значения разных столбцов разделены запятыми. А что делать, если само значение содержит запятые? Например, в таблице есть столбцы широты и долготы, в которых целые части от дробных отделены запятыми. Если столбец содержит разделитель, то все его значения должны начинаться и заканчиваться специальным символом text qualifier. Чаще всего это двойные кавычки.

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

  Строка заголовка

  В CSV-файле обычно присутствует заголовок. Это строка, в которой перечислены имена столбцов. Выглядит она примерно так:

  Некоторые СУБД сверяют имя столбца из файла CSV с названием столбца в таблице базы данных. В PostgreSQL такого функционала нет. Чтобы избежать ошибок нужно пропустить строку заголовка, если такая имеется. Для этого используется ключевое слово HEADER.

  Импорт данных с помощью COPY

  Чтобы импортировать данные из CSV-файла сначала нужно проверить сам источник, потом создать таблицу в базе данных. Далее нужно выполнить простой код из трех строк.

  После ключевого слова WITH указываются параметры импорта. В данном случае указано, что формат файла источника — это CSV, в первой строке которого находятся заголовки. Параметров бывает много. Чаще всего используются следующие:

  • Формат файла. Параметром format имя_формата указывается какой формат файла читается или пишется. Названия форматов: CSV, TXT, BINARY. Чаще всего применятся формат CSV. В файле TXT обычно в роли разделителя выступает табуляция.
  • Строка заголовка. Параметр header означает, что в файле в первом столбце находятся заголовки. Этот параметр говорит базе данных, что импортировать данные нужно со второй строки.
  • Разделитель. Параметр delimiter ‘символ_разделитель’ указывает какой символ в файле выступает разделителем. Разделителем может быть только 1 символ. Например, если в файле значения столбцов разделяются точкой с запятой, то параметр выглядит так: delimiter ‘;’.
  • Символ кавычек. Двойные кавычки говорят о том, что данные между ними нужно считать одним значением. Вместо кавычек в CSV-файле может использоваться другой символ. В таком случае нужно воспользоваться параметром quote ‘символ_quote_qualifier’

  Создаем таблицу

  Создадим таблицу, в которую загрузим данные из CSV-файла с перечнем всех православных храмов Москвы.

  Импорт некоторых столбцов

  Если в вашем CSV-файле есть данные только для некоторых столбцов вы все равно можете выполнить импорт. Нужно будет указать какие столбцы есть в данных.

  Добавим в нашу таблицы данные по мечетям. В CSV-файле с данными о мечетях нет столбцов MetroStation, MetroLine, Longitude, Latitude. Если попытаться импортировать данные из этого файла в таблицу religion, то вернется ошибка SQL Error [22P04]: ОШИБКА: нет данных для столбца «site».

  Названия столбцов в CSV-файле не совпадают с названиями столбцов в базе данных. В таком случае импорт делает в несколько шагов:

  1. Создается временная таблица
  2. Во временную таблицу импортируются данные из CSV-файла
  3. Из временной таблицы в основную таблицу с помощью insert into копируются нужные столбцы
  4. Временна таблица удаляется

  Экспорт с помощью COPY

  Командой COPY можно не только импортировать данные, но и экспортировать. Разница в том, что теперь вместо ключевого слова FROM используется TO.

  Есть 3 варианта экспорта:

  1. Таблица целиком
  2. Экспорт отдельных столбцов
  3. Экспорт результата запроса

  Экспорт с помощью UI

  Вся таблица целиком

  Чтобы экспортировать всю таблицу целиком найдите ее в панели Базы данных — Правый клик — Экспорт данных.

  

  Определенные строки и столбцы

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