Create tables with new names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create tables with new names

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-08-29 : 06:40:14

Hi all i have the following code which works.

IF EXISTS(SELECT name FROM sys.tables
WHERE name = 'tblsourcenew')
BEGIN
PRINT 'tblsourcenew'
DROP TABLE T_old
EXEC sp_rename 'tblsourcenew', 'tblsourcenew_old'
END
ELSE PRINT 'tblsourcenew'

fine is there a way to rename the file with a unique name automatically.

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:46:41

Yes.
The real question is "Why?".

E 12°55'05.25"
N 56°04'39.16"

petek
Posting Yak Master

192 Posts

Posted - 2008-08-29 : 06:51:30

i have data imported from different sources and i need to keep the old table for tracking, and when data is manipulated on the old table it needs to be archived with a new name

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 06:57:01

[code]DECLARE @SQL VARCHAR(1000),
@SourceTableName VARCHAR(200),
@TargetTableName VARCHAR(200)

SELECT @SourceTableName = 'tblSourceOld',
@TargetTableName = 'tblSourceNew'

SET @SQL = 'DROP TABLE ' + QUOTENAME(@TargetTableName)
EXEC (@SQL)

SET @SQL = 'SELECT TOP 0 * INTO ' + QUOTENAME(@TargetTableName) + ' FROM ' + QUOTENAME(@SourceTableName)
EXEC (@SQL)

SET @SQL = 'INSERT ' + QUOTENAME(@TargetTableName) + ' SELECT * FROM ' + QUOTENAME(@SourceTableName)
EXEC (@SQL)

SET @SQL = 'TRUNCATE TALBE ' + QUOTENAME(@SourceTableName)
EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 07:07:49

How do you get data to tblSourceNew?
You can easily truncate tblSourceOld and populate data to it without having tblSourceNew

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources