Insert issue - SQL Server Forums

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 issue

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-18 : 11:11:06

I want to be able to re run this script over and over and have it only insert records that don't exist.

I've tried the following. Keep getting zero rows affected. I do have a new record that should be inserted.


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
           join ItemAccounts on oecusitm_sql.item_no = ItemAccounts.ItemCode and oecusitm_sql.cus_item_no = ItemAccounts.ItemCodeAccount
           where not exists(select * from ItemAccounts t1 where t1.ItemCode = ItemAccounts.ItemCode and t1.ItemCodeAccount = ItemAccounts.ItemCodeAccount)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 11:15:49

first check in table if you've rows that satisfy the not exists condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-18 : 11:16:58

How do I do that?

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-07-18 : 11:42:13

I tried your suggestion and that does not seem to work.

if not exists(select * from ItemAccounts join oecusitm_sql on ItemAccounts.ItemCode = oecusitm_sql.item_no and
ItemAccounts.ItemCodeAccount = oecusitm_sql.cus_item_no)
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
join ItemAccounts on oecusitm_sql.item_no = ItemAccounts.ItemCode and oecusitm_sql.cus_item_no = ItemAccounts.ItemCodeAccount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources