Generate Tables Data Script : Connect SQL

Sql Server 2008 has made it easy to create scripts for not only your database structures by data itself. But if you are using Sql Server 2005 still you can create insert statements for your desired schema by using following scripts.

We will create two functions to facilitate our store procedure which will finally generate our desired script.

1- Create function which will return columns name for given schema tables

CREATE FUNCTION [dbo].[fnc_GetColumnsByCommas]

(

-- Add the parameters for the function

@schemaName VARCHAR(50),

@tableName VARCHAR(50)

)

RETURNS VARCHAR(4000)

BEGIN

DECLARE @column VARCHAR(2000)

,@columnS VARCHAR(4000)

,@i INT

SET @i=0

SET @column=''

SET @columnS=''

DECLARE Cur_Columns CURSOR STATIC FOR

SELECT sys.columns.name FROM sys.schemas
INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id

INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id

INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id

WHERE sys.types.name <>'timestamp' AND sys.objects.type ='U' AND sys.objects.name = @tableName AND sys.schemas.name = @schemaName

ORDER BY sys.columns.column_id

OPEN Cur_Columns

FETCH FIRST FROM Cur_Columns INTO @column

WHILE @@FETCH_STATUS = 0

BEGIN

IF @i=0

SET @columnS='['+@column+']'

ELSE

SET @columnS=@columnS +','+ '['+@column+']'

SET @i=@i+1

FETCH NEXT FROM Cur_Columns INTO @column

END

CLOSE Cur_Columns

DEALLOCATE Cur_Columns

-- Return the result of the function

RETURN @columns

END

2- Create function which will return required values for each row with desired column data type.

CREATE FUNCTION [dbo].[fnc_GetColumnsForValueByCommas]

(

-- Add the parameters for the function here

@schemaName VARCHAR(50),

@tableName VARCHAR(50)

)

RETURNS VARCHAR(4000)

BEGIN

DECLARE @column VARCHAR(4000)

,@typeName VARCHAR(500)

,@columnS VARCHAR(2000)

,@ColStart VARCHAR(50)

,@ColEnd VARCHAR(50)

,@i INT

SET @i=0

SET @column=''

SET @columnS=''

DECLARE Cur_Columns CURSOR STATIC FOR

SELECT sys.columns.name,sys.types.name FROM sys.schemas

INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id

INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id

INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id

WHERE sys.types.name <>'timestamp' AND sys.objects.type ='U' AND sys.objects.name = @tableName AND sys.schemas.name = @schemaName

ORDER BY sys.columns.column_id

OPEN Cur_Columns

FETCH FIRST FROM Cur_Columns INTO @column,@typeName

WHILE @@FETCH_STATUS = 0

BEGIN

IF @typeName = 'text' OR @typeName = 'uniqueidentifier' OR @typeName = 'varbinary' OR @typeName = 'smalldatetime'OR @typeName = 'char' OR @typeName = 'datetime' OR @typeName = 'varchar'

BEGIN

SET @ColStart = ' ISNULL(CHAR(39) + CAST ( '

SET @ColEnd = ' AS VARCHAR(MAX))+ CHAR(39),''NULL'') '

END

ELSE IF @typeName = 'nvarchar'OR @typeName = 'ntext' OR @typeName = 'nchar'

BEGIN

SET @ColStart = 'ISNULL( ''N''+CHAR(39)+ CAST ( '

SET @ColEnd = ' AS NVARCHAR(MAX)) + CHAR(39),''NULL'') '

END

ELSE

BEGIN

SET @ColStart = 'ISNULL(CAST ( '

SET @ColEnd = ' AS VARCHAR(MAX)), ''NULL'') '

END

IF @i=0

SET @columnS=@ColStart+@column+@ColEnd

ELSE

SET @columnS=@columnS +'+'',''+'+ @ColStart+@column+@ColEnd

SET @i=@i+1

FETCH NEXT FROM Cur_Columns INTO @column,@typeName

END

CLOSE Cur_Columns

DEALLOCATE Cur_Columns

-- Return the result of the function

RETURN @columns

END

3- Create Store Procedure to generate final script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[proc_CreateInsertStatmentsBySchema]

(

-- Add the parameters for the function here

@schemaName VARCHAR(50)

)

BEGIN

CREATE TABLE #Tables

(

id INT IDENTITY,

tbname VARCHAR(100),

columns VARCHAR(4000),

columnsForVal VARCHAR(4000)

)

INSERT INTO #Tables (tbname,columns,columnsForVal)

SELECT '['+sys.schemas.name+'].['+sys.objects.name+']', dbo.fnc_GetColumnsByCommas(@schemaName,sys.objects.name),dbo.fnc_GetColumnsForValueByCommas(@schemaName,sys.objects.name) FROM sys.schemas

INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id

WHERE sys.objects.type ='U'

AND sys.schemas.name = @schemaName

--SELECT * FROM #Tables

DECLARE @tbID INT

,@tbNAME VARCHAR(50)

,@tbColumn VARCHAR (4000)

,@tbColumnforVal VARCHAR (4000)

,@SQLstr VARCHAR(4000)

,@label VARCHAR(500)

,@Count INT

SELECT @Count = count(*) FROM #Tables

SET @tbID = 1

WHILE @tbID <> @Count +1

BEGIN

SELECT @tbNAME = tbNAME FROM #Tables WHERE id = @tbID

SELECT @tbColumn = columns FROM #Tables WHERE id = @tbID

SELECT @tbColumnforVal = columnsForVal FROM #Tables WHERE id = @tbID

SET @label = '--Table: '+@tbNAME

SET @SQLstr ='SELECT '+ CHAR(39)+'INSERT INTO '+@tbNAME+' ('+@tbColumn+') VALUES ( ''+'+@tbColumnforVal+' +'')'' FROM '+@tbNAME

EXEC(@SQLstr)

SET @tbID = @tbID + 1

END

DROP TABLE #Tables

END

4- Execute store procedure with “Result to Text” option

USE AdventureWorks

EXEC proc_CreateInsertStatmentsBySchema 'Person'

5- Results