Move Recs from/to Identical Tables 1

[RRoswell]

Hi,

Seems like I saw a way to do this down and dirty, but I can't find the answer and I might be wrong.

I have two customer tables, one for active customers, and one for past customers.

They are identical, but they have many columns, and I was wondering if there was an easy way to move records from the current customer table to the past customer table instead of having to list all the columns in the insert statement.

Many Thanks.

 

[r937]

how to "move" records

step 1

INSERT INTO table2
SELECT * FROM table1
WHERE some criteria

step2

DELETE FROM table1
WHERE some criteria


this relies on the columns being iedntical in both tables


say, how come you have 2 tables anyway? i would use just one table and an active/inactive flag

r937.com | rudy.ca

 

[RRoswell]

say, how come you have 2 tables anyway? i would use just one table and an active/inactive flag"

I may do that.

I'm setting up a cron job and I wanted its processing time to be as minimalist as possible.

What I really want to do is check for new customers, and if they exist, process the order and move the data to existing customers.

I thought that checking a new customer table (which would be empty most of the time) would be more economical than checking an existing customer table, which hopefully will have thousands of customers someday.

Thanks for your help.

 

[Kozusnik]

Actually, I understand having two tables. I wrote an Emergency Room Patient Tracking program which we use at my hospital. There is one table for current patients (usually less than 50 records - includes waiting room) and one for patients who have left the ER ( > 50000 records). The only time I pull from both tables is for a patient lookup, then I use a 'SELECT FROM table1...UNION...SELECT FROM table2' to get a list from both.

Mark