USE [master]
GO
IF OBJECT_ID('dbo.sp_Insert') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_Insert AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_Insert
@Table SYSNAME = NULL,
@NewTableName SYSNAME = NULL,
@IncludeColumnList VARCHAR(MAX) = NULL,
@ExcludeColumnList VARCHAR(MAX) = NULL,
@TABLOCK BIT = 1,
@MaxColumnsInARow INT = 100
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @MaxColumnsInARow = CASE WHEN ISNULL(@MaxColumnsInARow, 0) < 10 THEN 100
ELSE @MaxColumnsInARow
END
SET @Table = LTRIM(RTRIM(@Table))
DECLARE @ObjectId INT = OBJECT_ID(@Table)
IF @ObjectId IS NULL
BEGIN
PRINT 'RETURN'
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Insert',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT 'GO
'
END
DECLARE @DatabaseName SYSNAME = CASE WHEN @Table LIKE '#%' THEN 'tempdb'
ELSE ISNULL(PARSENAME(@Table, 3), DB_NAME())
END
DECLARE @SQL VARCHAR(MAX)
DECLARE @MergeList TABLE ([TableName] VARCHAR(256) NOT NULL,
[WhereList] VARCHAR(256) NULL,
[InsertList] VARCHAR(256) NULL,
[SelectList] VARCHAR(256) NULL,
[key_ordinal] TINYINT NULL,
[column_id] SMALLINT NOT NULL)
IF OBJECT_ID('tempdb.#Incl') IS NOT NULL
DROP TABLE #Incl
SELECT LTRIM(RTRIM(PARSENAME(Field, 1))) AS ColumnName
INTO #Incl
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@IncludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
IF OBJECT_ID('tempdb.Excl') IS NOT NULL
DROP TABLE #Excl
SELECT LTRIM(RTRIM(PARSENAME(Field, 1))) AS ColumnName
INTO #Excl
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@ExcludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''
INSERT @MergeList
EXEC ('SELECT
CASE WHEN t.name LIKE ''#%'' THEN t.name ELSE ''['' + s.name + ''].['' + t.name + '']'' END AS TableName,
CASE WHEN ic.column_id IS NOT NULL THEN ''t1.['' + c.name + ''] = @'' + c.name
ELSE NULL
END AS WhereList,
''['' + c.name + '']'' AS InsertList,
''t1.['' + c.name + '']'' AS SelectList,
ic.key_ordinal,
c.Column_id
FROM ' + @DatabaseName + '.sys.schemas s (NOLOCK)
INNER JOIN ' + @DatabaseName + '.sys.tables t (NOLOCK) ON s.schema_id = t.schema_id
INNER JOIN ' + @DatabaseName + '.sys.columns c (NOLOCK) ON c.object_id = t.object_id
LEFT OUTER JOIN ' + @DatabaseName + '.sys.indexes i (NOLOCK) ON i.object_id = t.object_id
AND i.is_primary_key = 1
LEFT OUTER JOIN ' + @DatabaseName + '.sys.index_columns ic (NOLOCK) ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
WHERE t.OBJECT_ID = OBJECT_ID(''' + @Table + ''')
AND (ic.key_ordinal IS NOT NULL
OR (ic.key_ordinal IS NULL
AND (NOT EXISTS ( SELECT *
FROM #incl )
OR EXISTS ( SELECT *
FROM #incl
WHERE columnname = c.NAME ))
AND (NOT EXISTS ( SELECT *
FROM #Excl )
OR NOT EXISTS ( SELECT *
FROM #Excl
WHERE columnname = c.NAME ))))
ORDER BY 1,
c.column_id')
SELECT @SQL = (SELECT 'INSERT ' + ISNULL(@NewTableName, Util.dbo.GetTempTableName(TableName)) + CASE WHEN @TABLOCK = 1 THEN ' WITH (TABLOCK)'
ELSE ''
END + ' ('
+ Util.dbo.StringConcat(CASE WHEN @MaxColumnsInARow > 1
AND Column_id % @MaxColumnsInARow = 1 THEN '
' ELSE ''
END + InsertList, ', ') + ')
SELECT /*
' + Util.dbo.StringConcat(CASE WHEN @MaxColumnsInARow > 1
AND Column_id % @MaxColumnsInARow = 1 THEN '
' ELSE ''
END + SelectList, ', ') + '
*/
' + Util.dbo.StringConcat(SelectList, ',
') + '
FROM ' + ISNULL(@NewTableName, Util.dbo.GetTempTableName(TableName)) + ' t1
' + ISNULL('WHERE ' + Util.dbo.StringConcat(WhereList, ' AND '), '')
FROM @MergeList
GROUP BY TableName) + '
' + ISNULL('-- /* Primary Key */ ' + (SELECT Util.dbo.StringConcat(CASE WHEN @MaxColumnsInARow > 1
AND Column_id % @MaxColumnsInARow = 1 THEN '
' ELSE ''
END + SelectList, ', ')
FROM (SELECT TOP 999999
SelectList,
Column_id
FROM @MergeList
WHERE key_ordinal IS NOT NULL
ORDER BY key_ordinal) KK), '')
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_Insert
GO