| Author |
 Topic  |
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
 Posted - 07/18/2012 : 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
India
48076 Posts |
Posted - 07/18/2012 : 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
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 07/18/2012 : 11:16:58
|
| How do I do that? |
|
|
|
Vack
Constraint Violating Yak Guru
USA
464 Posts |
Posted - 07/18/2012 : 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 |
|
|
| |
Topic |
|
|
|
Insert issue
