What is the difference between these two stATEMENTS

S

Stapes

INSERT INTO TTEMP_CampaignRun06 SELECT TTEMP_CampaignRun05.PK_Contact,
TTEMP_CampaignRun05.AcNo, ' ' AS InvoiceDate, ' ' AS TotalSpend,
TTEMP_CampaignRun05.Archive, TTEMP_CampaignRun05.OnHold,
TTEMP_CampaignRun05.NoMail, TTEMP_CampaignRun05.LCO_Due,
TTEMP_CampaignRun05.LCO, TTEMP_CampaignRun05.LCO_Memo,
TTEMP_CampaignRun05.Group, TTEMP_CampaignRun05.FK_CompID FROM
TTEMP_CampaignRun05;

SELECT TTEMP_CampaignRun05.PK_Contact, TM_CompContact.AcNo,
QRY_ContactAnnualPurchasesNew.Yr AS InvoiceDate, nz([Grand Total
Spend],0) AS TotalSpend, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.NoMail, TM_CompContact.LCO_Due,
TM_CompContact.LCO, TM_CompContact.LCO_Memo, TM_CompContact.Group,
TM_CompContact.FK_CompID, TM_CompContact.CreationDate INTO
TTEMP_CampaignRun06 FROM (TTEMP_CampaignRun05 INNER JOIN
TM_CompContact ON TTEMP_CampaignRun05.PK_Contact =
TM_CompContact.PK_Contact) INNER JOIN QRY_ContactAnnualPurchasesNew ON
TM_CompContact.PK_Contact = QRY_ContactAnnualPurchasesNew.PK_Contact
WHERE (((TM_CompContact.Archive) <> -1) AND ((TM_CompContact.OnHold)
<> -1) AND (TM_CompContact.NoMail <> -1) AND (TM_CompContact.Group <>
86) AND (TM_CompContact.Group <> 150) AND (TM_CompContact.Group <>
151) AND ((QRY_ContactAnnualPurchasesNew.Yr)='2006/2007') AND
((TM_CompContact.LCO) IS NULL) AND (TM_CompContact.LCO_Due<>-1) AND
(((nz([Grand Total Spend],0))>100)));
 
S

Stapes

INSERT INTO TTEMP_CampaignRun06 SELECT TTEMP_CampaignRun05.PK_Contact,
TTEMP_CampaignRun05.AcNo, ' ' AS InvoiceDate, ' ' AS TotalSpend,
TTEMP_CampaignRun05.Archive, TTEMP_CampaignRun05.OnHold,
TTEMP_CampaignRun05.NoMail, TTEMP_CampaignRun05.LCO_Due,
TTEMP_CampaignRun05.LCO, TTEMP_CampaignRun05.LCO_Memo,
TTEMP_CampaignRun05.Group, TTEMP_CampaignRun05.FK_CompID FROM
TTEMP_CampaignRun05;

SELECT TTEMP_CampaignRun05.PK_Contact, TM_CompContact.AcNo,
QRY_ContactAnnualPurchasesNew.Yr AS InvoiceDate, nz([Grand Total
Spend],0) AS TotalSpend, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.NoMail, TM_CompContact.LCO_Due,
TM_CompContact.LCO, TM_CompContact.LCO_Memo, TM_CompContact.Group,
TM_CompContact.FK_CompID, TM_CompContact.CreationDate INTO
TTEMP_CampaignRun06 FROM (TTEMP_CampaignRun05 INNER JOIN
TM_CompContact ON TTEMP_CampaignRun05.PK_Contact =
TM_CompContact.PK_Contact) INNER JOIN QRY_ContactAnnualPurchasesNew ON
TM_CompContact.PK_Contact = QRY_ContactAnnualPurchasesNew.PK_Contact
WHERE (((TM_CompContact.Archive) <> -1) AND ((TM_CompContact.OnHold)
<> -1) AND (TM_CompContact.NoMail <> -1) AND (TM_CompContact.Group <>
86) AND (TM_CompContact.Group <> 150) AND (TM_CompContact.Group <>
151) AND ((QRY_ContactAnnualPurchasesNew.Yr)='2006/2007') AND
((TM_CompContact.LCO) IS NULL) AND (TM_CompContact.LCO_Due<>-1) AND
(((nz([Grand Total Spend],0))>100)));
Click to expand...

What I meant to ask here: What is the difference between INSERT
INTO ... SELECT ... FROM and SELECT ... INTO ...FROM?

I am asking because, somewhere in my maze of code, I seem to be
deleting the original file(linked from another database), and creating
a new one in my front end!
 
D

Douglas J. Steele

INSERT INTO assumes that TTEMP_CampaignRun06 exists, and appends the rows
selected by the SELECT subquery into that table.

SELECT ... INTO assumes that TTEMP_CampaignRun06 does not exist, and creates
it (in the front end)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Stapes said:
What I meant to ask here: What is the difference between INSERT
INTO ... SELECT ... FROM and SELECT ... INTO ...FROM?

I am asking because, somewhere in my maze of code, I seem to be
deleting the original file(linked from another database), and creating
a new one in my front end!
INSERT INTO TTEMP_CampaignRun06 SELECT TTEMP_CampaignRun05.PK_Contact,
TTEMP_CampaignRun05.AcNo, ' ' AS InvoiceDate, ' ' AS TotalSpend,
TTEMP_CampaignRun05.Archive, TTEMP_CampaignRun05.OnHold,
TTEMP_CampaignRun05.NoMail, TTEMP_CampaignRun05.LCO_Due,
TTEMP_CampaignRun05.LCO, TTEMP_CampaignRun05.LCO_Memo,
TTEMP_CampaignRun05.Group, TTEMP_CampaignRun05.FK_CompID FROM
TTEMP_CampaignRun05;

SELECT TTEMP_CampaignRun05.PK_Contact, TM_CompContact.AcNo,
QRY_ContactAnnualPurchasesNew.Yr AS InvoiceDate, nz([Grand Total
Spend],0) AS TotalSpend, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.NoMail, TM_CompContact.LCO_Due,
TM_CompContact.LCO, TM_CompContact.LCO_Memo, TM_CompContact.Group,
TM_CompContact.FK_CompID, TM_CompContact.CreationDate INTO
TTEMP_CampaignRun06 FROM (TTEMP_CampaignRun05 INNER JOIN
TM_CompContact ON TTEMP_CampaignRun05.PK_Contact =
TM_CompContact.PK_Contact) INNER JOIN QRY_ContactAnnualPurchasesNew ON
TM_CompContact.PK_Contact = QRY_ContactAnnualPurchasesNew.PK_Contact
WHERE (((TM_CompContact.Archive) <> -1) AND ((TM_CompContact.OnHold)
<> -1) AND (TM_CompContact.NoMail <> -1) AND (TM_CompContact.Group <>
86) AND (TM_CompContact.Group <> 150) AND (TM_CompContact.Group <>
151) AND ((QRY_ContactAnnualPurchasesNew.Yr)='2006/2007') AND
((TM_CompContact.LCO) IS NULL) AND (TM_CompContact.LCO_Due<>-1) AND
(((nz([Grand Total Spend],0))>100)));
Click to expand...
Click to expand...
 
Top