Alter table name
-
I need to rename a group of tables to a new name which is a variable each month to archive the monthly data to recreate the report if needed. Is there a T-SQL statement to change the table name to @filename?
Is this even a good approach to archive tables for monthly historical data?
-
If you have a inventory database with the following structure
InvID int, qty real....
Make a warehouse type table that dimensions this information by date
so the new table will be InventoryByDate
RecordDate datetime, InvID int, qty real......
with RecordDate,InvID as the primary key
Use a procedure to copy the inventory data into the InventorybyDate table at intervals when reporting (archiving) needs to be done.
Not sure what your application is but this gives you the idea
-
Not really what I need to do. What is needed is to create a complete set of monthly data with the same table names adding the date to the table name. To rerun a historical report they could rename the tables (3) by removing the data portion.
What I was trying to do.
Declare @filename char(50)
Set @filename = 'report1 - Cycle ' + cast(month(getdate())as varchar)
CREATE TABLE @filename
([Store Nbr] int NOT NULL,
UPC varchar(255),
[Item Nbr] int,
[Item Flags] varchar(255),
[Item Desc] varchar(255),
[Size Desc] varchar(255),
[Item Status] varchar(255),
[Range 1 POS Sales] money,
[Range 2 POS Sales] money);
What is missing is the syntax to use the filename as the real table name.
Or I can just rename the existing data tables to this variable name.
-
Guess what I was trying to tell you without saying it is thats not really a good to do it.
but in case that's how you really want to go....
Declare @filename char(50)
Declare @sql as varchar(1000)
Set @filename = 'report1 - Cycle ' + cast(month(getdate())as varchar)
Set @sql ='CREATE TABLE ' + @filename +'
([Store Nbr] int NOT NULL,
UPC varchar(255),
[Item Nbr] int,
[Item Flags] varchar(255),
[Item Desc] varchar(255),
[Size Desc] varchar(255),
[Item Status] varchar(255),
[Range 1 POS Sales] money,
[Range 2 POS Sales] money);'
Exec(@SQL)
will do it
Using the table structure:
([ReportDate Datetime NOT NULL,
[Store Nbr] int NOT NULL,
UPC varchar(255),
[Item Nbr] int,
[Item Flags] varchar(255),
[Item Desc] varchar(255),
[Size Desc] varchar(255),
[Item Status] varchar(255),
[Range 1 POS Sales] money,
[Range 2 POS Sales] money);
will allow all your data to be in one table then just change parameters (date) to get the info rather than table name
-
thanks. that is what I was looking for. Can't use one table. The idea is scripts recreate all data for the report from the three base tables. I would need to create a new set of scripts to extract data from one table for a specific month. And the tables all have about 2 million rows in them. They have a new server with 500 gig of storage so the room is not an issue.
-
Then entire process may need to run over. (sometimes they correct the monthly data after sending reports) But when I try to use the same syntax you suggested I get an error "invalid column name 'report1 - Cycle 6'
This is what I am trying to do, to first check then remove the new filename prior to attempting to create it.
Declare @filename char(50)
Declare @sql as varchar(1000)
Set @filename = '['+'report1 - Cycle ' + RTrim(cast(month(getdate())as char))+']'
Set @sql ='IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = '+ @filename +')
BEGIN
DROP TABLE ' + @FILENAME+ '
END'
EXEC @sql
-
Could you not just use the system procedure to rename the current tables to the archive name:
EXEC sp_rename 'customers', @filename
and then just recreate the original tables using CREATE TABLE. This would be much quicker than trying to copy over 2 million rows at a time.
-
Thanks Dan, that is exactly what I was looking for.
-
I use the following stored procedure for monthly rename of any reporting tables:
CREATE PROCEDURE sp_monthly_rename AS
begin
declare @newname varchar(55)
set nocount on
select @newname = 'TableName' +
substring(CONVERT(varchar(8), dateadd(day,-1, getdate()),12),3,4) + substring(convert(varchar(20),getdate()),10,2)
exec sp_rename TableName, @newname
set nocount off
end
GO
-
I now find this problem with changing the name. The filname is altered just fine. The table structure is fine. The indexes are lost. But worse yet the data count is the same, but trying to open the table yields an ODBC error: invalid object name. When renaming back to the original filename the data is viewed fine from Enterprise Manager
-
Double check in EM whether dbo is the table owner or user. When you create the new table you lost not only indices, but table owner too. -
I just found the problem. I added the delimiters [] to the table name. It did not like this. But without them it works fine.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply