Stored procedure to export data in insert statement format in SQL Server

SP to export data in SQL Server

While there are already tools that do this very well, such as Management Studio itself, sometimes having a stored procedure that accomplishes this is quite handy, as it's portable, can be used within any application. The SP has an option to preserve identity values in exported data. This can be very handy in normalized databases, where referential integrity is a concern. Please not, that this stored procedure requires a table-valued function for splitting a delimiter-separated list into values (found here).

Below is the source code for the SQL Server procedure that generates a series of insert statements containing exported data. It allows to specify the order in which tables are populated via a parameter. Also, the generated SQL wraps all DML code inside a transaction to ensure that if an error occurs, everything is rolled back. The complete source code is below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Creates an SQL statement batch for populating the database with data.
--All of the current data in the database will be exported in SQL insert statment format.
--Should only be used on very small databases for testing purposes.
ALTER PROCEDURE [dbo].[ExportAllData]
(
	@TableOrder varchar(1000) = '',--Comma-delimited list of tables that specifies in which order to insert data into the tables.
	@PreserveIdentityValues bit = 1,
	@TargetDatabase varchar(50) = null --Specifies the database name to which the data is to be imported
)	
AS
BEGIN
	
	SET NOCOUNT ON;

	set @TableOrder = replace(replace(replace(@TableOrder,char(13),''), char(10), ''), char(9), '')

	declare @OrderTable table (Row int,TableName varchar(30))	
	insert into
	@OrderTable (Row, TableName)
	select RowNumber, Convert(varchar(30), RowValue) from TableFromString(@TableOrder,',',1)
	

	declare @TableName varchar(100)
	declare @ColumnName varchar(100)
	declare @ColumnTypeName varchar(20)
	declare @IsIdentity bit
	declare @InsertStatement varchar(max)
	declare @IsFirstColumn bit
	declare @AllIdentity bit
	declare @HasIdentity bit
	declare @ReplaceApos as nvarchar(20)
	set @ReplaceApos = char(39)+char(39)+char(39)+char(39)+',' + char(39) + char(39) + char(39) + char(39) + char(39) + char(39) + ')'


	declare @DataSelectStatement nvarchar(max)

	declare @InsertStatements table (StatementID int identity(1,1), StatementText nvarchar(max))

	if @TargetDatabase is not null
	begin
		insert into @InsertStatements (StatementText)
		values ('USE ' + @TargetDatabase)
	end

	insert into @InsertStatements (StatementText)
	values ('BEGIN TRY')
	insert into @InsertStatements (StatementText)
	values ('BEGIN TRANSACTION')

	if @PreserveIdentityValues = 1
	begin
		insert into @InsertStatements (StatementText)
		values ('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"')
	end

	DECLARE tables_cursor CURSOR FOR
	select SysObjects.[Name]
	FROM SysObjects
	WHERE  SysObjects.[type] = 'U'
	ORDER BY isnull((select Row from @OrderTable where TableName = SysObjects.[Name]),9999999) asc
	, SysObjects.[Name] asc


	OPEN tables_cursor;

	FETCH NEXT FROM tables_cursor
	INTO @TableName;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE column_cursor CURSOR FOR 
		SELECT distinct SysColumns.[Name], SysColumns.ColStat as ColumnName, SysTypes.[Name] as TypeName
		FROM
			SysObjects INNER JOIN SysColumns
		ON SysObjects.[Id] = SysColumns.[Id]
			INNER JOIN SysTypes
		ON SysTypes.[xtype] = SysColumns.[xtype]
		WHERE  SysObjects.[type] = 'U'
		AND SysObjects.name = @TableName
		AND SysTypes.[Name] != 'sysname'
		

		

		set @InsertStatement = 'insert into dbo.' + @TableName + ' ('
		set @DataSelectStatement = ''

		set @IsFirstColumn = 1
		set @AllIdentity = 1
		set @HasIdentity = 0


		OPEN column_cursor;
		FETCH NEXT FROM column_cursor INTO @ColumnName, @IsIdentity,@ColumnTypeName;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			if @IsIdentity = 1
			begin
				set @HasIdentity = 1
			end
			if @IsIdentity = 0 OR @PreserveIdentityValues = 1
			begin
				set @AllIdentity = 0
				if @IsFirstColumn = 0
				begin
					set @InsertStatement = @InsertStatement + ', '
					set @DataSelectStatement = @DataSelectStatement + ' + '', '''
				end
				set @InsertStatement = @InsertStatement + @ColumnName
				set @IsFirstColumn = 0

				if charindex('date', @ColumnTypeName) > 0 OR charindex('time', @ColumnTypeName) > 0
				begin
					set @DataSelectStatement = @DataSelectStatement + '+ isnull('''''''' + replace(convert(nvarchar(30), ' + @ColumnName + ', 21), ' +  @ReplaceApos + '+ '''''''', ''null'')'
				end
				else if @ColumnTypeName = 'text'
				begin
					set @DataSelectStatement = @DataSelectStatement + '+ isnull('''''''' + replace(convert(nvarchar(max), ' + @ColumnName + '), ' +  @ReplaceApos + '+ '''''''', ''null'')'
				end
				else if charindex('char', @ColumnTypeName) > 0 OR @ColumnTypeName = 'uniqueidentifier'
				begin
					set @DataSelectStatement = @DataSelectStatement + '+ isnull('''''''' + replace(' + @ColumnName + ', ' +  @ReplaceApos + '+ '''''''', ''null'')'
				end
				else
				begin
					set @DataSelectStatement = @DataSelectStatement + ' + replace(isnull(convert(nvarchar(max), ' + @ColumnName + '), ''null''),' + @ReplaceApos
				end			
			end

		FETCH NEXT FROM column_cursor INTO @ColumnName, @IsIdentity,@ColumnTypeName;
		END;

		set @InsertStatement = @InsertStatement + ')' + char(13) + char(10)

		if @AllIdentity = 1
		begin
			set @DataSelectStatement = 'select ''insert into dbo.' + @TableName + ' default values''' + char(13) + char(10) + 'from dbo.' + @TableName
		end
		else
		begin
			set @DataSelectStatement = 'select + '''+ @InsertStatement +''' + '' values(''' +  @DataSelectStatement + ' + '')'''
			set @DataSelectStatement = @DataSelectStatement + '+ char(13) + char (10)' + char(13) + char (10) + 'from dbo.' + @TableName
		end
		
		if @HasIdentity = 1 AND @PreserveIdentityValues = 1
		begin
			insert into @InsertStatements (StatementText)
			values ('SET IDENTITY_INSERT ' + @TableName + ' ON')
		end

		insert into @InsertStatements
		EXEC (@DataSelectStatement)

		if @HasIdentity = 1 AND @PreserveIdentityValues = 1
		begin
			insert into @InsertStatements (StatementText)
			values ('SET IDENTITY_INSERT ' + @TableName + ' OFF')
		end

		CLOSE column_cursor;
		DEALLOCATE column_cursor;

		FETCH NEXT FROM tables_cursor
		INTO @TableName;
	END
	CLOSE tables_cursor;
	DEALLOCATE tables_cursor;

	if @PreserveIdentityValues = 1
	begin
		insert into @InsertStatements (StatementText)
		values ('exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"')
	end

	insert into @InsertStatements (StatementText)
	values ('COMMIT TRANSACTION')	

	insert into @InsertStatements (StatementText)
	values ('END TRY')

	insert into @InsertStatements (StatementText)
	values ('BEGIN CATCH')

	insert into @InsertStatements (StatementText)
	values ('ROLLBACK')

	insert into @InsertStatements (StatementText)
	values ('DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int')

	insert into @InsertStatements (StatementText)
	values ('SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()')
		
	insert into @InsertStatements (StatementText)
	values ('RAISERROR(@ErrMsg, @ErrSeverity, 1)')
	insert into @InsertStatements (StatementText)
	values ('END CATCH')

	select StatementText from @InsertStatements
	order by StatementID asc
END





Information Error Confirmation required