Simple insert query

[TPH]

Hello guys,

I have a simple problem of combining two tables, as shown below. I guess I wasn't able to find the right search terms for what I need. Sorry about the English.

Table 01:
ID | Data
<null> A
<null> B
<null> C

Table 02:
ID | Data
01 <null>
02 <null>
03 <null>

Wanted result (By inserting the fields from Table02 into Table01):
ID | Data
01 A
02 B
03 C

This doesn't seem to work:

INSERT INTO Table01 ( ID )
SELECT Table02.ID
FROM Table02
WHERE (((Table01.ID) Is Null));

(It asks me for the Table01.ID value)

Thank you!

 

[Galaxiom]

Firstly you have a space in the table names that you have not included in the sql.

Secondly the Where clause applies to the FROM clause.

The third issue is that Insert adds new records. It does not update existing records.

Finally, without anything to join the two tables there is no control over which values from table02 will be matched with the records in table01. You need to explain how the records are related before we can offer code suggestions.

 

[TPH]

Thank you both for the quick replies.

Firstly you have a space in the table names that you have not included in the sql.

Secondly the Where clause applies to the FROM clause.

The third issue is that Insert adds new records. It does not update existing records.

Alright...I tried changing it to an Update query but the example below doesn't update anything:

UPDATE Table01 INNER JOIN Table02 ON Table01.ID = Table02.ID SET Table01.ID = [Table02].[ID]
WHERE (([Table01].[ID] Is Null));

Finally, without anything to join the two tables there is no control over which values from table02 will be matched with the records in table01. You need to explain how the records are related before we can offer code suggestions.

Why not just add an autonumber field to Table 01?

Sorry, I'll try to go into a little more detail...the original Table01 has about 1000k records, but no ID. When I try to create an ID field with autonumber, I get the "File sharing lock count exceeded" error. We have restricted access at work, so I'm not able to go into the registry to change the MaxLocksPerFile entry.

So the idea was to import a simple table with just the ID column filled in, and then combine that with the existing table that has the data. Wouldn't really matter what order the records ended up in, as long as each one had a unique ID number.

Normally we'd export the large Table01 into Excel in batches, create the ID fields manually and then import them back into Access, and then combine the data. But I was curious to know if there was an easier way.

Thanks again!

 

[Galaxiom]

Create a table with the ID field (autonumber) and the data field. No records yet.

Then insert the records from the data table. The autonumber will populate automatically as the data records are inserted.

 

[TPH]

That worked great, thank you!