February 26, 2008 at 7:28 am
Hi all,
I am trying to get the cursor to update a value in table(s) from other database(s):
Help would be sincerely appreciated!
Thank you.
DECLARE MyCursor CURSOR
FOR
(SELECT
tmpAPIBC.AUDTORG,
tmpAPIBC.CNTBTCH,
case
when tmpAPIBC.AUDTORG = 'MDB1' then (select NEXTBTCHNO+1 from MBD1..GL01)
when tmpAPIBC.AUDTORG = 'MBD2' then (select NEXTBTCHNO+1 from MBD2..GL01)
when tmpAPIBC.AUDTORG = 'MBD3' then (select NEXTBTCHNO+1 from MBD3..GL01)
when tmpAPIBC.AUDTORG = 'MBD4' then (select NEXTBTCHNO+1 from MBD4..GL01)
when tmpAPIBC.AUDTORG = 'MBD5' then (select NEXTBTCHNO+1 from MBD5..GL01)
when tmpAPIBC.AUDTORG = 'MBD6' then (select NEXTBTCHNO+1 from MBD6..GL01)
end as NxtBNo,
case
when tmpAPIBC.AUDTORG = 'MDB1' then 'MBD1..GL01'
when tmpAPIBC.AUDTORG = 'MBD2' then 'MBD2..GL01'
when tmpAPIBC.AUDTORG = 'MBD3' then 'MBD3..GL01'
when tmpAPIBC.AUDTORG = 'MBD4' then 'MBD4..GL01'
when tmpAPIBC.AUDTORG = 'MBD5' then 'MBD5..GL01'
when tmpAPIBC.AUDTORG = 'MBD6' then 'MBD6..GL01'
end as DBTble
FROM tmpAPIBC)
OPEN MyCursor
DECLARE @MyDB sysname
DECLARE @MyCnt int
DECLARE @MyBNo int
DECLARE @MyDBTble sysname
FETCH NEXT FROM MyCursor INTO @MyDB, @MyCnt, @MyBNo, @MyDBTble
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
update @MyDBTble set NEXTBTCHNO = @MyBNo
update tmpAPIBC set tmpAPIBC.CNTBTCH = @MyBNo where tmpAPIBC.AUDTORG = @MyDB
update tmpAPIBH set tmpAPIBH.CNTBTCH = @MyBNo where tmpAPIBH.AUDTORG = @MyDB
update tmpAPIBD set tmpAPIBD.CNTBTCH = @MyBNo where tmpAPIBD.AUDTORG = @MyDB
END
FETCH NEXT FROM MyCursor INTO @MyDB, @MyCnt, @MyBNo, @MyDBTble
END
CLOSE MyCursor
DEALLOCATE MyCursor
February 26, 2008 at 12:52 pm
If you would like to manipulate data among datbases, you may have to follow the 4 name vention, such as
databaseName.ownerName.tableName.columnName
February 26, 2008 at 12:57 pm
Not to be contrary - but three and four-part names anywhere other than the FROM clause is now considered deprecated. The "correct" syntax these days is to use multiple parts in the FROM to identify where the table is coming from, give it an alias - and use the alias everywhere else...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 1:10 pm
And the root problem is that you can't use variables to specify the table name. If you want the update to affect different tables depending on the variable, you'll have to use dynamic SQL
DECLARE @sSQL varchar(1000)
SET @sSQL = 'update ' + @MyDBTble + ' set NEXTBTCHNO = ' + CAST(@MyBNo as Varchar(7))
EXECUTE (@sSQL)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply