Append Queries

[HunterEngineeringCoop]

Hi all,



I have a question concerning append queries. I would like to append new records into TableA from TableB without creating any duplicate records in TableA.

I am using a standard Select Query to query data from TableB and then swith to append query to add the data to TableA. This method will copy over all the data, even if it is already present in TableA. I need to filter it somehow to not include redundant data.

The reason I am doing this is because I have a table that cannot be modified by me, and I need to copy that data to a new table to add more fields. I need this table to automatically update when the old table is updated. I think an append query could manage this, but, if there is a more elegant solution, I would be pleased to know.

Thanks in advance,

[weekend00]

Can you please explain more:

The reason I am doing this is because I have a table(table B?) that cannot be modified by me, and I need to copy that data to a new table(Table A?) to add more fields. I need this table to automatically update when the old table(Table B?) is updated. I think an append query could manage this, but, if there is a more elegant solution, I would be pleased to know.

Am I correct with the table A and B?

Assume I understand you correctly, you are trying to append records from table B to table A when records are added in table B, in this case, an append query can do it.
to eliminate redundant records, we need to know which field or fields( we call it the key field(s), like employeeID, projectID etc.) can identify a record, then we can eliminate the recundant.

You also want the updates in table B to go to table A, then you need update query. you don't want to append because they are exist in table B.
you still need the key field(s) to do an update in table A, and you need to know which field may be changed. (if most of the fields may be changed, you can also do a delete then append instead of updating.)

[HunterEngineeringCoop]

Hi Weekend00,

Thanks for the help!!

Yes you are correct with the labeling of TableA and TableB.

I'm having trouble with primary Key because TableB has a multiple field key, consisting of three fields, which I have never used before.

Basically what I'm trying to accomplish is to add new records to TableA any time new records are added to TableB. I want to add ONLY the new records, which would be any records that aren't in TableA. I don't think and update query is what I need because it doesn't actually add new records.

[weekend00]

Append query:

insert into tableA select tableB.* from tableB as b left join tableA as a on a.keyfield1=b.keyfield1 and a.key2=b.key2 and a.key3=b.key3 where a.key2 is null

[slave138]

I usually use SELECT DISTINCTROW ... when I want to avoid duplicates:

INSERT into tableA SELECT DISTINCTROW tableB.* FROM tableB AS b LEFT JOIN tableA AS a ON a.keyfield1=b.keyfield1 AND a.key2=b.key2 AND a.key3=b.key3

...or something similar (I didn't test to make sure the syntax was 100%)

[weekend00]

I usually use SELECT DISTINCTROW ... when I want to avoid duplicates:

INSERT into tableA SELECT DISTINCTROW tableB.* FROM tableB AS b LEFT JOIN tableA AS a ON a.keyfield1=b.keyfield1 AND a.key2=b.key2 AND a.key3=b.key3

...or something similar (I didn't test to make sure the syntax was 100%)

The records that you are inserting is distinct, but this query does not check existing records in tableA.

And, should use distinct instead of distinctrow. distinct guarantee unique row in select set, distinctrow guarantee unique row in from result set, an example maybe more clear:

table t1:
f1 f2
1 2
1 3
select distinct f1 from t1
returns:
f1
1

select distinctrow f1 from t1
returns
f1
1
1

[slave138]

The records that you are inserting is distinct, but this query does not check existing records in tableA.

Acknowledged. I'm used to looking for duplicates in the records I'm importing (impatient people submitting multiple times, for example) as opposed to comparing the two.

That said, if there is a unique field in the records being imported from tableB (like a Primary key) and the target table (tableA) includes a matching unique field, the query doesn't need to compare at all because the INSERT will ignore the matching records.

And, should use distinct instead of distinctrow. distinct guarantee unique row in select set, distinctrow guarantee unique row in from result set, an example maybe more clear:

table t1:
f1 f2
1 2
1 3
select distinct f1 from t1
returns:
f1
1

select distinctrow f1 from t1
returns
f1
1
1

I would argue that a record where F1=1 and F2 = 2 is not a duplicate record of one where F1 = 1 and F2 = 3 which is why DISTINCTROW would be more appropriate when dealing with entire records instead of individual fields. It returns unique records where DISTINCT returns unique fields.

[weekend00]

You are right, JOIN is not needed.

I forgot that Acces can insert non duplicate rows while skip the duplicate ones with unique index. I get used to have a fail batch/transaction when trying to insert duplicate key in sybase.

[nigelbloomy]

I am so glad I found this. I have an original table that is updated constantly by lots of people. My append table needs to be updated with that new information. Using this thread, I made an append query that has both tables on it. The tables are joined by the ID (that is the unique identifier in both tables), but the join is set up so the query includes everything from the original table, and only the items that match from the append table (the Left join in the SQL below). The important part is to only include records in the query where the append table has no data (the Is Null criteria). That way the query only returns data that is in the original table and nothing that is already in my append table.

I turned this into an append query that adds the new information to my append table. I tried so many other ways of doing this and I really wanted to avoid some of the crazy VBA codes I have seen while googling this issue. This method worked and it is very simple. (My explanation is probably confusing, but the concept works and is simple to implement.) If you want an append query that only appends new information this is the way to do it. Thank you Weekend00.

INSERT INTO [Table Append] ( ID, Name )
SELECT [Table Original].ID, [Table Original].Name
FROM [Table Original] LEFT JOIN [Table Append] ON [Table Original].ID = [Table Append].ID
WHERE ((([Table Append].ID) Is Null));

Append query:

insert into tableA select tableB.* from tableB as b left join tableA as a on a.keyfield1=b.keyfield1 and a.key2=b.key2 and a.key3=b.key3 where a.key2 is null