I'm running an F# application to pull data from an Access table to a linked SQL Server table. Here is the resulting query:
INSERT INTO dbo_TempTerm (UnitID, PolicyTermYear, InsuredName, PolicyNumber, RenewalDate, CovATotal, CovBTotal, CovLTotal, DwellExtn, AllOtherPerilDeductible, MedPay, TotalPremium, HurricaneDeductible, Zone, Subzone, PercentCRC, PercentCRD, YearBuilt, RenYrs, PercentCFD, PercentHA, PercentMLD, GRP1, PercentNH, QCLM, RateV, CRI, AgentCode, AgentName, AFOCode, PolicyType, PolicyForm, OC, DateCreated)
SELECT [UNIT ID], [POLICY TERM YEAR], [INSURED NAME], [POLICY #], [RENEWAL DT], [COV A TOTAL], [COV B TOTAL], [COV L TOTAL], [DWELL EXTN], [ALL/OTHER PERIL DEDUCTIBLE], [MED PAY], [TOT PREMIUM], [HURR DED], ZONE, SUBZONE, [%CRC], [%CRD], [YR BLT], RENYRS, [%CFD], [%HA], [%MLD], GRP1, [%NH], QCLM, [RATE V], CRI, [AGENT CODE], [AGENT NAME], [AFO CODE], [POLICY TYPE], [POLICY FORM], OC, DateCreated
FROM tblPrior
The query parses and runs fine in Access. But from within my application, it raises an error: Syntax error in INSERT INTO statement.
Here is my F#, though I don't think it's relevant.
module Data
open System.Data
open System.Data.OleDb
type Period = Prior | Current
let Upload (p:Period) db =
use conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + db + ";Persist Security Info=False;")
let execNonQuery s =
let comm = new OleDbCommand(s, conn) in
comm.ExecuteNonQuery() |> ignore
let (table,proc) = match p with
| Prior -> ("tblPrior" ,"InitPriorTable")
| Current -> ("tblCurrent","InitCurrentTable")
do conn.Open()
let u = sprintf "INSERT INTO dbo_TempTerm (UnitID, PolicyTermYear, InsuredName, PolicyNumber, RenewalDate, CovATotal, CovBTotal, CovLTotal, DwellExtn, AllOtherPerilDeductible, MedPay, TotalPremium, HurricaneDeductible, Zone, Subzone, PercentCRC, PercentCRD, YearBuilt, RenYrs, PercentCFD, PercentHA, PercentMLD, GRP1, PercentNH, QCLM, RateV, CRI, AgentCode, AgentName, AFOCode, PolicyType, PolicyForm, OC, DateCreated) SELECT [UNIT ID], [POLICY TERM YEAR], [INSURED NAME], [POLICY #], [RENEWAL DT], [COV A TOTAL], [COV B TOTAL], [COV L TOTAL], [DWELL EXTN], [ALL/OTHER PERIL DEDUCTIBLE], [MED PAY], [TOT PREMIUM], [HURR DED], ZONE, SUBZONE, [%%CRC], [%%CRD], [YR BLT], RENYRS, [%%CFD], [%%HA], [%%MLD], GRP1, [%%NH], QCLM, [RATE V], CRI, [AGENT CODE], [AGENT NAME], [AFO CODE], [POLICY TYPE], [POLICY FORM], OC, DateCreated FROM %s" table
// DEBUG.
printfn "%s" u
// DEBUG.
execNonQuery u
execNonQuery proc