Trouble copying data from old table to new table

[RelentlessMike]

Hello all,

I am working with a ColdFusion MX 6.1 frontend and a SQL Server 2000 backend -

There is a "CUSTOMER_INFO" table for every month and year stretching back
approximately 2 years, ie:

CUSTOMER_INFO_052005 (May 2005)
CUSTOMER_INFO_042005 (April 2005)
etc.

I don't really want to bore anybody with details as to why we have so many
CUSTOMER_INFO tables (though I can ramble on for hours about it if you'd like
me to).

I'm creating a scheduled job which will run on the first of every month and
copy last month's CUSTOMER_INFO table into a newly created CUSTOMER_INFO table
for the current month.

Creating the new table is not a problem. The problem is selecting data from
the table belonging to the month that just ended and putting it into the new
table.

Please examine the following code. I would very much appreciate it if
somebody could tell me what I am doing wrong:


/* Get a two character string to represent last month */

DECLARE @Old_Month char(2)
SET @Old_Month = DATEPART((month), GETDATE() -1)

IF @Old_Month < 10
SET @Old_Month = '0' + @Old_Month
GOTO Proc_Cont

Proc_Cont:


/* Get a four character string to represent whatever year last month was in */

DECLARE @Old_Year char(4)
SET @Old_Year = DATEPART((year), GETDATE()) -1)


/* Add the @Old_Month and @Old_Year variables to get a six character string
representing last month and whatever year last month was in */

DECLARE @Old_MonthYear char(7)
SET @Old_MonthYear = @Old_Month + @Old_Year


/* Prepend "CUSTOMER_INFO_" to the front of my 6 character month/year variable
*/

DECLARE @Old_CustomerInfo char(20)
SET @Old_CustomerInfo = 'CUSTOMER_INFO_' + @Old_MonthYear


/* Now why won't the following SELECT INTO statement work? (assuming that I
were to
run this code on 5/1/05) */

INSERT INTO CUSTOMER_INFO_052005
(CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED )
SELECT CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED
FROM @Old_CustomerInfo


/*And I've also tried the following: */

INSERT INTO CUSTOMER_INFO_052005
(CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED )
SELECT CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED
FROM 'CUSTOMER_INFO_' + CAST(DATEPART((month), GETDATE()-1) AS CHAR(2)) +
CAST(DATEPART((year), GETDATE()-1) AS CHAR(4))


/*And I've also tried this: */

INSERT INTO CUSTOMER_INFO_052005
(CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED )
SELECT CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED
FROM (SELECT name FROM sysobjects WHERE name = @Old_CustomerInfo)



Please keep in mind that right after I've declared and set my
@Old_CustomerInfo variable, if I run a PRINT @Old_CustomerInfo statement from
within Query Analyzer, the output is CUSTOMER_INFO_042005 which is correct
because I am running my code on 5/1 ( I have my computer's calendar turned
back).

I am guessing that since @Old_CustomerInfo is a CHAR variable somehow this is
causing me a problem but I'm really not sure what's going on. I would greatly
appreciate any help with this.

Thanks,
Mike

[jmj]

Your first insert statemen looks fine. You shouldn't need to do any convert
statements.
What error message are you receiving? You're completely sure that all the
fields are the same on both tables and that you didn't accidentally add
something like and identity column to your customerid

[RelentlessMike]

"...you didn't accidentally add something like and identity column to your
customerid "

Doh! I can't double-check this from where I am right now, but you may have
hit the nail on the head my friend.

Thanks very much,
Mike

[RelentlessMike]

Solved!

The problem actually was not related to an identity column. But after much
swearing and twitching at my desk I have found a solution and would like to
share it with anybody who is interested:

Instead of using this INSERT INTO query...

INSERT INTO CUSTOMER_INFO_052005
(CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED )
SELECT CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED
FROM @Old_CustomerInfo

...I needed to run the query using dynamic SQL:

DECLARE @Insert_Var char(200)
SELECT @Insert_Var = 'INSERT INTO CUSTOMER_INFO_052005
(CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED ) ' +
'SELECT CUSTOMER_ID,CUSTOMER_NAME,BILLABLE,SIZE_ALLOCATED ' + 'FROM ' +
@Old_CustomerInfo
EXEC (@Insert_Var)

At any rate, thanks again for your help jmj.

Mike