SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 updating temporary tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sahu74
Posting Yak Master

USA
100 Posts

Posted - 04/30/2003 :  01:46:26  Show Profile  Reply with Quote
I am creating 2 temporary tables

#BPS_TABLE and #BPD_TABLE

I am trying to update some fields in #BPS_TABLE with values from #BPD_TABLE.

I am using the following syntax:

UPDATE #BPS_TABLE
SET BPS_CURR_BASE_SAL = TOTD_CURR_BASE_SAL,
BPS_FINAL_BGT_SAL = FINAL_BGT_SAL
FROM #BPS_TABLE AS T3
JOIN #BPD_TABLE AS T4
ON T3.BPS_ACCOUNT_NBR10 = T4.ACCOUNT_NBR10
AND T3.BPS_XOBJECT = T4.XOBJECT
AND T3.BPS_CLASS = T4.CLASS

I am getting this error:

Invalid object name '#BPD_TABLE'.

Any suggestions as to what I am doing wrong?

PKS

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 04/30/2003 :  02:38:41  Show Profile  Reply with Quote
CREATE TABLE #BPS_TABLE
(
BPS_ACCOUNT_NBR10 INTEGER,
BPS_XOBJECT INTEGER,
BPS_CLASS INTEGER,
BPS_CURR_BASE_SAL MONEY,
BPS_FINAL_BGT_SAL MONEY
)

CREATE TABLE #BPD_TABLE
(
ACCOUNT_NBR10 INTEGER,
XOBJECT INTEGER,
CLASS INTEGER,
TOTD_CURR_BASE_SAL MONEY,
FINAL_BGT_SAL MONEY
)


UPDATE #BPS_TABLE
SET BPS_CURR_BASE_SAL = TOTD_CURR_BASE_SAL,
BPS_FINAL_BGT_SAL = FINAL_BGT_SAL
FROM #BPS_TABLE AS T3
JOIN #BPD_TABLE AS T4
ON T3.BPS_ACCOUNT_NBR10 = T4.ACCOUNT_NBR10
AND T3.BPS_XOBJECT = T4.XOBJECT
AND T3.BPS_CLASS = T4.CLASS


NO errors here.

Can you post what you're doing before the update.



Go to Top of Page

sahu74
Posting Yak Master

USA
100 Posts

Posted - 04/30/2003 :  04:44:55  Show Profile  Reply with Quote
Thank you. I am inserting a set of records into the first temp table on certain fields. I need to update the other fields with values I generate from the other temp table. Is this what you wanted to know or the code.

Thank you again for the help.

PKS

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 04/30/2003 :  09:27:41  Show Profile  Reply with Quote
Yes I'd like to see the creation of the temp tables and everything after that.

Go to Top of Page

sahu74
Posting Yak Master

USA
100 Posts

Posted - 04/30/2003 :  13:43:53  Show Profile  Reply with Quote
This is what I have done finally.. Seems to work. Would like to know if you have any other better way of coding this. Appreciate all your help.

PKS.


CREATE TABLE #BPS_TABLE
(
[BPS_ORGID] varchar(2),
[BPS_ENTITY] varchar(1),
[BPS_FISCAL_YR] varchar(4),
[BPS_ACCOUNT_NBR10] varchar(10),
[BPS_XOBJECT] varchar(2),
[BPS_CLASS] varchar(1),
[BPS_EXPB_REVB_IND] varchar(2),
[BPS_CURR_BASE_BGT] Decimal(14,0),
[BPS_DPT_INIT_BGT] Decimal(14,0),
[BPS_DPT_REQ_BGT] Decimal(14,0),
[BPS_PRP_BGT] Decimal(14,0),
[BPS_FINAL_BGT] Decimal(14,0),
[BPS_NOT_EQUAL] varchar(10),
[BPS_CURR_BASE_SAL] Decimal(14,0),
[BPS_DPT_INIT_BGT_SAL] Decimal(14,0),
[BPS_DPT_REQ_BGT_SAL] Decimal(14,0),
[BPS_PRP_BGT_SAL] Decimal(14,0),
[BPS_FINAL_BGT_SAL] Decimal(14,0)
)

CREATE TABLE #BPD_TABLE
(
[BPD_ORGID] varchar(2),
[BPD_ENTITY] varchar(1),
[BPD_FISCAL_YR] varchar(4),
[BPD_ACCOUNT_NBR10] varchar(10),
[BPD_XOBJECT] varchar(2),
[BPD_CLASS] varchar(1),
[BPD_CURR_BASE_SAL] Decimal(14,0),
[BPD_DPT_INIT_BGT_SAL] Decimal(14,0),
[BPD_DPT_REQ_BGT_SAL] Decimal(14,0),
[BPD_PRP_BGT_SAL] Decimal(14,0),
[BPD_FINAL_BGT_SAL] Decimal(14,0)
)

SET @SQL1 =
'SELECT *
FROM openquery(ServerA, ''select ORGID, ENTITY, FISCAL_YR, ACCOUNT_NBR10, XOBJECT, CLASS, EXPB_REVB_IND,
CURR_BASE_BGT, DPT_INIT_BGT, DPT_REQ_BGT, PRP_BGT, FINAL_BGT
FROM BUDGET.DBO.BDBPSCTB
WHERE ORGID = ''''TT'''''')
WHERE ENTITY ='''+ @ENTITY + '''
AND FISCAL_YR =''' + @FYEAR + '''
AND EXPB_REVB_IND = ''EB''
AND XOBJECT < ''03''
ORDER BY ACCOUNT_NBR10, XOBJECT, CLASS'

--PRINT @SQL1

INSERT INTO #BPS_TABLE ([BPS_ORGID], [BPS_ENTITY], [BPS_FISCAL_YR], [BPS_ACCOUNT_NBR10], [BPS_XOBJECT],
[BPS_CLASS], [BPS_EXPB_REVB_IND], [BPS_CURR_BASE_BGT], [BPS_DPT_INIT_BGT], [BPS_DPT_REQ_BGT], [BPS_PRP_BGT],
[BPS_FINAL_BGT])

EXEC (@SQL1)

--SELECT * FROM #BPS_TABLE


SET @SQL2 =
'SELECT T1.ORGID , T1.ENTITY, T1.FISCAL_YR, T1.ACCOUNT_NBR10, T1.XOBJECT, T1.CLASS,
SUM (CURR_BASE_SAL) as [TOTD_CURR_BASE_SAL], [DPT_INIT_BGT_SAL], [DPT_REQ_BGT_SAL],
[PRP_BGT_SAL], [FINAL_BGT_SAL]
FROM openquery(serverA, ''select ORGID, ENTITY, FISCAL_YR, ACCOUNT_NBR10, XOBJECT, CLASS, CURR_BASE_SAL ,
[DPT_INIT_BGT_SAL], [DPT_REQ_BGT_SAL], [PRP_BGT_SAL], [FINAL_BGT_SAL]
From BUDGET.DBO.BDBPDCTB
WHERE ORGID = ''''TT'''''') AS T1,
openquery(serverA, ''select ORGID, ENTITY, FISCAL_YR, ACCOUNT_NBR10, XOBJECT, CLASS, EXPB_REVB_IND,
CURR_BASE_BGT, DPT_INIT_BGT, DPT_REQ_BGT, PRP_BGT, FINAL_BGT
FROM BUDGET.DBO.BDBPSCTB
WHERE ORGID = ''''TT''''
AND ENTITY =''''' + @ENTITY + '''''
AND FISCAL_YR =''''' + @FYEAR + '''''
AND EXPB_REVB_IND = ''''EB''''
AND XOBJECT < ''''03''''
ORDER BY ACCOUNT_NBR10, XOBJECT, CLASS'') AS T2
WHERE T1.ENTITY = T2.ENTITY
AND T1.FISCAL_YR = T2.FISCAL_YR
AND T1.ACCOUNT_NBR10 = T2.ACCOUNT_NBR10
AND T1.XOBJECT = T2.XOBJECT
AND T1.CLASS = T2.CLASS
GROUP BY T1.ORGID, T1.ENTITY, T1.FISCAL_YR, T1.ACCOUNT_NBR10, T1.XOBJECT, T1.CLASS,
[DPT_INIT_BGT_SAL], [DPT_REQ_BGT_SAL],[PRP_BGT_SAL], [FINAL_BGT_SAL]'
--PRINT @sql2

INSERT INTO #BPD_TABLE
EXEC (@SQL2)

--SELECT * FROM #BPD_TABLE


UPDATE #BPS_TABLE
SET BPS_CURR_BASE_SAL = BPD_CURR_BASE_SAL,
BPS_DPT_INIT_BGT_SAL = BPD_DPT_INIT_BGT_SAL,
BPS_DPT_REQ_BGT_SAL = BPD_DPT_REQ_BGT_SAL,
BPS_PRP_BGT_SAL = BPD_PRP_BGT_SAL,
BPS_FINAL_BGT_SAL = BPD_FINAL_BGT_SAL
FROM #BPS_TABLE AS T3
JOIN #BPD_TABLE AS T4
ON T3.BPS_ORGID = T4.BPD_ORGID
AND T3.BPS_ENTITY = T4.BPD_ENTITY
AND T3.BPS_FISCAL_YR = T4.BPD_FISCAL_YR
AND T3.BPS_ACCOUNT_NBR10 = T4.BPD_ACCOUNT_NBR10
AND T3.BPS_XOBJECT = T4.BPD_XOBJECT
AND T3.BPS_CLASS = T4.BPD_CLASS

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 04/30/2003 :  20:02:54  Show Profile  Reply with Quote
Why are you using dynamic sql I don't see why you need it.
Also why use openquery and not just create a linked server and use the 4 part naming convention Server.Catalog.Schema.Object?





Edited by - ValterBorges on 04/30/2003 20:07:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000