Create an extra table (copy the columns and new table) for every existing table

[sauronbrad]

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:

USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_NAME = 'sales'

    SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''


    SELECT * INTO @AUDIT_TABLE
    FROM @TABLE_NAME

    TRUNCATE TABLE @AUDIT_TABLE
    ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime  Char(50),AuditUser Char(50)


    SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME> @TABLE_NAME
    AND TABLE_TYPE= 'BASE TABLE'
    AND TABLE_NAME!= 'sysdiagrams'
    AND TABLE_NAME!= 'Audit'
    AND TABLE_NAME NOT LIKE 'audit%'

END

Thanks. ..

[manoshailu]

hi,

Your code is right but u had used the @TABLE_NAME AND @AUDIT_TABLE. Both the variables are not the Table type so it wont allow to create the table to your requirement. Store the query in a string variable and have to execute.

Try the Below Coding:

DECLARE @TABLE_NAME  SYSNAME
	DECLARE @AUDIT_TABLE  VARCHAR(50)
	DECLARE @SQL VARCHAR(100)
	
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
	WHERE
	TABLE_TYPE= 'BASE TABLE'
	AND TABLE_NAME NOT LIKE 'audit%'
	AND TABLE_NAME!= 'sysdiagrams'
	AND TABLE_NAME!= 'Audit'
	AND TABLE_NAME = 'sales'

	WHILE @TABLE_NAME IS NOT NULL
	BEGIN
		
		SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
		WHERE TABLE_NAME> @TABLE_NAME
		AND TABLE_TYPE= 'BASE TABLE'
		AND TABLE_NAME!= 'sysdiagrams'
		AND TABLE_NAME!= 'Audit'
		--AND TABLE_NAME = 'sales'
		
		SET @AUDIT_TABLE = 'Audit'+   @TABLE_NAME
		SELECT @AUDIT_TABLE	
		SELECT @TABLE_NAME

		---Drop THE TABLE IF EXISTS
		SET @SQL ='DROP TABLE ' + @AUDIT_TABLE
		EXEC (@SQL)

		--CREATE THE STRUCTURE OF THE TABLE FROM THE ANOTHER TABLE
		SET @SQL=  'SELECT * INTO  ' + @AUDIT_TABLE + ' FROM ' + @TABLE_NAME
		EXEC (@SQL)

		--REMOVE THE RECORDS IF EXISTS
		SET @SQL= 'TRUNCATE TABLE ' + @AUDIT_TABLE
		EXEC (@SQL)

		--ADD COLUMNS TO THE TABLE
		SET @SQL=	 'ALTER TABLE ' + @AUDIT_TABLE + ' ADD UserAction Char(10),AuditStartTime  Char(50),AuditUser Char(50)'
		EXEC (@SQL)
		
		
		SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
		WHERE TABLE_NAME> @TABLE_NAME
		AND TABLE_TYPE= 'BASE TABLE'
		AND TABLE_NAME!= 'sysdiagrams'
		AND TABLE_NAME!= 'Audit'
		AND TABLE_NAME NOT LIKE 'audit%'
		

	
	END

Have a nice Day
:) Shailaja:)