Dynamic generating insert script with given condition and with only required column for given table

Hi Folks,

Today I am going to share a script by which we can dynamically create insert script with only needed columns and only needed data.
In simple words we can avoid unwanted data, even we can avoid unwanted column from insert list.

Here is example table and insert data:
***** you can directly use script at any point or any situation, it is always safe and did not harm any data or any definition  *****

CREATE TABLE [dbo].[Calender](
    [years] [varchar](5) NULL,
    [MONTHS] [varchar](5) NULL,
    [salesAmount] [int] NULL,
    [avrgsaleperMonth] [float] NULL
)

GO

INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jan’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’FEB’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Mar’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Apr’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’May’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jun’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jul’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Aug’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Sep’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’OCT’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’NOV’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’DEC’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jan’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’FEB’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Mar’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Apr’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’May’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jun’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jul’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Aug’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Sep’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’OCT’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’NOV’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’DEC’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jan’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’FEB’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Mar’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Apr’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’May’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jun’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jul’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Aug’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Sep’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’OCT’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’NOV’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’DEC’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jan’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’FEB’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Mar’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Apr’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’May’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jun’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jul’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Aug’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Sep’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’OCT’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’NOV’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’DEC’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jan’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’FEB’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Mar’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Apr’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’May’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jun’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jul’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Aug’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Sep’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’OCT’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’NOV’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’DEC’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′

Now this is the script for generate script of what we want excluding all unwanted column and with only required as we can pass these values in our parameters(@NonIncludeColumnName, @Condition)
as describe below just execute the script and you got the insert script of newly created table (Calender) with only where month = Jan and AvrgSalePerMonth is not null,
And with exclude the column AvrgSalePerMonth and month.

Script :

DECLARE
/***** TableName ****/
@TableName SYSNAME =’Calender’
/***** SchemaName ****/
,@SchemaName SYSNAME =’dbo’ 
/****** JUST PASS COMMA SEPRATED VALUES OF ALL DON’T WANT TO INCLUDE  COLUMN ********/
,@NonIncludeColumnName varchar(250) = ‘avrgsaleperMonth,months’
/***** Where Condition/left blank(”) if don’t want any filtering  ****/
,@Condition VARCHAR(500) = ‘[MONTHS]=”Jan” and avrgsaleperMonth is not null’

    DECLARE @ColumnList VARCHAR(max)
    DECLARE @InsertList VARCHAR(max)
    DECLARE @pkList VARCHAR(max)
    DECLARE @SQL varchar(max)
    DECLARE @WhereSQL varchar(max)
    SELECT @ColumnList = ”
    SELECT @InsertList = ”
    SELECT @pkList  = ” 

/************** Collect NonIncluding Columns **************************/
DECLARE @delimiter nchar(1) = ‘,’

DECLARE @result TABLE
    ([id] int NOT NULL
    ,[value] nvarchar(4000))

IF ISNULL(@NonIncludeColumnName, ”) = N” OR ISNULL(@delimiter, N”) = N”
       OR ISNULL(CHARINDEX(@delimiter, @NonIncludeColumnName, 1), 0) = 0
    BEGIN
        — No valid delimiter and/or csv, so return only the csv value as it.
        INSERT INTO @result ([id], [value]) VALUES (1, @NonIncludeColumnName);
        RETURN;
    END;
    — Csv must end with the delimiter:
    IF LEN(@NonIncludeColumnName) < 4000 AND RIGHT(@NonIncludeColumnName, 1) <> @delimiter
        SET @NonIncludeColumnName = @NonIncludeColumnName + @delimiter;
    — Split the csv value with a CTE
    WITH cte ([id], [value], [leftCsv])
    AS (SELECT 1 as [id]
              ,SUBSTRING(@NonIncludeColumnName
                        ,1
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) – 1) AS [value]
              ,SUBSTRING(@NonIncludeColumnName
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) + 1
                        ,LEN(@NonIncludeColumnName)) AS [leftCsv]
        UNION ALL
        SELECT cte.[id] + 1 AS [id]
              ,SUBSTRING(cte.[leftCsv]
                        ,1
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) – 1) AS [value]
              ,SUBSTRING(cte.[leftCsv]
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) + 1
                        ,LEN(cte.[leftCsv])) AS [leftCsv]
        FROM cte
        WHERE CHARINDEX(@delimiter, cte.[leftCsv], 1) <> 0)
    INSERT INTO @result ([id], [value])
    SELECT [id], [value]
    FROM cte
    OPTION (MAXRECURSION 4000);

/************** END of Collect NonIncluding Columns **********************/
/**************  Collect all column name for the table ******************/
    SELECT @ColumnList = @ColumnList + CASE 
                                       WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’)
                                       THEN Quotename(SC.name) + ‘,’ 
                                       ELSE ” 
                                       END 
     — Collect all insert statments for the table                                 
          ,@InsertList = @InsertList + CASE WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’) THEN
                                        ”” +  QUOTENAME(sc.name) + ‘=” + ‘  + ‘CASE WHEN ‘ + Quotename(SC.name) 
                                        + ‘ IS NULL THEN ”NULL” ELSE  ”””” +    REPLACE(CONVERT(VARCHAR(MAX),’ + Quotename(SC.name) + ‘),””””,””””””) + ””””  END + ‘ + ‘ ”,” + ‘
                                       ELSE ”
                                       END
      FROM SYS.objects so
      JOIN SYS.columns sc
        ON so.object_id = sc.object_id
      JOIN SYS.types st
        ON sc.user_type_id = st.user_type_id
       AND sc.system_type_id = st.system_type_id
      JOIN SYS.schemas sch
        ON sch.schema_id = so.schema_id
     WHERE SO.name = @TableName 
       AND sch.name = @SchemaName
       AND sc.name  IN ( SELECT Value FROM @result )    /********************Which Column Need To Remove**********************/
     ORDER BY SC.column_id
/******************** ADD WHERE CONDITION **********************/
    SELECT @WhereSQL = CASE 
                       WHEN LTRIM(RTRIM(@Condition)) <> ” THEN ‘ WHERE ‘ + @Condition
                       ELSE ”
                       END
    SELECT @ColumnList =SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)
    SELECT @InsertList =SUBSTRING(@InsertList,1,LEN(@InsertList)-4) + ””
   — combine all variable and generate the final list
    SELECT @SQL = ‘SELECT ”INSERT INTO ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) +  char(10)+  ‘(‘ + @ColumnList + ‘)” + char(10)+  ”SELECT ” + ‘ + @InsertList 
                + ‘ FROM ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) 
                + @WhereSQL
/******************** EXECUTE THE STATEMENT **********************/
     –PRINT @sql
    EXEC(@SQL)
/******************** END OF SCRIPT **********************/
For removing the table. from database just execute below given drop table statement.
Drop Table TABLE [dbo].[Calender]

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Advertisements

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