1. Find account information that in customer table but not exist in another table
SELECT Acctno, BTState From rittenhouse_customer Where acctno NOT IN ( Select cust.acctno from rittenhouse_customer cust, temp_account acct WHERE cust.acctno = acct.acctno )
another way -
Select rc.acctno, rc.Acctname, rc.BTState, rc.BTZIP from rittenhouse_customer rc LEFT JOIN rittenhouse_cust_profile rcf on (rc.acctno = rcf.acctno) Where rcf.acctno IS NULL order by rc.BTState
2.Find duplicates
SELECT sku, COUNT(*) AS Expr1 FROM rittenhouse_products_1 GROUP BY sku HAVING (COUNT(*) > 1)
3. BCP and update to a table
delete from rittenhouse_products_1 go BULK INSERT Rittenhouse..rittenhouse_products_1 FROM 'c:\dataimport\RittenhouseInventory.txt' WITH ( CHECK_CONSTRAINTS, DATAFILETYPE = 'char', FIELDTERMINATOR = '~', ROWTERMINATOR = '\n' ) go --update rittenhouse_products_1, multiple 100 for price update rittenhouse_products_1 set list_price = list_price * 100 , NetPrice = NetPrice * 100 go
|