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