SQL Server - Generate data script for a table, conditionally

While Data migration or Project Release, we use Generate data script frequently. For taking whole data means we can use “Tasks -> Generate Scripts” Option, provided by SQL. But if we want to generate data script for a specific data rows than, there is no option in SQL to do this.

After a little bit of googling i find out a link, that has a procedure to facilitate generate data script, But problem is we can’t generate data script conditionally (Again the same problem).

But this time we have a source, the procedure formed by dynamic query. I have added a little bit of stuff to conditional table data script Generation.

CREATE PROC [dbo].[InsertGenerator]

(

    @tableName            VARCHAR(100),

    @whereCondition        VARCHAR(MAX) = NULL

)

AS

BEGIN

    --    Declare a cursor to get col name and 

    --    its data type for the specified table

    DECLARE cursCol CURSOR 

    FAST_FORWARD 

    FOR 

        SELECT 

            column_name, data_type 

        FROM information_schema.columns 

        WHERE table_name = @tableName

    -- OPEN CURSOR

    OPEN cursCol

    DECLARE 

        @string NVARCHAR(3000),        --for storing the first half of INSERT statement

        @stringData NVARCHAR(3000), --for storing the data (VALUES) related statement

        @dataType NVARCHAR(1000);    --data types returned for respective columns

    SET @string='INSERT '+@tableName+'('

    SET @stringData=''

    DECLARE @colName NVARCHAR(50)

    FETCH NEXT FROM cursCol INTO @colName,@dataType

    IF @@fetch_status <> 0

        BEGIN

            PRINT 'Table '+@tableName+' not found, processing skipped.'

            CLOSE curscol

            DEALLOCATE curscol

            RETURN

        END

    WHILE @@FETCH_STATUS=0

        BEGIN

            IF @dataType in ('varchar','char','nchar','nvarchar')

                BEGIN

                    SET @stringData 

                            = @stringData+''''+'''+isnull('''''+'''''+' 

                              + @colName + '+'''''+''''',''NULL'')+'',''+'

                END

            ELSE IF @dataType in ('text','ntext') --if the datatype is text or something else 

                BEGIN

                    SET @stringData 

                            = @stringData+'''''''''+isnull(cast(' 

                              + @colName + ' as varchar(2000)),'''')+'''''',''+'

                END

            ELSE IF @dataType = 'money' --because money doesn't get converted from varchar implicitly

                BEGIN

                    SET @stringData 

                            = @stringData+'''convert(money,''''''+isnull(cast(' 

                              + @colName + ' as varchar(200)),''0.0000'')+''''''),''+'

                END

            ELSE IF @dataType='datetime'

                BEGIN

                    SET @stringData

                            = @stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'

                              + @colName +',121)+'''''+''''',''NULL'')+'',121),''+'

                END

            ELSE IF @dataType='image' 

                BEGIN

                    SET @stringData 

                            = @stringData+'''''''''+isnull(cast(convert(varbinary,'

                              + @colName+') as varchar(6)),''0'')+'''''',''+'

                END

            ELSE --presuming the data type is int,bit,numeric,decimal 

            BEGIN

                SET @stringData 

                            = @stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'

                              + @colName + ')+'''''+''''',''NULL'')+'',''+'

            END

            SET @string= @string + @colName+','

            FETCH NEXT FROM cursCol INTO @colName,@dataType

        END

    DECLARE @Query nvarchar(4000)

    SET @query 

            = 'SELECT ''' + SUBSTRING(@string,0,len(@string)) + ') VALUES(''+ ' 

                + SUBSTRING(@stringData,0,LEN(@stringData)-2)+'''+'')'' FROM '

                + @tableName + ISNULL(' WHERE ' + @whereCondition, '')

    exec sp_executesql @query

    --select @query

    CLOSE cursCol

    DEALLOCATE cursCol

END

How to use:

USE [NorthWind]

GO

EXEC    [InsertGenerator]

        @tableName = N'Orders',

        @whereCondition = N'ShipCountry = ''USA'' AND EmployeeID = 3'

GO