script help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

script help

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-25 : 11:02:46

I have all these scripts I need to run to create and group all the data. I was wondering if I can take all of them and create a bat file that a double click on to run it for me. Here is the code below.

1st Script InsertTables

INSERT INTO HBStatements ([Cust-no],[From-cust],PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,BudgetAmount,Memo,[Order-Code])

select
[Cust-no],[From-cust],Null AS PreBalance,Credit,Debit,budgetpayment,[Original-amt],Baldue,[Seq-no],[Cust-po],date,[Trans-code],Reference,
Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,Null AS BalanceAmount,Null AS Memo,[Order-Code]
from TransBalAfter

UNION ALL

select
[Cust-no],[From-cust],PreBalance,Credit,Debit,Null AS budgetpayment,NULL AS [Original-amt],NULL AS Baldue,NULL AS [Seq-no],NULL AS [Cust-po],date,NULL AS [Trans-code],NULL AS Reference,Chargename,ChargeAdd,ChargeCity,ChargeSt,ChargeZip,[Misc-code],SiteName,SiteAdd,SiteCity,SiteState,SiteZip,MainID,Null AS BalanceAmount,Memo, Null AS [Order-Code]
FROM TransPrebal

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2nd Script FromNEWCustUpdateSite, This updates the inserted data so the SiteName, SiteAddress, SiteCity, SiteState, and SiteZip are Tied to the From-cust not equal to Cust-no.

SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] <> msd.[Cust-no])

update msd
set
msd.SiteName = c.name,
msd.SiteAdd = c.address,
msd.SiteCity = c.City,
msd.SiteState = c.St,
msd.SiteZip = c.[Zip-Code]

From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[From-cust]
WHERE (msd.[From-cust] <> msd.[Cust-no])

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3rd Script FromNEWCustUpdateCust, This updates the inserted data so the ChargeName, ChargeAddress, ChargeCity, ChargeState, and ChargeZip are Tied to the Cust-no not equal to From-cust.

SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] <> msd.[Cust-no])

update msd
set msd.ChargeName = c.name,
msd.ChargeAdd = c.address,
msd.ChargeCity = c.City,
msd.ChargeSt = c.St,
msd.ChargeZip = c.[Zip-code]
From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[Cust-no]
WHERE (msd.[From-cust] <> msd.[Cust-no])

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4th Script FromEQCustUpdate, This updates all Site and Charge fields that are equal From-cust = Cust-no

SELECT msd.[From-cust], msd.[Cust-no], msd.PreBalance, msd.[From-cust], msd.Reference, msd.budgetpayment, msd.[Original-amt], msd.Baldue, msd.[Seq-no], msd.Memo, msd.[Cust-po], msd.date, msd.[Trans-code], msd.Credit, msd.Debit,
msd.[Misc-Code], msd.ChargeName, msd.ChargeAdd, msd.ChargeCity, msd.ChargeSt, msd.ChargeZip, msd.SiteName, msd.SiteAdd, msd.SiteCity, msd.SiteState, msd.SiteZip
FROM Service.dbo.HBStatements msd
WHERE (msd.[From-cust] = msd.[Cust-no])

update msd
set msd.ChargeName = c.name,
msd.ChargeAdd = c.address,
msd.ChargeCity = c.City,
msd.ChargeSt = c.St,
msd.ChargeZip = c.[Zip-code],
msd.SiteName = c.name,
msd.SiteAdd = c.address,
msd.SiteCity = c.City,
msd.SiteState = c.St,
msd.SiteZip = c.[Zip-Code]

From
Service.dbo.HBStatements msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[From-cust]
WHERE (msd.[From-cust] = msd.[Cust-no])

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5th Script Delete Nulls, this is where we are taking the Prebalance and debit and credits and removing the ones that are null/0 in all 3 columns.

DELETE
FROM HBStatements
WHERE PreBalance = '0' AND Credit IS Null AND Debit IS Null

DELETE
FROM HBStatements
WHERE Baldue = '0' AND Credit IS Null AND Debit IS Null

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6th Script Delete Misc-code, This Script removes all of the unwanted Misc-codes Rows we do not want to report on in our statement.

DELETE
FROM HBStatements
WHERE [Misc-code]= 'COM' OR [Misc-code] = 'CLCT' OR [Misc-code] = 'IND' OR [Misc-code] = 'UNCL'

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7th Script Update Budget Account This lets us pull from the Sabudget the prd and the amount they owe as a budget customer.

UPDATE
Service.dbo.HBStatements
SET
Service.dbo.HBStatements.BudgetAmount = Service.dbo.SaBudget.Amount
FROM HBStatements INNER JOIN
SaBudget ON HBStatements.[Cust-no] = SaBudget.[Charge-cust]
WHERE (SaBudget.[sa-prd] = '05')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8th Script Import Memo Message

update msd
set
msd.Message = c.Message

From
Service.dbo.HBStatements msd
inner join Service.dbo.Comments c on c.[Misc-Code] = msd.[Misc-Code]
---------------------------------------------------------------------------
9th Script Update Memo field for Transaction Type P%
update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Trans-code] = msd.[Trans-Code]
WHERE msd.[Trans-code] LIKE 'P%'
---------------------------------------------------------------------------
10th Script update Memo Transaction Types C's, D, F, and I's

update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Trans-code] = msd.[Trans-Code]
WHERE (msd.[Trans-code] = 'C')OR(msd.[Trans-code] = 'CA') OR
(msd.[Trans-code] = 'CJ') OR
(msd.[Trans-code] = 'CS') OR
(msd.[Trans-code] = 'DP') OR
(msd.[Trans-code] = 'F') OR
(msd.[Trans-code] = 'I') OR
(msd.[Trans-code] = 'IA') OR
(msd.[Trans-code] = 'IJ')

---------------------------------------------------------------------------
10th Script Update Memo for transactions in IS that have a Order Code
update msd
set
msd.Memo = c.Memo

From
Service.dbo.HBStatements msd
inner join Service.dbo.hbdescription c on c.[Order-code] = msd.[Order-Code]
WHERE (msd.[Trans-code] = 'IS')

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-25 : 21:38:43

You can use sqlcmd if you have it on your machine http://msdn.microsoft.com/en-us/library/ms170572.aspx Another option would be to use Powershell and use Invoke-SqlCmd http://technet.microsoft.com/en-us/library/cc281720.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources