Writing large SQL scripts advice

Shicky

Shicky

Shicky

Associate
Joined
13 Jan 2007
Posts
2,424
Location
Belfast,Northern Ireland
Long story short I have a rather huge SQL script to write...or it seems unavoidable to me anyway. Basically when dealing with a support item it was discovered a fix had skipped out a bunch of records.

Now in order to correct this I need to write an update script to fix all the information that was missed. The problem is this change will touch on over 20 tables. Do I really have to write some huge ass script, declare a bunch of variables at the top and update each table one by one?

Is there a better way to do this?
 
Without knowing your schema, or exactly what changes, and the relationships between each record this is next to impossible to tell you.

*licks finger and puts it in the air*

Do I really have to write some huge ass script, declare a bunch of variables at the top and update each table one by one?
Click to expand...
Probably yeah.
 
Sorry I'm a bit new to this so just trying to get my head around linking everything up.

At the moment I have a normal query - SELECT FROM WHERE which basically finds about 2000 records that I need to update which link across several tables.

Can someone tell me how I can link this simple query to something else so I can basically execute several stored procedures, all in the same script? But only affecting the records returned by my simple query?

Apologies, that probably sounds as clear as mud!
 
So below is the select query. The rows that I get from this, I will need to use information from to do several inserts/updates into other tables. Stored procedures exist for some so I would like to use those where I can, does this sound remotely plausible?

Code: 
SELECT [MembershipTermID]
  ,[MemberStatusProgKey]
  ,[StartDate]
  ,[EndDate]
  ,[AdditionalDiscount]
  ,[EntryDateTime]
  ,[UpdateDateTime]
  ,[MembershipID]
  ,[AgentID]
  ,[PlanVersionID]
  ,[ForceThroughReference]
  ,[IsForceThrough]
  ,[NextTermPrePaid]
  ,[IsBillingMonthly]
  ,[CICSMEMBERNUM]
  ,[CICSHISTORY]
  ,[TMPSeqNoColumn]
  ,[LastPaymentDate]
  ,[PaidToDate]
  ,[IsIndeterminate]
  ,DATEDIFF(MONTH, PaidToDate, GETDATE()) as MonthsDifference
  ,dbo.FullMonthsSeparation (PaidToDate, GETDATE())
FROM [Apollo].[dbo].[MembershipTerm] 
WHERE MemberStatusProgKey='DORMANT' 
AND IsBillingMonthly=1 
AND dbo.FullMonthsSeparation (PaidToDate, GETDATE()) >= 2
 
With changes such as this using large amounts of memory it's always best to run them in batches.

What you are asking will take some time to run (and yes will be a large script), however in the scheme of things will be relativly small. The largest database modification/update script I made was required to update latitude and longitude points for 19 million rows.. needless to say it took around 9 hours to run.

The suggest you have made does sound feasible IMO.
 
Frankly my mind is just boggled as to what the structure of this should be.

Do I run my select query first, then execute a bunch of stored procedures? Will this only affect the rows that have been pulled from the select query?

A side question would be is it possible to get values from a previously executed stored procedure?

Such as:
Code: 
EXEC spCancellationDetailInsert 'SYSTEM', 0, 0.0, [EndDate], @TodaysDate, @TodaysDate,'CANC_DORMANT'

EXEC spMembershipTermCancellationDetailInsert 'SYSTEM', 0, <CancellationId>???, [MembershipTermID]
where cancellationID would have been created from the previously executed stored procedure?

Another is the ones in [] are from the select query itself, is that the correct way to reference them? Going to have to go through some counter so it executes all these stored procedures for each row

Feel a bit out of my depth!
 
Using SQL Server 2008 I believe and using SQL Server Management studio to write these queries etc. Not too sure what you mean by server side coding so I'm guessing the answer is no
 
That's fine, unfortuantly I'm not the best with SQL Server 2008 or its related software suites.

However, head over to StackOverflow which is a large programming Q&A community, explain your question fully and link back here if needed. I could almost guarantee you'll get a response to this sort of question :)

Be sure to check their rules out before posting (you'll get a better response that way)
 
something like this may work for you:
Code: 
1. put all the records you want to action into a temporary table:

CREATE TABLE #MYTABLE
(
RowID int identity not null,
UserID int,
Name nvarchar(20) ....etc
)
-- NB. the RowID will be used to process each record

2. loop through each record and action it as you wish..

while exists(select 1 from #MYTABLE)
    begin
    declare @rowid int
    declare @UserID int
    declare @Name nvarchar(20)

    select 
         top 1 @rowid = rowid, @UserID = UserID, @Name = Name 
    from 
        #MYTABLE
    -- now you've got the rowid and all col values for this record in temp variables prefix

    -- perform updates on this record in another table eg:
    update someothertable
        set somecol = somevalue
    where
        UserID = @UseriD

   --after performing updates, delete this record from the temp table and loop                                                              -  --to the next one
    delete from #MYTABLE where rowid = @rowid
    end


Hopefully that makes sense?
 
You must log in or register to reply here.
Back
Top Bottom