Generate SQL Insert Statements with Indentation


Howdy Everyone.

Generating SQL insert statements for a given table is always a tedious task in SQL Server, Mostly the automated generated scripts are not indented with spaces, so that it will be easier to read and most of all it will be easy to change a column values or remove column values.

The below code will give you the insert statements along with the indentation of the code.

 

<pre>IF EXISTS ( SELECT 1 FROM sys.procedures WHERE name = 'InsertGenerator')
    DROP PROCEDURE InsertGenerator

GO

CREATE PROCEDURE InsertGenerator
(
    @TableName  VARCHAR(100)
)
AS
BEGIN
    DECLARE @IndentationTable   TABLE
    (
        ColumnName      VARCHAR(500)
        ,MaxLength      BIGINT
    )

    DECLARE @SQL        NVARCHAR(MAX)
    DECLARE @Insert     NVARCHAR(MAX)
    DECLARE @SQ         VARCHAR(4)
    DECLARE @SSQ        VARCHAR(4)
    DECLARE @ColumnName VARCHAR(300)
    DECLARE @DataType   VARCHAR(50)
    DECLARE @MaxLength  BIGINT

    SET @SQ     = ''''
    SET @SSQ    = ''''''''''

    BEGIN TRY
        
        DECLARE maxLength CURSOR
        FOR
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 

        DECLARE Cols CURSOR
        FOR
            SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 


        OPEN maxLength
        FETCH NEXT FROM maxLength INTO @ColumnName
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @SQL = 'SELECT ' + @SQ +  @ColumnName + @SQ + ' ColumnName, MAX(LEN('+@ColumnName+')) MaxLength FROM '  + @TableName  + ' '
            INSERT INTO @IndentationTable (ColumnName,MaxLength)
            EXEC sp_executeSQL @SQL
            FETCH NEXT FROM maxLength INTO @ColumnName
        END
        CLOSE maxLength
        DEALLOCATE maxLength

        UPDATE @IndentationTable         
        SET MaxLength = MaxLength + 8

        SELECT @Insert =  'SELECT ' + @SQ + 'INSERT INTO ' + @TableName + '(' + (SELECT STUFF( (SELECT ',' + ColumnName FROM @IndentationTable FOR XML PATH ('')),1,1,'')) + ' ) VALUES ( ' + @SQ + '+'
        SET @SQL = @Insert
        OPEN Cols
        FETCH NEXT FROM Cols INTO @ColumnName,@DataType
        WHILE (@@FETCH_STATUS = 0)
        BEGIN


            SELECT  @MaxLength = MaxLength 
            FROM    @IndentationTable
            WHERE   ColumnName = @ColumnName
            
            
                        
            SELECT @SQL = @SQL + CASE  
                                    WHEN @DataType IN ('BIGINT','BIT','DECIMAL','INT','MONEY','NUMERIC','SMALLINT','SMALLMONEY','TINYINT','FLOAT','REAL')   THEN   ' CAST( ' + @ColumnName + ' AS VARCHAR) '
                                    WHEN @DataType IN ('DATE','DATETIME2','DATETIME','DATETIMEOFFSET','SMALLDATETIME','TIME') THEN @SQ + 'CAST( ' + @SQ + '+' + @SSQ + '+' + 'CAST( ' + @ColumnName + ' AS VARCHAR ) ' + '+' + @SSQ + '+' + @SQ + ' AS ' + @DataType + ' ) ' + @SQ
                                    WHEN @DataType IN ('CHAR','VARCHAR','TEXT') THEN @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    WHEN @DataType IN ('NCHAR','NVARCHAR','NTEXT') THEN 'N' + @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    ELSE   CAST(@ColumnName AS NVARCHAR)
                                  END  +  '+' + 'REPLICATE( ' + @SQ+ ' ' +@SQ + ','+CAST(ISNULL(@MaxLength,20) AS VARCHAR) + '-LEN(' + @ColumnName + '))' +     '+'  + @SQ +',' + @SQ + '+'

            FETCH NEXT FROM Cols INTO @ColumnName,@DataType    
        END
        CLOSE Cols
        DEALLOCATE Cols

        SET @SQL = LEFT(@SQL,LEN(@SQL)-LEN('+'  + @SQ +',' + @SQ +  '+'))
        SELECT @SQL = @SQL + '+' + @SQ + ')' + @SQ +  ' FROM ' +  @TableName
        select @SQL 
        EXEC sp_executeSQL @SQL

    END TRY
    BEGIN CATCH
        SELECT CAST(ERROR_LINE() AS VARCHAR) + '    ' + ERROR_MESSAGE() + '   AT ' + CAST(ERROR_LINE() AS varchar)
    END CATCH
END

GO

To execute this code you have to give the stored proc name followed by the table name.

Example:
EXEC InsertGenerator <table_name>

Hope this helps you guys in generating a better and readable code.