MS SQL

MS SQL RSS

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

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Mar 2008

Posts: 1

Reputation: sauronbrad is an unknown quantity at this point

Solved Threads: 0

sauronbrad sauronbrad is offline

Offline

Newbie Poster

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

Mar 28th, 2008

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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. ..

•

Join Date: Jun 2007

Posts: 95

Reputation: manoshailu is an unknown quantity at this point

Solved Threads: 9

manoshailu manoshailu is offline

Offline

Junior Poster in Training

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

Apr 10th, 2008

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
	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
	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