Sql Sqerver : Generate Conditional Table Data Script


In earlier posts “Sql Server 2005:Generate Tables Data Script ” and “Sql Server 2008:Generate Data Script” we have discussed methods to generate table data script for schema based script for Sql Server 2005 and specific table’s data script for Sql Server 2008 respectively.

On request of few blog readers following script will generate table data script based on your given conditions. This script is useful to generate script for only required records of a targeted table data and not the whole data for a table.
Following are four steps to achieve our goal.
1.       Create function to get all columns name of targeted table
2.       Create function to get values for all columns of targeted table
3.       Create a store procedure, to group our queries for future use.
4.       Execute store procedure with following parameters
a.       Schema Name
b.      Table Name
c.       Condition with WHERE clause
————————————————–
–STEP (1)
————————————————–
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 
————————————————–
–STEP (2)
————————————————–
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’
                OR @typeName = ‘date’
                OR @typeName = ‘time’
                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 
————————————————–
–STEP (3)
————————————————–
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateInsertStatmentsBySchema]
    (
      — Add the parameters for the function here
      @SchemaName VARCHAR(50),
      @TableName VARCHAR(50),
      @Condition VARCHAR(2000)
    )
AS
    BEGIN
        DECLARE @tbID INT,
            @tbNAME VARCHAR(50),
            @tbColumn VARCHAR(4000),
            @tbColumnforVal VARCHAR(4000),
            @SQLstr VARCHAR(4000),
            @label VARCHAR(500),
            @Count INT
        SELECT  @tbNAME = ‘[‘ + sys.schemas.name + ‘].[‘ + sys.objects.name
                + ‘]’,
                @tbColumn = dbo.fnc_GetColumnsByCommas(@schemaName,
                                                       sys.objects.name),
                @tbColumnforVal = 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
                AND sys.objects.name = @TableName
        SET @SQLstr = ‘SELECT ‘ + CHAR(39) + ‘INSERT INTO ‘ + @tbNAME + ‘  (‘
            + @tbColumn + ‘)  VALUES ( ”+’ + @tbColumnforVal
            + ‘ +”)” FROM ‘ + @tbNAME + @Condition
        EXEC ( @SQLstr
            )
    END
————————————————–
STEP (4)Execute store procedure with “Result to Text” option

————————————————–  
    Use AdventureWorks
    EXEC [dbo].[proc_CreateInsertStatmentsBySchema] ‘HumanResources’,
        ‘Department’, ‘ WHERE DepartmentID <10'
Advertisements

Posted on December 27, 2010, in 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: