How to create a field for temporary table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to create a field for temporary table?

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-28 : 20:50:18

This is my code:

SELECT T1.FISC_YEAR, T1.FUND, EB AS [EB/RB Ind], T2.OBJECT AS Code INTO #WorkingTable
FROM tableABC T1, tableXYZ T2
WHERE ....

I am trying to create a temporary table with fields FISC_YEAR, FUND, OBJECT from tableABC and tableXYZ.

I also want to create another field 'EB/RB_Ind' in the same temporary table with a default value 'EB' which I intend to update later.

I am not able to achieve this. Any help will be appreciated.
PKS

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-28 : 21:11:43

'EB' As [EB/RB_Ind]

Can you post your ddl for the temp table.

Edited by - ValterBorges on 04/28/2003 21:13:28

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-29 : 12:18:13

CREATE TABLE #WORKINGTABLE
(
[FISC_YEAR] CHAR(4) NOT NULL,
[FUND] CHAR(4) NOT NULL,
[EB/RB_Ind] NVARCHAR(2),
[CODE] CHAR(2) NOT NULL,
)

Any more suggestions??

Thank you.
PKS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 12:20:26

Try putting single quotes around 'EB' as Valter suggested.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 12:53:15

What error are you getting?

Could it be because there are null valuse in T1 and/or T2 and your ddl doesn't allow for those.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 13:24:47

There is no DDL. The SELECT INTO syntax *creates* a new table, it doesn't append data to an existing table.

- Jeff

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-29 : 13:46:02

That my problem I guess. sorry for not making it clear earlier. After I create the temporary table, I need to append data to it using a similar query again. I guess I should use

CREATE TABLE #tempTable

and then insert data from first query and update the [EB/RB Ind] field with 'EB'

and then insert data from the second query and update the [EB/RB Ind] field with 'RB'

Any suggestions for doing it in a different way?

Thank you.
PKS

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 14:02:57

you could do it in one swoop with a union all or case statement.

SELECT A.FISC_YEAR, A.FUND, A.[EB/RB Ind], A.Code INTO #WorkingTable
FROM
(
SELECT
T1.FISC_YEAR, T1.FUND, 'EB' AS [EB/RB Ind], T2.OBJECT AS Code
FROM tableABC T1, tableXYZ T2 WHERE ....
UNION ALL
T1.FISC_YEAR, T1.FUND, 'RB' AS [EB/RB Ind], T2.OBJECT AS Code
FROM tableABC T1, tableXYZ T2 WHERE ....
) A

or

SELECT
T1.FISC_YEAR, T1.FUND,
CASE
WHEN condition1 THEN 'EB'
WHEN condition2 THEN 'RB'
END AS [EB/RB Ind],
T2.OBJECT AS Code
INTO #WorkingTable
FROM tableABC T1, tableXYZ T2 WHERE ....

Edited by - ValterBorges on 04/29/2003 14:05:02

Edited by - ValterBorges on 04/29/2003 14:05:47

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 14:08:14

Also, it may be useful to know that:

INSERT INTO table (col1, col2, ...)
SELECT ....

inserts data from a select statement into an EXISTING table, while:

SELECT col1, col2
INTO table

completely creates a brand new table based on the columns in the SELECT statement, and inserts data from on the SELECT statement.

- Jeff

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-29 : 18:22:17

Thank you ValterBorges and Jeff for your feedback . Appreciate all your help. Will be looking forward to more help from you.

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources