Click here to Skip to main content
14,238,330 members
Rate this:
Please Sign up or sign in to vote.
See more:
I NEED TO COPY FROM EMP TB TO EmpRetirement TABLE & Retirement TABLE

What I have tried:

SELECT * INTO EmpRetirement
FROM EMP
WHERE (((EMP.JopEnd)<=Now()))
ORDER BY Emp.ID, Emp.Agenow;
* INTO Retirement
Posted
Updated 14-Jun-19 2:44am
Comments
DerekTP123 11-Jun-19 11:00am
   
You'll need to tell us what DBMS you're using, and give us table definitions (e.g. we need to know if there is an auto-number / auto-increment column defined). Better still, read a primer on SQL and you'll figure it out yourself, probably sooner than you'll get an answer here.
ZurdoDev 14-Jun-19 10:11am
   
Very easy. Where are you stuck?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If you are still having problems with this and need help, then you need to give us a lot more information. Such as: What database are you using (looks like Access?); What are the table structures; what do you want to delete etc etc. A really good starting point is to give some sample data and some expected results.

Also read the following:
Some guidelines for posting questions in the forums[^]
Article Idea: How Not to Ask a Question[^]

In the meantime: This will fix your attempt
SELECT * INTO EmpRetirement
FROM EMP
WHERE EMP.JopEnd<=Now()
ORDER BY Emp.ID, Emp.Agenow;
You will need a 2nd query for the Retirement table..
SELECT * INTO Retirement FROM EMP
WHERE EMP.JopEnd<=Now()
ORDER BY Emp.ID, Emp.Agenow;
Now some things to consider:
1. It is bad practice to have multiple tables with the same schema. I'm not going to go into a long discourse here - research "Database Design techniques"[^]
2. If those tables already exist and you are not using Access the queries will fail - you will need to use
INSERT INTO EmpRetirement
SELECT * FROM EMP …
If you are using Access you will be prompted and told that the table will be overwritten - i.e. all existing data will be lost.
3. It is also bad practice to use SELECT * - list the columns that you want explicitly. What if someone adds a column to EMP but not to EmpRetirement - your code will immediately fail. It is also why @DerekTP123 has mentioned auto-number/auto-increment columns - if both EMP and EmpRetirement have an autonumber column then your query will fail.
The final part is that you apparently want to delete the stuff once copied. That would be
DELETE *
FROM EMP
WHERE EMP.JopEnd<=Now();
I advise you to check that the insert was successful before deleting any data though
   
Comments
Maciej Los 14-Jun-19 15:37pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100