Copy Table Contents

USE Budget
GO

/*

DROP TABLE #columnsPerTable

*/

SET NOCOUNT ON

DECLARE @iter INT = 0, @iterCol INT = 0, @tableCount INT = 0, @columnCount INT = 0

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @delete VARCHAR(1024) = ' DELETE '
, @select VARCHAR(1024) = ' SELECT '
, @from VARCHAR(1024) = ' FROM '
, @where VARCHAR(1024) = ' WHERE FiscalYear = @fromYear'
, @tableName VARCHAR(1024) = ''
, @columnName VARCHAR(1024) = ''
, @columnString VARCHAR(8000) = ''
, @comma CHAR(2)= ', '

-- CREATE TABLE #tables (
DECLARE @tables TABLE (
ident INT IDENTITY (1, 1)
, TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
)

-- CREATE TABLE #columns (
DECLARE @columns TABLE (
TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

CREATE TABLE #columnsPerTable (
-- DECLARE #columnsPerTable TABLE (
ident INT IDENTITY (1, 1)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

INSERT @tables (
TABLE_SCHEMA
, TABLE_NAME
)
SELECT DISTINCT
t.TABLE_SCHEMA
, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = t.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = 'Budget'
AND t.TABLE_SCHEMA = 'dbo'
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT LIKE '%bak%'
AND c.COLUMN_NAME LIKE '%fisc%'

-- # of tables
SELECT @tableCount = @@ROWCOUNT

INSERT @columns (
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
)
SELECT t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
FROM @tables t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME

WHILE (@iter < @tableCount)
BEGIN

SELECT @tableName = TABLE_NAME
FROM @tables
WHERE ident = @iter + 1

INSERT #columnsPerTable (
TABLE_NAME
, COLUMN_NAME
)
SELECT TABLE_NAME
, COLUMN_NAME
FROM @columns
WHERE TABLE_NAME = @tableName

SELECT @columnCount = @@ROWCOUNT

WHILE (@iterCol < @columnCount)
BEGIN

SELECT @columnString += '[' + COLUMN_NAME + ']' + @comma
FROM #columnsPerTable
WHERE ident = @iterCol + 1
AND COLUMN_NAME IS NOT NULL

SELECT @iterCol += 1

END

SELECT @columnString = LEFT (RTRIM(LTRIM(@columnString)), LEN(RTRIM(LTRIM(@columnString))) - 1)

SELECT @delete
+ ' '
+ @tableName
+ @where

SELECT @insert
+ @tableName
+ ' ( '
+ @columnString
+ ' ) '

SELECT @select
+ @columnString
+ @from
+ @tableName
+ @where

SELECT @columnCount = 0, @iterCol = 0, @columnString = ''
SELECT @iter += 1
DELETE #columnsPerTable
DBCC CHECKIDENT (#columnsPerTable, RESEED, 0)

END

/*

DROP TABLE #columnsPerTable

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @select VARCHAR(1024) = ' SELECT '
, @where VARCHAR(1024) = ' WHERE '
, @tableName VARCHAR(1024)
, @columnName VARCHAR(1024)
, @columnString VARCHAR(Max)
, @comma CHAR(2)= ', '

*/