>Generate Tables Data Script


>

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) 

AS 

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) 
AS 
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
go

CREATE PROCEDURE [dbo].[proc_CreateInsertStatmentsBySchema]
(
      — Add the parameters for the function here
      @schemaName VARCHAR(50)
)
AS

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.nameFROM 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

INSERT INTO [Person].[Address]  ([AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]) 

          VALUES ( 32514,N‘9882 Clay Rde’,NULL,N‘Redmond’,79,N‘98052’,‘B89DE861-DF93-4E67-A743-C9522BDE4D44’,‘Feb 19 1999 12:00AM’)
INSERT INTO [Person].[Address]  ([AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]) 
          VALUES ( 32515,N‘5050 Mt. Wilson Way’,NULL,N‘Kenmore’,79,N‘98028’,‘C18D71F8-74C0-4A0F-945D-D2B8F2A545BB’,‘Mar 25 1999 12:00AM’)
INSERT INTO [Person].[Address]  ([AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]) 
          VALUES ( 32516,N‘6891 Ham Drive’,NULL,N‘Redmond’,79,N‘98052’,‘2DBF3412-6E46-4415-BB46-8BC983E29E4A’,‘Mar 20 1999 12:00AM’)
INSERT INTO [Person].[Address]  ([AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]) 
          VALUES ( 32517,N‘177 11th Ave’,NULL,N‘Sammamish’,79,N‘98074’,‘2F76ECD8-B22C-4353-BE61-09FE000E13CE’,‘Jan 30 1999 12:00AM’)
INSERT INTO [Person].[Address]  ([AddressID],[AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[rowguid],[ModifiedDate]) 
          VALUES ( 32518,N‘8040 Hill Ct’,NULL,N‘Redmond’,79,N‘98052’,‘0E5F2D4F-C38F-406C-9169-8CCD754DBF94’,‘Feb 20 1999 12:00AM’)
Advertisements

Posted on September 29, 2009, in Sql Server 2005, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: