Move (not append) records from one table to another

[Guest]

Hello,

I have a query that is successfully appending the records I want from one
table to another. I need to delete only the source records which were moved
to the target table. Here is my query:

INSERT INTO [tblCandidate-Test] ( CANDIDATE, AREA_CODE, [NUMBER],
INPUT_NUMBER, DNC, FOLLOWUP_NUMBER )
SELECT TOP 150 [tblRRD Output].Name, Left([tblRRD Output]![PhoneNumber],3)
AS AREA_CODE, [tblRRD Output].PhoneNumber, Right([tblRRD
Output]!PhoneNumber,7) AS INPUT_NUMBER, [tblRRD Output].DNC, "1" &
[PhoneNumber] AS FOLLOWUP_NUMBER
FROM [tblRRD Output]
WHERE ((([tblRRD Output].DNC)=False));

Any thoughts on how this query could be modified to meet the requirement?

Thanks much!

 

[Allen Browne]

A move consists of an append followed by a delete.

Every table should have a primary key, so if you copy the key value you
could use that to identify the records that way (assuming the key doesn't
get reused after they are archived.)

The query to delete the records that are now in both tables (based on a
field named ID) would look like this:
DELETE FROM [tblRRD Output]
WHERE EXISTS
(SELECT [ID] FROM [tblCandidate-Test]
WHERE [tblCandidate-Test].[ID] = [tblRRD Output].[ID]);

 

[Guest]

So, should I just add your SQL code to the end of my SQL code before the ";"
or is this a second query?

A move consists of an append followed by a delete.

Every table should have a primary key, so if you copy the key value you
could use that to identify the records that way (assuming the key doesn't
get reused after they are archived.)

The query to delete the records that are now in both tables (based on a
field named ID) would look like this:
DELETE FROM [tblRRD Output]
WHERE EXISTS
(SELECT [ID] FROM [tblCandidate-Test]
WHERE [tblCandidate-Test].[ID] = [tblRRD Output].[ID]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that is successfully appending the records I want from one
table to another. I need to delete only the source records which were
moved
to the target table. Here is my query:

INSERT INTO [tblCandidate-Test] ( CANDIDATE, AREA_CODE, [NUMBER],
INPUT_NUMBER, DNC, FOLLOWUP_NUMBER )
SELECT TOP 150 [tblRRD Output].Name, Left([tblRRD Output]![PhoneNumber],3)
AS AREA_CODE, [tblRRD Output].PhoneNumber, Right([tblRRD
Output]!PhoneNumber,7) AS INPUT_NUMBER, [tblRRD Output].DNC, "1" &
[PhoneNumber] AS FOLLOWUP_NUMBER
FROM [tblRRD Output]
WHERE ((([tblRRD Output].DNC)=False));

Any thoughts on how this query could be modified to meet the requirement?

Thanks much!

 

[Allen Browne]

It is a second query. Execute it separately.

Depending how far you want to go, you can learn about how to execute both in
the click of the same button, checking that the Append worked successfully
before you execute the Delete. See:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

Or, if it is important to rollback to exactly the same place if any step
goes wrong (e.g. some records did not append, or not all deleted
successfully), you can wrap a transaction around the whole thing. Details
in:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

So, should I just add your SQL code to the end of my SQL code before the
";"
or is this a second query?

A move consists of an append followed by a delete.

Every table should have a primary key, so if you copy the key value you
could use that to identify the records that way (assuming the key doesn't
get reused after they are archived.)

The query to delete the records that are now in both tables (based on a
field named ID) would look like this:
DELETE FROM [tblRRD Output]
WHERE EXISTS
(SELECT [ID] FROM [tblCandidate-Test]
WHERE [tblCandidate-Test].[ID] = [tblRRD Output].[ID]);

I have a query that is successfully appending the records I want from
one
table to another. I need to delete only the source records which were
moved
to the target table. Here is my query:

INSERT INTO [tblCandidate-Test] ( CANDIDATE, AREA_CODE, [NUMBER],
INPUT_NUMBER, DNC, FOLLOWUP_NUMBER )
SELECT TOP 150 [tblRRD Output].Name, Left([tblRRD
Output]![PhoneNumber],3)
AS AREA_CODE, [tblRRD Output].PhoneNumber, Right([tblRRD
Output]!PhoneNumber,7) AS INPUT_NUMBER, [tblRRD Output].DNC, "1" &
[PhoneNumber] AS FOLLOWUP_NUMBER
FROM [tblRRD Output]
WHERE ((([tblRRD Output].DNC)=False));

Any thoughts on how this query could be modified to meet the
requirement?

Thanks much!