Insert works in 2008 but not SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert works in 2008 but not SQL 2000

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-25 : 09:50:34

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)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-25 : 09:56:33

Choose EITHER

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 ItemAccounts.ItemCode is null

or

from oecusitm_sql join cicmpy on oecusitm_sql.cus_no = cicmpy.debcode
where not exists(select 1 from ItemAccounts t1 where t1.ItemCode = oecusitm_sql.item_no and t1.ItemCodeAccount = oecusitm_sql.cus_item_no)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources