How to copy data from One table to Another

[mynameiskamath]

Hi,
While writing some query i got struck

What is the requirement
How can I copy Data from one table to another?
Table1 has about 500 rows and 25 columns(col1, col2 ....col25). I want to copy selected columns from Table1 to table 2.
i.e. I wanna copy Col1, col2, col3, col5, col6, col7, col24, col5 from table1 to table2 in the same database.

I have never copied data from one table to another. I tried lot of R&D but none of them didn't work.

Can somebody tell me what query to use in this case.
This is kind of URGENT for me

Please Help
Thanks in adnavce

Regards
Kamath

 

[Access2007User]

you can use the append query.

 

[Galaxiom]

INSERT INTO Table2(Col1, col2, col3, col5, col6, col7, col24, col5) SELECT Col1, col2, col3, col5, col6, col7, col24, col5 FROM table1;

http://msdn.microsoft.com/en-us/library/bb208861.aspx

Another way is to simply copy and paste the table with the clipboard in the navigation pane and delete the fields you don't want.

 

[neileg]

I think you need to read up on normalisation. Neither the original table structure or the concept of copying data look compatible with a normalised database.

 

[mynameiskamath]

Hi,
Thanks for the real quick reply. I just happened to do that yesterday itself. I used the below query to fix my issue that i was facing.

Which query fixed the issue
INSERT INTO Table2 (Name, Roll, Marks)
SELECT Table1.Name, Table1.Roll, Table1.Marks
FROM Table1;

What I did to Fix the issue
Let us assume that below 4 fields are there in the Table1

• Name
• Comment
• Roll
• Marks

I wanted only Name, Roll and Marks to be dumped into Table2. For this i created Table2 with the below fields (Excluded Comment field)

• Name
• Roll
• Marks

Once these tables were created, i just fired the above query to achive the desired result.

Regards
Kamath

 

[mynameiskamath]

Hi Galixiom,
I just noticed your reply in the mail thread. Thanks a lot for the real quick response. I appreciate that

Regards
Kamath

 

[namliam]

Hi,
I used the below query to fix my issue that i was facing.

If you read up about normalization you will have skipped a part if you think this is your solution, go back and read again. Again and then AGAIN!





Noooooooooooooooooooo, in a normalized database you DO NOT copy data from table to table... NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

No
No
No

This is bad
Real bad
Worse

How will you keep this data up to date?? You dont, unless you "simply" re-run the query -each and every time- a NIGHTMARE

Why dont you just use a "normal" query instead of this copy-ing stuff??

 

[Galaxiom]

Why dont you just use a "normal" query instead of this copy-ing stuff??

I assumed the OP was just restructuring their database and the original table would become redundant. Isn't it obvious that you wouldn't repeat the data in another table?

 

[namliam]

Unfortunatly Galaxiom, NO

For some reason even a lot of the developers at my company seem to enjoy duplicating data all over the place. I.e. We have 5 times the same address ??

Not duplicating data seems like "but offcourse", however a lot of people come from excel or go to Access without any formal idea/education towards what they are doing... Doing it on the fly with ideas that "they get it done" while F-ing around with the whole DB-Development and basic things like Normalization...

 

[apoculamus]

Sorry to be here years too late for this thread, but I just encountered the problem today and joined!

To namliam's point normalization is the 1st rule every database programmer must, must live by. However, here's why this issue is a valid one. Today I was presented with a flat file that has just under 60,000 line items and I need to break it down into tables. It was already in a table with an ungodly number of data duplications throughout. So I pushed it down into a spreadsheet and in different tabs I deleted all columns that did not belong in the various tables. Then I created the tables with 'unique - no duplicates' properties on the key fields for each new table as and where applicable. It was a bit tedious, but first I established my structure and relationships, so when I did my uploads the result was pretty good.

My 1st final master key table ended up with just over 6,000 line items, about +/- 10% the size of the flat file.

 

[Pat Hartman]

Please start a new thread for a new question. This is a four year old thread for a different topic. I would move it for you but I can't find the option that does it. Sorry.

Your problem needs to be solved with an update query but we don't have enough information yet.