Switching of a table

Question

Post reply

Switching of a table

Eshwaran

SSC Veteran

Points: 229
More actions
December 12, 2006 at 9:58 pm

#171224

Hi,
Can any one Guide me the following is my requirement. I Have to automate the following things.
The load happends from a flat file to temp table after certain period the Table has to be Renamed and change the primary key & Create the index on newly created tables and the newly created table name has to added in the the view.Re-Creating a Temporary again.
Here is my Approach:
Created a job
1. Load a data from flat file to table
2. Renaming the existing table
3. Drop the existing primary key & Re-create a new one in the renamed table
4. Creation of the Index
5. Re-Creating a temp table again for the load.

Is there any better approach is availble to carry out the same.

With Regards,
Eshwar

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Instead of renaming the PK...you can create the pk dynamically adding the datetime suffix to its name...

DECLARE @sql Varchar(4000)

, @Date Varchar(20)

SELECT @Date = '_'+Convert(Char(8), getdate(),112)+ Replace( Convert( char(10),getdate(),108),':','')

SELECT @sql = ''

SELECT @sql = 'ALTER TABLE [dbo].[Test_new] WITH NOCHECK ADD

CONSTRAINT [PK_test'+@Date+'] PRIMARY KEY CLUSTERED

([col1]) WITH FILLFACTOR = 100 ON [PRIMARY]'

exec (@SQL)

MohammedU
Microsoft SQL Server MVP

Eshwaran SSC Veteran Points: 229 More actions · Answer 2

Thanks !!!

Let me check this.

Remaning Steps what ever iam performing is the best method available ?

Wht do you feel this. Renaming of table can be done only with sp_rename is there any other method is available.

MohammedU SSCoach Points: 19644 More actions · Answer 3

I don't see any issue with it...

you can use sp_rename proc.

MohammedU
Microsoft SQL Server MVP

Eshwaran SSC Veteran Points: 229 More actions · Answer 4

Eshwaran

SSC Veteran

Points: 229

December 13, 2006 at 10:18 pm

#677991

Hi Udin,

Thanks for the help i have Done it.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

There is an issue with using sp_rename. It does not trigger a DDL event. So if you have Database or Server Triggers that are watching for DDL events, then you will miss that this event occurred.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Eshwaran SSC Veteran Points: 229 More actions · Answer 6

Robert,

Yes you r right i had a issue with sp_rename.

I executed that part in a seprate step in a job. So that was not a problem.

MohammedU SSCoach Points: 19644 More actions · Answer 7

Thanks Robert good point...

I didn't read it before...

I think DDL trigger only works for create and alter statements...sp_rename uses " EXEC %%Object(ID = @objid).SetName(Name = @newname)" statement to rename the table so that DDL can't capture it...

MohammedU
Microsoft SQL Server MVP

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

It works for Drop statements as well. I think they really missed the boat by not making rename part of the Alter Table statment.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

MohammedU SSCoach Points: 19644 More actions · Answer 9

Yes..

sp_rename is not using any DDL statements so that it is not covered.. May be Next Version may cover it..

MohammedU
Microsoft SQL Server MVP

MohammedU SSCoach Points: 19644 More actions · Answer 10

Yes..

sp_rename is not using any DDL statements so that it is not covered.. May be Next Version may cover it..

MohammedU
Microsoft SQL Server MVP