Converting Select results into Insert script - SQL Server

SSMs and automation tools

SQL Server Management Studio (SSMS) and extensions like the SSMS Toolpack provide capabilities that can automate the conversion of SELECT query outputs into INSERT statements. This is particularly useful when dealing with larger datasets or different databases.

Generating insert scripts manually

Sometimes you need a quick and dirty solution. In such instances, you can manually concatenate your SELECT results into an INSERT INTO script format:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 
  'INSERT INTO MyTargetTable (Column1, Column2) VALUES (' +
   QUOTENAME(Col1, '''') + ', ' + 
   QUOTENAME(Col2, '''') + ');' + CHAR(13)
FROM SourceTable
WHERE SomeCondition;

EXECUTE sp_executesql @sql;


Here, QUOTENAME() is your knight in shining armor, handling the quoting, while ISNULL is your sidekick, managing those pesky nulls.

Dynamic insert script creation

If you're dealing with constantly shifting table structures, dynamic insert script creation comes in handy. SQL Server's system catalog view, sys.all_columns, and dynamic SQL work together like Batman and Robin for this task:

DECLARE @table NVARCHAR(128) = N'TargetTable', @sql NVARCHAR(MAX), @cols NVARCHAR(MAX);

SELECT @cols = STUFF((
    SELECT ', ' + QUOTENAME(name) 
    FROM sys.all_columns 
    WHERE object_id = OBJECT_ID(@table) 
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

SET @sql = 'INSERT INTO ' + @table + '(' + @cols + ') SELECT ' + @cols + ' FROM ...';
-- Above query: Paint me like one of your French girls

EXECUTE(@sql);


STUFF and FOR XML PATH work together to produce a comma-separated list of column names, dynamically built for our superhero INSERT statement.

Streamlining with procedures

Creating a stored procedure can help automate the INSERT script generation process. It's like having your own Alfred, taking care of the routine work while you focus on the big picture:

CREATE PROCEDURE GenerateInsertScript 
  @Source NVARCHAR(128),
  @Target NVARCHAR(128),
  @Filter NVARCHAR(MAX) = NULL
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  -- Dynamic script generation logic...just think of it as Alfred preparing your Batmobile
  SET @sql = --...;
  EXECUTE(@sql);
END
GO


With parameters like @Source, @Target, and @Filter, you can personalize your data movement tasks Batman-style.

Replacement and clean up

In generated scripts, always swap temp table names with the actual target table names. And never forget to clean up after your data heist: always drop temporary tables to keep your database Gotham clean.

Visit toolpack's official site

For a deep-dive into the world of the SSMS Toolpack, its official website is your Batcave. Arm yourself with best practices, detailed instructions, and effective usage tips.

Customize with advanced options

SSMS advanced options let you calibrate script generation to specific scenarios. Whether you need Data only or a fusion of schema and data, they got your back.

Execute in a new window

Preview the generated script in a new query window. This allows you to make necessary adjustments before deployment, avoiding potential mishaps that could ruin your perfect crime.