I am trying to insert data into a table using a .Net DataAdapter. The following simplified example shows my problem (hopefully)
The DataTable has 3 columns
Now the insert command sql for the DataAdapter is the following (and works in Sql Assistant with values)
insert into mytable select ?, ?, ? where not exists (select 1 from mytable where Id = ?)
Now because named parameters aren't supported I have more parameter markers than columns and I can't figure out how to configure the DbCommand parameter collection to support this scenario.
Do I have to add a fourth column to the datatable that is a duplicate of the existing Id column? Or is there another way.
You might switch to MERGE instead of INSERT:
MERGE INTO mytable AS tgt USING VALUES (?,?,?) AS src (Id, FirstName, LastName)
ON src.Id = tgt.Id
WHEN NOT MATCHED THEN
INSERT VALUES (src.Id, src. FirstName, src.LastName)
the SourceColumn maps a parameter-value to a DataTable column. One or more parameters can be mapped to a Source Column.