What do I need to do to have this script work in SQL 2000. When I run it in SQL 2000 I get cannot insert duplicate key. I am running the script against the same data in both versions.
I would expect it to insert zero records if it finds existing records in the table.
INSERT INTO [001].[dbo].[ItemAccounts]
([ItemCode]
,[AccountCode]
,[MainAccount]
,[ItemCodeAccount]
,[PurchaseCurrency]
,[PurchasePrice]
,[PurchaseVATCode]
,[PurchaseVATPerc]
,[PurchaseVATIncl]
,[PurchaseUnitToInternalUnitFactor]
,[PurchaseUnitToPurchasePackageFactor]
,[PurchaseOrderSize]
,[DiscountMargin]
,[SalesPriceRecommended]
,[SlsPkgsPerPurPkg]
,[DeliveryTimeInDays]
,[DeliverableFromStock]
,[StatisticalFactor]
,[Warranty]
,[Division]
,[syscreated]
,[syscreator]
,[sysmodified]
,[sysmodifier]
,[sysguid])
select
oecusitm_sql.item_no,
cicmpy.cmp_wwn,
'False',
oecusitm_sql.cus_item_no,
'USD',
OECUSITM_SQL.item_price,
0,
0,
'False',
1,
1,
1,
0,
0,
1,
0,
'False',
1,
0,
1,
GETDATE(),
1,
getdate(),
1,
NEWID()
from oecusitm_sql join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
left join ItemAccounts on ItemAccounts.ItemCode = oecusitm_sql.item_no and ItemAccounts.ItemCodeAccount = oecusitm_sql.cus_item_no
where not exists(select 1 from ItemAccounts t1 where t1.ItemCode = oecusitm_sql.item_no and t1.ItemCodeAccount = oecusitm_sql.cus_item_no)
Insert works in 2008 but not SQL 2000
Posted - 07/25/2012 : 09:50:34
