I posted this question yesterday and for some reason i cant get it to work. I got many good answers but they dont meet the scope of what i need. Basically i need to run a query that will loop through all the customers and grab their Acct_Balance and set it to 0 but before I set it to 0 i need to add an entry with that negative amount to the AR_Transactions table. So for real numbers sake if a customer has -200 in the Acct_Balance field in the customer table then i need to add an entry or 200 to bring the value to 0. Kind of like a line item to 0 it out. And vice verca for if the customer has 200 in the in the Acct_Balance field in the customer table then i need to add an entry or -200 to zero that out as well. I tried a few things. Here is one of the examples i tried.
BEGIN TRANSACTION
INSERT INTO [cresql].[dbo].[AR_Transactions]
(Trans_ID, DateTime , Dirty, Store_ID, Trans_Type, Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Balance )
SELECT (SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions]), DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199, CustNum, -Acct_Balance, Acct_Balance
FROM [cresql].[dbo].[Customer]
WHERE Acct_Balance <> 0
UPDATE [cresql].[dbo].[Customer] SET Acct_Balance = 0
WHERE Acct_Balance <> 0
COMMIT TRANSACTION
but i got this error
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'pkAR_Transactions'. Cannot insert duplicate key in object 'dbo.AR_Transactions'.
The statement has been terminated.
I tried to run the insert statement and all works fine but with this query it fails....Also the pkAR_Transactions primary key is apparantly not non-increment which is why i am doing this hack to get the last entry in that field
Here is my db structure for the two tables..
AR_Transactions table
column name type allow null
Trans_ID bigint Unchecked
DateTime datetime Unchecked
Cashier_ID nvarchar(50) Checked
CustNum nvarchar(12) Unchecked
Trans_Type nvarchar(2) Unchecked
Prev_Cust_Balance money Checked
Prev_Inv_Balance money Checked
Trans_Amount money Unchecked
Payment_Method nvarchar(4) Checked
Payment_Info nvarchar(20) Checked
Description nvarchar(38) Checked
Invoice_Number bigint Unchecked
Store_ID nvarchar(10) Unchecked
Dirty bit Unchecked
Station_ID nvarchar(5) Checked
Payment_Type smallint Checked
Customers Table
column name type allow null
CustNum nvarchar(12) Unchecked
First_Name nvarchar(15) Checked
Last_Name nvarchar(15) Unchecked
Company nvarchar(30) Checked
Address_1 nvarchar(30) Checked
Address_2 nvarchar(30) Checked
City nvarchar(20) Checked
State nvarchar(12) Checked
Zip_Code nvarchar(10) Checked
Phone_1 nvarchar(15) Checked
Phone_2 nvarchar(15) Checked
CC_Type nvarchar(5) Checked
CC_Num nvarchar(50) Checked
CC_Exp nvarchar(8) Checked
Discount_Level nvarchar(1) Unchecked
Discount_Percent real Unchecked
Acct_Open_Date datetime Checked
Acct_Close_Date datetime Checked
Acct_Balance money Checked
Acct_Max_Balance money Checked
Bonus_Plan_Member bit Unchecked
Bonus_Points int Checked
Tax_Exempt bit Unchecked
Member_Exp datetime Checked
Dirty bit Unchecked
Phone_3 nvarchar(15) Checked
Phone_4 nvarchar(15) Checked
EMail nvarchar(50) Checked
County nvarchar(30) Checked
Def_SP nvarchar(10) Checked
CreateDate datetime Checked
Referral nvarchar(20) Checked
Birthday datetime Checked
Last_Birthday_Bonus datetime Checked
Last_Visit datetime Checked
Require_PONum bit Unchecked
Max_Charge_NumDays int Checked
Max_Charge_Amount money Checked
License_Num nvarchar(20) Checked
ID_Last_Checked datetime Checked
Next_Start_Date datetime Checked
Checking_AcctNum nvarchar(20) Checked
PrintNotes bit Unchecked
Loyalty_Plan_ID bigint Checked
Tax_Rate_ID int Checked
Bill_To_Name nvarchar(30) Checked
Contact_1 nvarchar(30) Checked
Contact_2 nvarchar(30) Checked
Terms nvarchar(15) Checked
Resale_Num nvarchar(15) Checked
Last_Coupon datetime Checked
Account_Type smallint Checked
ChargeAtCost bit Checked
Disabled bit Checked
ImagePath nvarchar(255) Checked
License_ExpDate datetime Checked
TaxID nvarchar(20) Checked
