Help with insert query - please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to append records to [MERGE] from the records in [IMPORT] ONLY where
[IMPORT].[PERIOD_VALUE] = '2' AND [IMPORT].[ACCTNO] does not exist in
[MERGE].[ACCTNO].

The data types are identical and the columns are in the same order, but the
column names are not the same:

[MERGE]: ACCTNO; ACCOUNT_DESC; PY_ACTUAL;
[IMPORT]: ACCTNO; ACCOUNT_DESC; YTD_ACTUAL;

My code so far (as you can see I need a lot of help):

INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL FROM [IMPORT]

Could someone please correct my SQL? Thanks much in advance from a depressed
beginner...
 
INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL
FROM [IMPORT]
WHERE [IMPORT].[PERIOD_VALUE] = '2'
AND [IMPORT].[ACCTNO] NOT IN (SELECT DISTINCT ACCTNO FROM MERGE)

Or, you might be able to do:

INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL
FROM [IMPORT]
LEFT JOIN [MERGE] ON [IMPORT].ACCTNO = [MERGE].ACCTNO
WHERE [IMPORT].[PERIOD_VALUE] = '2'
AND [MERGE].ACCTNO IS NULL

HTH
Dale
 
Dale, thanks so much! The second code runs the fastest, so I'm sticking with
that one for live processing, but I will keep both in the library for future
reference. Again, thanks.

Dale Fye said:
INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL
FROM [IMPORT]
WHERE [IMPORT].[PERIOD_VALUE] = '2'
AND [IMPORT].[ACCTNO] NOT IN (SELECT DISTINCT ACCTNO FROM MERGE)

Or, you might be able to do:

INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL
FROM [IMPORT]
LEFT JOIN [MERGE] ON [IMPORT].ACCTNO = [MERGE].ACCTNO
WHERE [IMPORT].[PERIOD_VALUE] = '2'
AND [MERGE].ACCTNO IS NULL

HTH
Dale

quartz said:
I need to append records to [MERGE] from the records in [IMPORT] ONLY where
[IMPORT].[PERIOD_VALUE] = '2' AND [IMPORT].[ACCTNO] does not exist in
[MERGE].[ACCTNO].

The data types are identical and the columns are in the same order, but the
column names are not the same:

[MERGE]: ACCTNO; ACCOUNT_DESC; PY_ACTUAL;
[IMPORT]: ACCTNO; ACCOUNT_DESC; YTD_ACTUAL;

My code so far (as you can see I need a lot of help):

INSERT INTO [MERGE] (ACCTNO, ACCOUNT_DESC, PY_ACTUAL) "
SELECT ACCTNO, ACCOUNT_DESC, YTD_ACTUAL FROM [IMPORT]

Could someone please correct my SQL? Thanks much in advance from a depressed
beginner...
Click to expand...
Click to expand...
 
Back
Top