SQL Error (?)

[bradcccs]

I have received an import script from a data supplier, and seem to have difficulty importing. I constantly receive the message that the "SQL Failed".

I have bought this to the attention of the data supplier, and received 3 further "versions" of the import, all which fail also.

I was wondering whether anyone could spot an glaring errors in the following SQL. I am not SQL proficient, and thus could not spot anything myself, but am confident the forum experts would spot it if it existed.


SQL which failed :
insert into [client] ([CL_Code], [CL_AdvisorCode], [LastUpdated],
[MaritalCode], [UserRef], [ClientTitle], [ClientSurname],
[ClientGiven], [ClientPrefName], [ClientDOB], [ClientSex],
[ClientOccupation], [ClientTFN], [ClientSocSec], [Salutation],
[PartnerTitle], [PartnerSurname], [PartnerGiven], [PartnerPrefName],
[PartnerDOB], [PartnerSex], [PartnerOccupation], [PartnerTFN],
[PartnerSocSec], [PH_Other], [ReferralCode], [StatusCode],
[Reminder], [ReminderDate], [ReminderDateTag], [cl_code_old],
[exReference], [privacy], [ClientABN], [PartnerABN], [ClientWebPage],
[PartnerWebPage], [BusinessCode], [CurRatingCode], [CurLastDate],
[OriRatingCode], [OriLastDate]) select s.[CL_Code],
s.[CL_AdvisorCode], s.[LastUpdated], s.[MaritalCode], s.[UserRef],
s.[ClientTitle], s.[ClientSurname], s.[ClientGiven],
s.[ClientPrefName], s.[ClientDOB], s.[ClientSex],
s.[ClientOccupation], s.[ClientTFN], s.[ClientSocSec],
s.[Salutation], s.[PartnerTitle], s.[PartnerSurname],
s.[PartnerGiven], s.[PartnerPrefName], s.[PartnerDOB], s.[PartnerSex],
s.[PartnerOccupation], s.[PartnerTFN], s.[PartnerSocSec],
s.[PH_Other], s.[ReferralCode], s.[StatusCode], s.[Reminder],
s.[ReminderDate], s.[ReminderDateTag], s.[cl_code_old],
s.[exReference], s.[privacy], s.[ClientABN], s.[PartnerABN],
s.[ClientWebPage], s.[PartnerWebPage], s.[BusinessCode],
s.[CurRatingCode], s.[CurLastDate], s.[OriRatingCode],
s.[OriLastDate] from vfps_work..client s where not exists (select *
from [client] dd where dd.[CL_Code] = s.[CL_Code])

Any error correction would be appreciated, or confirmation that no glaring errors exist would be even better

Thanks in advance

Brad.

 

[Pat Hartman]

The next to the last line:

s.[OriLastDate] from vfps_work..client s where not exists (select *

should be:

s.[OriLastDate] from vfps_work.client AS s where not exists (select *

 

[bradcccs]

Thanks Pat.

I shall try your correction with the import.

If I don't post back, it is because it worked (and with your record, I am confident it will), and I have 3000 clients to sort and customise.

Cheers

Brad.

 

[DanR]

Brad

Like you, I am no expert with SQL.

When I have trouble with SQL code in Access, I copy the code, start a new Access query, and post the SQL code into the SQL view.

You can then view it in the normal query design view. You will then either a) see what it is trying to do (in comparison to what you intended) or b) get an SQL error, which will generally be highlighted in the code, pointing you to the mistake.

Hope this helps.

Dan


P.S. Carn the Pies!