| Author |
 Topic  |
|
|
sahu74
Posting Yak Master
USA
100 Posts |
 Posted - 04/30/2003 : 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
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 04/30/2003 : 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
USA
100 Posts |
Posted - 04/30/2003 : 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
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 04/30/2003 : 09:27:41
|
Yes I'd like to see the creation of the temp tables and everything after that.
|
|
|
|
sahu74
Posting Yak Master
USA
100 Posts |
Posted - 04/30/2003 : 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
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 04/30/2003 : 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 |
|
|
| |
Topic |
|
|
|
updating temporary tables
