• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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)                      
0
bobby6055
Asked:
bobby6055
  • 3
  • 3
  • 3
1 Solution
 
pcelbaCommented:
If both tables have same columns:

INSERT INTO TableA
  SELECT * FROM TableB
0
 
pcelbaCommented:
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
0
 
pcelbaCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SheilsCommented:
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
0
 
bobby6055Author Commented:
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.
0
 
SheilsCommented:
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
0
 
bobby6055Author Commented:
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.
0
 
SheilsCommented:
Bobby

There is no code involved in my suggestion. I take it that you can create a query in design view. If so then you are good to go.

Infact you can even use cut an paste for the first 2 steps:

In the database window go  right click on table A then click copy
Then right click in a blank space on the window and click paste.
A dialog box will open. Type in a name (say A1) Under paste option select data and structure
Open the new table in design view and create a new field (say ID) and set it to autonumber.

repeat the above sets for table B.

Open a new query in design view
select the two new tables
link them through the id field that you created above.
select all the fields that you want to go in the "merge" table.
right click on the query in design view and click query type>make table query
Type a new name in the dialog box click OK
click on the red exclamation mark icon on your toolbar to run the query.

The new table should be created

0
 
bobby6055Author Commented:
Works as expected
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now