Generate insert script for selected records?

sql

prompt-engineering

data-types

sql-scripts

byAlex Kataev·Dec 30, 2024

To generate insert scripts from selected rows, construct a SELECT statement that morphs into a single-string INSERT command row by row. Concatenate column values and SQL syntax using string functions like CONCAT() or ||.

SELECT 'INSERT INTO table (col1, col2) VALUES (' 
  || QUOTE(column1) || ', ' 
  || QUOTE(column2) || ');'
FROM table
WHERE condition;

Replace table, col1, col2, and condition as necessary. Utilize QUOTE() or its equivalent to accurately handle strings and special characters leading to executable INSERT statements for the filtered dataset.

Handling complex scenarios for insert scripting

In the quest to go beyond the basic scripting, we dive deep into scripts for inserting large datasets, handling NULLs correctly, ensuring data types consistency, and automating scripting processes.

Handling large datasets and special characters

When dealing with large datasets, breaking up your inserts can avoid clogging your database with intermediary tables or using the SELECT INTO:

-- Senior SQL citizens prefer creating temp tables
SELECT INTO temp_table FROM original_table WHERE condition;

Then script the contents of temp_table. Tame NULL values and characters that might wreak havoc in your script, like single quotes, with saviour functions like ISNULL and REPLACE:

SELECT 'INSERT INTO table (col1, col2) VALUES ('
  + ISNULL('''' + REPLACE(column1, '''', '''''') + '''', 'NULL') + ','
  + ISNULL(CONVERT(VARCHAR, column2), 'NULL') + ');'
FROM temp_table;
-- Oh Null, thou art a heartless beast!

Automation and data type consistency

For automation, cast a stored procedure spell with dynamic SQL that accepts parameters for table names and filters:

CREATE PROCEDURE GenerateInsertScript 
  @TableName NVARCHAR(128), @FilterCondition NVARCHAR(4000)
AS
BEGIN
  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = '...'; -- write an epic poem in SQL here
  EXEC sp_executesql @SQL;
END
-- Hogwarts School of SQL Magic.

Use CONVERT or CAST as needed, ensuring data type consistency. Stitch parts together with the fabled STUFF() function and handle tables with variable column counts.

Best practices and debugging

Please remember to:

Exclude identity columns unless you've summoned the mighty IDENTITY_INSERT.
Script a commit/rollback strategy to protest against half-hearted inserts.
Use sys.all_columns and kin for some dynamic SQL mischief.
Make your values squeaky-clean with proper formatting and escaping.

Tools of the trade like SSMS or SSDT can sometimes make a mess when scripting complex scenarios. Review and fine-tune their output before they run Supercalifragilisticexpialidocious scripts on your data.