Programs

SQL_STORED_PROCEDURE - prc_table2insert

Main program

Name	prc_table2insert
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Generate insert commands for a given table or view. This procedure exports data for a given table to a INSERT INTO {table_name}({columnlist_names}) VALUES ({columnlist_data}) script. Table- and fieldname parameter values may optionally be enclosed by []. If the schema_name for table_name specification is omitted, dbo is used. If the database_name for table_name specification is omitted, DB_NAME() is used. The following fieldtypes are not supported and will be filtered out from the columnlist: system_type_id type_descrption -------------- --------------- 34 image 98 sql_variant 240 hierarchyid/geometry/geography 165 varbinary 173 binary 189 timestamp 241 xml PARAMETERS 1 @table_name NVARCHAR(255) Table- or viewname. Temp tables are not supported. 2 @whereclause NVARCHAR(255) Optional WHERE clause to filter rows. Must begin with 'WHERE '. 3 @orderfields NVARCHAR(255) Optional (comma separated) list of fieldnames to order rows (ascending). For descending sortorder add ' DESC' to fieldnames. 4 @skipfields NVARCHAR(255) Optional (comma separated) list of fieldnames to skip. 5 @script NVARCHAR(MAX) OUTPUT Output string containing the insert commands. EXAMPLES DECLARE @script NVARCHAR(MAX) EXEC [dbo].[prc_table2insert] @table_name='[DBMAINT].[dbo].[Ref_setup]', @script=@script OUTPUT SELECT @script DECLARE @script NVARCHAR(MAX) EXEC [dbo].[prc_table2insert] @table_name='CommandLog', @whereclause='WHERE [ID] <= 1000', @orderfields='ID', @script=@script OUTPUT SELECT @script USED OBJECTS - dbo.fun_format_table_name (UDF in local database) - dbo.fun_nsplit (UDF in local database) (STRING_SPLIT is avoided because the lack of ordering and trimming) HISTORY 2022-12-01 - Created procedure 2022-12-06 - Paramater @table_name accepts with or without schema_name. 2022-12-08 - Set identity_insert command had double [[ - Changed output parameter to doublebyte NVARCHAR(MAX) 2024-05-27 - Added parameter for database_name - Enhanced schema name handling 2024-05-28 - Enhanced error handling 2025-02-14 - Check table exists 2025-05-17 - Changed length of parameter @table_name to NVARCHAR(260) because schema_name can be provided 2025-10-20 - Removed USE [dbname] GO from output 2026-01-23 - Use STRING_AGG to create columnlists - Use STRING_AGG to select datarows - Removed input parameter @database_name - Added input parameters @whereclause, @orderfields and @skipfields 2026-01-28 - Fixed error: STRING_AGG aggregation result exceeded the limit of 8000 bytes TAGS <program> <description>Generate insert commands for a given table or view</description> <minversion>14</minversion> <generic>1</generic> <author>Gerrit Mantel</author> <created>2022-12-01</created> <lastmodified>2026-01-28</lastmodified> </program>
Description	Generate insert commands for a given table or view
Minversion	14.0
Generic	1
Author	Gerrit Mantel
Created	2022-12-01 00:00:00
Lastmodified	2026-01-28 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	2
Sourcecode

Sub programs