bobby6055
asked on
Simple Update Query
What is the query that will simply copy data from TableB fields into matching fields in Table A without having to use any WHERECLAUSES?
e.g.I dont need to copy the autonumber from tableB
TableA (table with no data) TableB (tablewith Data)
ID '<===Move data into tableA ID
LNo(number) LNo(number)
HNo(text) HNo(text)
CCon(Yes/No) CCon(Yes/No) ,_---True/False
Flow (Decimal) Flow (Decimal)
e.g.I dont need to copy the autonumber from tableB
TableA (table with no data) TableB (tablewith Data)
ID '<===Move data into tableA ID
LNo(number) LNo(number)
HNo(text) HNo(text)
CCon(Yes/No) CCon(Yes/No) ,_---True/False
Flow (Decimal) Flow (Decimal)
If tables have more than just above five mentioned columns:
INSERT INTO TableA (ID, LNo, HNo, CCon, Flow)
SELECT ID, LNo, HNo, CCon, Flow FROM TableB
INSERT INTO TableA (ID, LNo, HNo, CCon, Flow)
SELECT ID, LNo, HNo, CCon, Flow FROM TableB
Autonumber is not possible to copy this way. You have to either disable the autonumbering in TableB and eanble it after inserting records or you have to copy fields without autonumber column.
Create a new query in design view.
Add the table that already contains the data.
Right click on the query(in design view) and click on query type>Append Query
A dialog box will pop up. Select the table that you want to insert the data into and click OK
Then add the field to the query as per normal. The related field in the recieving table will come up automatically if their name of the fields in the two tables are exactly the same. Otherwise you can select the name from the dropdown box in the append to row.
This is exactly the same solution as suggested by pcelba except that you don't have to use code
Add the table that already contains the data.
Right click on the query(in design view) and click on query type>Append Query
A dialog box will pop up. Select the table that you want to insert the data into and click OK
Then add the field to the query as per normal. The related field in the recieving table will come up automatically if their name of the fields in the two tables are exactly the same. Otherwise you can select the name from the dropdown box in the append to row.
This is exactly the same solution as suggested by pcelba except that you don't have to use code
ASKER
I tried all the suggestions but the records created appeared to be showing in diferent rows other than current row records in the table.
Table A already has other existing fields in it with data. I created new fields in Table A (LNo, HNo,CCon,Flow) and all I want is to place all data from TableB . TableB already has data in the fields that I want to copy. I dont want to create new rows, I simply want to dump records in those fields that I just created i TableA.
I have deleted the ID field from TableA since it's an autonumber and I dont need data from (TableB.ID) copied
I wanted the new fields I created in TableA be filled with data from TableB nothing more. I am not getting it to work on my side.
Table A already has other existing fields in it with data. I created new fields in Table A (LNo, HNo,CCon,Flow) and all I want is to place all data from TableB . TableB already has data in the fields that I want to copy. I dont want to create new rows, I simply want to dump records in those fields that I just created i TableA.
I have deleted the ID field from TableA since it's an autonumber and I dont need data from (TableB.ID) copied
I wanted the new fields I created in TableA be filled with data from TableB nothing more. I am not getting it to work on my side.
Insert into (or Append) query don't work like that (unless you use where).
If this is a one off task I suggest the following:
assuming that the rows in the two tables are is the order you want to "merge" them.
Use a make table query to create two tables: A1 and B1 from table A and B respectively.
Insert an autonumber field each table(A1 and B1)
Use a query to extract vlues from A1 and B1. Convert that to a make table query
If this is a one off task I suggest the following:
assuming that the rows in the two tables are is the order you want to "merge" them.
Use a make table query to create two tables: A1 and B1 from table A and B respectively.
Insert an autonumber field each table(A1 and B1)
Use a query to extract vlues from A1 and B1. Convert that to a make table query
ASKER
sb9. You suggestion sounds logical. I am not an SQL guy and I will appreciate it if you could provide me a sample db of your suggested idea.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works as expected
INSERT INTO TableA
SELECT * FROM TableB