Hi there,
I am trying to import data from a custom XML file to a custom SQL table using the SQL Provider. The table looks as follows:
CREATE TABLE [dbo].[CustomPrices](
[ContractId] [NVARCHAR](30) NOT NULL,
[ProductId] [NVARCHAR](30) NOT NULL,
[Price] [DECIMAL](18, 0) NOT NULL,
[MarketingNotes] [NVARCHAR](MAX) NULL,
CONSTRAINT [PK_CustomPrices] PRIMARY KEY CLUSTERED
(
[ContractId] ASC,
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The primary key of the table is a composite of ContractId and ProductId.
In the UI these two columns are correctly inferred as keys (I can see they are selected when I click Set Keys for the table). Yet in the XML they are not:
<name>ContractId</name><type>System.String</type><isNew>False</isNew><isPrimaryKey>False</isPrimaryKey>
When I execute the import job a second time, it fails with the eror:
Import job failed: failed to move data from temporary table [dbo.CustomPricesTempTableForSqlProviderImport] to main table [dbo.CustomPrices]. Exception: Violation of PRIMARY KEY constraint 'PK_CustomPrices'. Cannot insert duplicate key in object 'dbo.CustomPrices'. The duplicate key value is (, 6552). The statement has been terminated. Sql query: update [dbo].[CustomPrices] set [ContractId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ContractId], [ProductId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ProductId], [Price]=[dbo].[CustomPricesTempTableForSqlProviderImport].[Price], [MarketingNotes]=[dbo].[CustomPricesTempTableForSqlProviderImport].[MarketingNotes] from [dbo].[CustomPricesTempTableForSqlProviderImport] where [dbo].[CustomPrices].[ContractId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ContractId] and [dbo].[CustomPrices].[ProductId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ProductId] ; insert into [dbo].[CustomPrices] ([ContractId], [ProductId], [Price], [MarketingNotes]) (select [dbo].[CustomPricesTempTableForSqlProviderImport].[ContractId], [dbo].[CustomPricesTempTableForSqlProviderImport].[ProductId], [dbo].[CustomPricesTempTableForSqlProviderImport].[Price], [dbo].[CustomPricesTempTableForSqlProviderImport].[MarketingNotes] from [dbo].[CustomPricesTempTableForSqlProviderImport] left outer join [dbo].[CustomPrices] on [dbo].[CustomPrices].[ContractId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ContractId] and [dbo].[CustomPrices].[ProductId]=[dbo].[CustomPricesTempTableForSqlProviderImport].[ProductId] where [dbo].[CustomPrices].[ContractId] is null);
How do I tell the DIF to use the composite key and use an UPDATE rather than an INSERT when a duplicate key is found?
Thanks,
Imar
