I would not use a number for the name of a table in SQL server. I have seen a bug involved with doing this.The following code will give you table names like Jan_f_policy, Feb_f_policy.You will need to replace "DateColumn" in the code below with whatever your own column name is. You may also need to modify '1/1/2003' depending upon the dates you are working with. NOTE: If f_policy contains more than a year's worth of data, this will put all data from January into the first table regardless of which year it is in. You may also have to make modifications if you are not using SQL 2000. Let me know if you have any questions.DECLARE @intMonth integerDECLARE @vchSQL varchar(2000)DECLARE @vchMonth varchar(3)SET @intMonth = 1SET @vchMonth = DATENAME(mm, DATEADD(mm, @intMonth - 1, '1/1/2003'))WHILE (@intMonth < 13)BEGIN SET @vchSQL = 'SELECT * INTO ' + @vchMonth + '_f_policy FROM f_policy WHERE MONTH(DateColumn) = ' + CAST(@intMonth AS varchar(2)) EXECUTE (@vchSQL) SET @intMonth = @intMonth + 1 SET @vchMonth = DATENAME(mm, DATEADD(mm, @intMonth - 1, '1/1/2003'))END
How can i rename a table using SQL
Posted - 2003-05-26 : 10:14:01
