How to copy data from One table to Another (2 Viewers)

mynameiskamath · Aug 26, 2009

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 · Aug 26, 2009

you can use the append query.

Galaxiom · Aug 26, 2009

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 · Aug 26, 2009

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 · Aug 27, 2009

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 · Aug 27, 2009

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 · Aug 27, 2009

mynameiskamath said:
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 · Aug 27, 2009

namliam said:
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 · Aug 28, 2009

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 · Dec 5, 2017

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 · Mar 17, 2021

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.

How to copy data from One table to Another (2 Viewers)

mynameiskamath

New member

Access2007User

Registered User.

Galaxiom

Super Moderator

neileg

AWF VIP

mynameiskamath

New member

mynameiskamath

New member

namliam

The Mailman - AWF VIP

Galaxiom

Super Moderator

namliam

The Mailman - AWF VIP

apoculamus

New member

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread