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

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 361,950 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,606 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 908 | Replies: 1

•

Join Date: Mar 2008

Posts: 1

Reputation: sauronbrad is an unknown quantity at this point

Rep Power: 0

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

(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

Thanks. ..

•

Join Date: Jun 2007

Posts: 62

Reputation: manoshailu is an unknown quantity at this point

Rep Power: 2

Solved Threads: 4

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