sql-server - синтаксис - сложные sql запросы примеры




Каков наилучший способ автоматического создания операторов INSERT для таблицы SQL Server? (13)

Microsoft должна рекламировать эту функциональность SSMS 2008. Функция, которую вы ищете, встроена в утилиту Generate Script , но функциональность отключена по умолчанию и должна быть включена при написании таблицы.

Это быстрый запуск для создания INSERT для всех данных в вашей таблице, без использования сценариев или надстроек для SQL Management Studio 2008:

  1. Щелкните правой кнопкой мыши по базе данных и выберите « Задачи» > « Сгенерировать скрипты» .
  2. Выберите таблицы (или объекты), с которыми вы хотите сгенерировать сценарий.
  3. Перейдите на вкладку « Настройка сценариев » и нажмите кнопку « Дополнительно» .
  4. В категории « Общие » перейдите к « Тип данных для сценария»
  5. Существует 3 варианта: только схема, только данные , а также схема и данные . Выберите подходящую опцию и нажмите « ОК» .

Затем вы получите инструкцию CREATE TABLE и все INSERT для данных прямо из SSMS.

Мы пишем новое приложение, и при тестировании нам понадобится куча фиктивных данных. Я добавил эти данные с помощью MS Access для выгрузки файлов excel в соответствующие таблицы.

Каждый раз так часто мы хотим «обновить» соответствующие таблицы, что означает их сброс, повторное создание и запуск сохраненного запроса на доступ к MS Access.

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

Теперь у меня есть данные в таблицах. Каков наилучший способ автоматического создания большого списка инструкций INSERT из этого набора данных?

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

Я использую 2008 Management Studio для подключения к базе данных SQL Server 2005.



Если вам нужен программный доступ, вы можете использовать хранимую процедуру с открытым исходным кодом `GenerateInsert.

Инструкция INSERT (ы)

Так же, как простой и быстрый пример, для создания инструкций INSERT для таблицы AdventureWorks.Person.AddressType выполните следующие утверждения:

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

Это создаст следующий скрипт:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

Как упоминалось @Mike Ritacco, но обновлено для SSMS 2008 R2

  1. Щелкните правой кнопкой мыши имя базы данных
  2. Выберите «Задачи»> «Создать скрипты»
  3. В зависимости от ваших настроек, страница ввода может отображаться или не отображаться
  4. Выберите «Выбрать конкретные объекты базы данных»,
  5. Разверните дерево и проверьте соответствующие таблицы.
  6. Нажмите кнопку "Далее
  7. Нажмите Дополнительно.
  8. В разделе «Общие» выберите соответствующую опцию «Типы данных для сценария»,
  9. Завершить работу мастера

Затем вы получите все инструкции INSERT для данных прямо из SSMS.

EDIT 2016-10-25 SQL Server 2016 / SSMS 13.0.15900.1

  1. Щелкните правой кнопкой мыши имя базы данных

  2. Выберите «Задачи»> «Создать скрипты»

  3. В зависимости от ваших настроек, страница ввода может отображаться или не отображаться

  4. Выберите «Выбрать конкретные объекты базы данных»,

  5. Разверните дерево и проверьте соответствующие таблицы.

  6. Нажмите кнопку "Далее

  7. Нажмите Дополнительно.

  8. В разделе «Общие» выберите соответствующую опцию «Типы данных для сценария»,

  9. Нажмите ОК.

  10. Выберите, хотите ли вы, чтобы выход переходил на новый запрос, буфер обмена или файл

  11. Дважды нажмите «Далее»

  12. Ваш скрипт подготовлен в соответствии с настройками, которые вы выбрали выше.

  13. Нажмите Готово


Мы используем эту хранимую процедуру - она ​​позволяет настраивать таргетинг на определенные таблицы и использовать предложения where. Текст можно найти here .

Например, он позволяет это сделать:

EXEC sp_generate_inserts 'titles'

Не используйте вставки, используйте BCP


Первая ссылка на sp_generate_inserts довольно крутая, вот действительно простая версия:

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Queues'               -- your table

DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) + 
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '
PRINT @S'

EXEC (@SQL)

В моей системе я получаю этот результат:

INSERT INTO Queues([QueueName], [iSort])
SELECT 'WD: Auto Capture', '10' UNION 
SELECT 'Car/Lar', '11' UNION 
SELECT 'Scan Line', '21' UNION 
SELECT 'OCR', '22' UNION 
SELECT 'Dynamic Template', '23' UNION 
SELECT 'Fix MICR', '41' UNION 
SELECT 'Fix MICR (Supervisor)', '42' UNION 
SELECT 'Foreign MICR', '43' UNION 
...

У вас есть данные в производственной базе данных? Если это так, вы можете настроить период обновления данных через DTS. Мы делаем еженедельно по выходным дням, и очень приятно иметь чистые, реальные данные каждую неделю для нашего тестирования.

Если у вас еще нет производства, тогда вы должны создать базу данных, которая хочет, чтобы вы ее хотели (свежий). Затем дублируйте эту базу данных и используйте эту недавно созданную базу данных в качестве тестовой среды. Когда вам нужна чистая версия, просто повторите очистку, а Боб - ваш дядя .


Это можно сделать и с помощью Visual Studio (по крайней мере, в версии 2013 года).

В VS 2013 также можно отфильтровать список строк, на которых основан оператор вставки, в SSMS это невозможно, так как я знаю.

Выполните следующие действия:

  • Откройте окно «Обозреватель объектов SQL Server» (меню: / Просмотр / Обозреватель объектов SQL Server)
  • Открыть / развернуть базу данных и ее таблицы
  • Щелкните правой кнопкой мыши на таблице и выберите «Просмотр данных» из контекстного меню
  • Это отобразит данные в основной области
  • Дополнительный шаг: нажмите на значок фильтра «Сортировка и набор данных фильтра» (четвертый значок слева в строке над результатом) и примените некоторый фильтр к одному или нескольким столбцам
  • Нажмите иконки «Сценарий» или «Сценарий в файл» (значки справа от верхнего ряда, они выглядят как маленькие листы бумаги)

Это создаст (условные) инструкции вставки для выбранной таблицы в активное окно или файл.


Кнопки «Фильтр» и «Сценарий» Visual Studio 2013 :


Я использовал этот script который я разместил в своем блоге ( How-to для создания инструкций операторов Insert на сервере sql ).

До сих пор работал на меня, хотя они могут быть ошибками, которые я еще не обнаружил.


Я использую sqlite для этого. Я считаю, что это очень полезно для создания баз данных для скретч-тестов.

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql


Я также изучил эту тему, но я не мог получить конкретное решение для этого. В настоящее время подход, который я придерживаюсь, копирует содержимое в excel из студии SQL Server Managment и затем импортирует данные в Oracle-TOAD, а затем генерирует инструкции insert


GenerateData - отличный инструмент для этого. Это также очень легко сделать для него, потому что исходный код вам доступен. Несколько приятных функций:

  • Генератор имен для имен людей и мест
  • Возможность сохранять профиль генерации (после его загрузки и настройки локально)
  • Возможность настройки и управления генерацией через скрипты
  • Множество различных выходов (CSV, Javascript, JSON и т. Д.) Для данных (в случае, если вам нужно протестировать набор в разных средах и хотите пропустить доступ к базе данных)
  • Бесплатно . Но подумайте о том, чтобы пожертвовать, если вы найдете полезное ПО :).







code-generation