Tuesday, September 29, 2009

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')