DECLARE @SourceLogin VARCHAR(100) = 'ADEMIN@SBDEMO',
@SourceBBCD VARCHAR(500) = 'TAL;TEST;ADEMIN;2',
@DestinationLogin VARCHAR(100) = 'ADEMIN2@SBDEMO',
@DestinationBBCD VARCHAR(MAX) = 'TAL;TEST;ADEMIN2;11',
@TSNotify CHAR(1)='N'
IF OBJECT_ID('tempdb.dba.#SourceBBCD' ) IS NOT NULL DROP TABLE #SourceBBCD
IF OBJECT_ID('tempdb.dba.#user_account_perm_tmp') IS NOT NULL DROP TABLE #user_account_perm_tmp
IF OBJECT_ID('tempdb.dba.#DestinationBBCD' ) IS NOT NULL DROP TABLE #DestinationBBCD
DECLARE @DefaultServerID INT,
@logid bigint ,
@serverName varchar(50)
DECLARE @datalist VARCHAR(500),
@ind INT,
@SourceAccountID BIGINT,
@DestinationAccountID BIGINT ,
@Date datetime
DECLARE @sourceUser VARCHAR(50) = 'ADEMIN' ,
@sourceDomain VARCHAR(50) = 'SBDEMO' ,
@sourceUserID int,
@sourceDomainID INT
DECLARE @DestinationUser VARCHAR(50) = 'ADEMIN2',
@DestinationDomain VARCHAR(50) = 'SBDEMO' ,
@DestinationUserID int,
@DestinationDomainID INT
DECLARE @error varchar(5000),
@DestinationBBCDParsed VARCHAR(250)
DECLARE @Firm VARCHAR(100) = 'S',
@bank VARCHAR(100) = 'TAL',
@Branch VARCHAR(100) = 'TEST',
@Cust VARCHAR(100) = 'ADEMIN',
@Deposit VARCHAR(100) = '2'
SELECT @Date=GetDate(),
@error=''
------------------
PRINT 'Source User = ' +@sourceUser + CHAR(10) + 'Destination User = ' + @DestinationUser
-- select data from source table into temporary source table ---
----------------------------------------------------------------
SELECT Firm=@firm,
firmid,
Bank=@bank,
bankid,
branch=@branch,
branchID,
customer=@Cust,
custid,
account=@Deposit,
accountID,
serverId,
talmachine,
Currency,
Notes,
Owner,
Zero,
MarginSourceID,
BrokerMarginRuleSetID,
HouseMarginRuleSetID,
SuitabilityRuleID,
ForexRuleId,
MinShortSalePrice,
OptionCode,
AddPosScalp
INTO #SourceBBCD
FROM vw_deposit_info
WHERE firm=@firm AND
bank=@bank AND
branch=@branch AND
Customer=@Cust AND
account=@Deposit
----------------------
--select * from #SourceBBCD
----------------------
DECLARE @AccountLine VARCHAR(250)
SET @AccountLine=''
CREATE TABLE
#DestinationBBCD
(
Firm varchar(50),
firmid INT ,
Bank varchar(50),
bankid int,
branch varchar(50),
branchID int ,
customer varchar(50),
custid int,
account varchar(50),
accountID INT,
serverId INT,
talmachine varchar(50),
Currency varchar(10),
Notes varchar(255),
Owner varchar(50),
Zero int,
MSourceID int,
BrMarID INT,
HouseMarID INT,
SuitID INT,
ForexID INT,
MinShortPrice Decimal(14,4),
OptCode varchar(4),
AddPosScalp INT
)
SET @Firm ='S'
SET @bank ='TAL'
SET @Branch ='TEST'
SET @Cust ='ADEMIN'
SET @Deposit ='11'
------------------------------------
INSERT INTO #DestinationBBCD
(
Firm ,
firmid,
Bank ,
bankid,
branch,
branchID,
customer ,
custid,
account ,
accountID,
serverId,
talmachine,
Currency,
Notes,
Owner,
Zero,
MSourceID,
BrMarID,
HouseMarID,
SuitID,
ForexID,
MinShortPrice,
OptCode,
AddPosScalp
) SELECT Firm=@firm,
firmid,
Bank=@bank,
bankid,
branch=@branch,
branchID,
customer=@Cust,
custid,
account=@Deposit,
accountID,
serverId,
talmachine,
Currency,
Notes,
Owner,
Zero,
MarginSourceID,
BrokerMarginRuleSetID,
HouseMarginRuleSetID,
SuitabilityRuleID,
ForexRuleId,
MinShortSalePrice,
OptionCode,
AddPosScalp
FROM vw_deposit_info
WHERE firm=@firm AND
bank=@bank AND
branch=@branch AND
account=@Deposit AND
Customer=@Cust
--SELECT * FROM #DestinationBBCD
IF @@RowCount=0
BEGIN
PRINT 'WARNING: Account '+ @datalist+' Not Exists and will be created by a script'
INSERT INTO #DestinationBBCD ( Firm,firmid, Bank,bankid, Branch, branchID,Customer, custid,Account,accountID)
VALUES(@firm ,0,@bank,0,@branch,0,@Cust,0,@Deposit,0)
END
-- first FirmID is inserted into firmID field based on ID from the Firms table
IF EXISTS( SELECT 1 FROM #DestinationBBCD where accountid=0)
BEGIN
UPDATE #DestinationBBCD
SET firmid=d.firmid
FROM vw_Deposit_info d
WHERE d.Firm= #DestinationBBCD.firm
SELECT * FROM #DestinationBBCD
-- and we are trying to insert bank right away by quering for bankid with desired firmid and bank name.
UPDATE #DestinationBBCD
SET bankid=b.bankid
FROM banks b
WHERE b.firmid= #DestinationBBCD.firmid AND b.bank= #DestinationBBCD.bank
END
-- if however bank does not exist i.e. accountID = 0 we have to create a bank
IF EXISTS( SELECT 1 FROM #DestinationBBCD where accountid=0 and bankid=0)
BEGIN
INSERT INTO banks (firmid,bank)
SELECT DISTINCT firmid, BANK
FROM #DestinationBBCD
-- once bank is created #DestinationBBCD is updated with new Bank ID.
UPDATE #DestinationBBCD
SET bankid=b.bankid
FROM banks b
WHERE b.firmid= #DestinationBBCD.firmid AND b.bank= #DestinationBBCD.bank
END
-- checking whether bank has been inserted.
SELECT * FROM #DestinationBBCD
-- now branch should be created or updated with assmption that Branch name already exist
UPDATE #DestinationBBCD
SET branchid=b.branchID
FROM branches b
WHERE b.firmid= #DestinationBBCD.firmid AND b.bankid= #DestinationBBCD.bankid AND b.branch= #DestinationBBCD.branch
-- after previous step branch id should be inserted if not we check
IF EXISTS( SELECT 1 FROM #DestinationBBCD where accountid=0 and branchID=0)
BEGIN
INSERT INTO branches (firmid,bankid, Branch)
SELECT DISTINCT firmid, bankid, Branch
FROM #DestinationBBCD
WHERE not exists ( SELECT 1
FROM branches
WHERE #DestinationBBCD.firmid=branches.firmid
AND #DestinationBBCD.bankid =branches.bankid
AND #DestinationBBCD.branch =branches.branch )
END
-- checking whether bank has been inserted.
SELECT * FROM #DestinationBBCD
-- assuming that FBB existed try to get customer
UPDATE #DestinationBBCD
SET custid=b.custid
FROM Customers b
WHERE b.firmid= #DestinationBBCD.firmid AND b.bankid= #DestinationBBCD.bankid
AND b.branchid= #DestinationBBCD.branchid
AND b.Customer= #DestinationBBCD.Customer
AND #DestinationBBCD.custid =0
IF EXISTS( SELECT 1 FROM #DestinationBBCD where custid=0)
BEGIN
INSERT INTO customers (firmid,bankid, Branchid, Customer)
SELECT DISTINCT firmid, bankid, Branchid,#DestinationBBCD.customer
FROM #DestinationBBCD
WHERE not exists ( SELECT 1
FROM customers
WHERE #DestinationBBCD.firmid=customers.firmid AND
#DestinationBBCD.bankid =customers.bankid AND
#DestinationBBCD.branchid =customers.branchid AND
#DestinationBBCD.Customer =customers.Customer)
END
SELECT * FROM #DestinationBBCD
-- creating the account assuming everything exist in the old db
UPDATE BBCD
SET AccountID=b.AccountID, serverId=b.serverId, talmachine=b.talmachine
FROM vw_deposit_Info b
JOIn #DestinationBBCD BBCD
ON b.firmid= bbcd.firmid AND b.bankid= bbcd.bankid
AND b.branchid= bbcd.branchid
AND b.Custid=bbcd.Custid
AND b.account=bbcd.account
AND b.accountid>0
--if however accountid=0 i.e. does not exist then
IF EXISTS( SELECT 1 FROM #DestinationBBCD where accountid=0)
BEGIN
SELECT @DefaultServerID =SERVERID , @serverName=talmachine
FROM #SourceBBCD
INSERT INTO Deposits(
firmid,
bankid,
Branchid,
Custid,
Account,
ServerID,
Currency,
Notes,
Owner,
Zero,
MarginSourceID,
BrokerMarginRuleSetID,
HouseMarginRuleSetID,
SuitabilityRuleSetID,
ForexRuleId,
MinShortSalePrice,
OptionCode,
AddPosScalp
)
SELECT DISTINCT #DestinationBBCD.firmid,
#DestinationBBCD.bankid,
#DestinationBBCD.Branchid,
#DestinationBBCD.custid,
#DestinationBBCD.Account,
@DefaultServerID,
s.Currency,
s.Notes,
s.Owner,
s.Zero,
s.MarginSourceID,
s.BrokerMarginRuleSetID,
s.HouseMarginRuleSetID,
s.SuitabilityRuleID,
s.ForexRuleId,
s.MinShortSalePrice,
s.OptionCode,
s.AddPosScalp
FROM #DestinationBBCD,
#SourceBBCD s
WHERE NOT EXISTS (
SELECT 1
FROM vw_deposit_Info d
WHERE #DestinationBBCD.firmid=d.firmid AND
#DestinationBBCD.bankid =d.bankid AND
#DestinationBBCD.branchid =d.branchid AND
#DestinationBBCD.Custid =d.Custid AND
#DestinationBBCD.account =d.account
)
END
-- checking whether bank has been inserted.
SELECT * FROM #DestinationBBCD