updating temporary tables - 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 2000 Forums

Transact-SQL (2000)

updating temporary tables

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-30 : 01:46:26

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
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 02:38:41

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.

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-30 : 04:44:55

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

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 09:27:41

Yes I'd like to see the creation of the temp tables and everything after that.

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-30 : 13:43:53

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

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 20:02:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources