Create Table Problem - 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

General SQL Server Forums

New to SQL Server Programming

Create Table Problem

Author

Topic

ScratchyCrasher
Starting Member

2 Posts

Posted - 2009-03-17 : 11:16:38

Hello All,

I'm very new to SQL server having been using iSeries Query for many years and need a helping hand if some kind soul has a couple of minutes.

Trying to create a table based upon the results of a SELECT and JOIN. I can run the SELECT on it's own just fine and get the results I'm looking for but when I stick the CREATE bit on the front I get the following error :

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.

Very sure I'm doing something pretty dumb, any help would be much appreciated.

Here's the SQL (don't laugh if it looks too bad huh?)

create table GENIUS3.TRABZON_DATA as
(select TRANSACTION_HEADER.ID, TRANS_DATE, FK_POS, CODE, NUM, FK_USER
from TRANSACTION_HEADER
join TRANSACTION_SALE on(TRANSACTION_HEADER.ID = TRANSACTION_SALE.FK_TRANSACTION_HEADER)
where TRANSACTION_HEADER.TRANS_DATE > '07-03-2009 00:01'
and TRANSACTION_HEADER.TRANS_DATE < '14-03-2009 23:59'
and TRANSACTION_HEADER.FK_STORE = 10026
and TRANSACTION_HEADER.STATUS = 0
and TRANSACTION_HEADER.PTYPE <> 2
and TRANSACTION_SALE.CODE = '1010281')

Thanks, M.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:20:28

you cant use select with create. for creating table you need to use select...into


select TRANSACTION_HEADER.ID, TRANS_DATE, FK_POS, CODE, NUM, FK_USER INTO GENIUS3.TRABZON_DATA
from TRANSACTION_HEADER 
join TRANSACTION_SALE on(TRANSACTION_HEADER.ID = TRANSACTION_SALE.FK_TRANSACTION_HEADER)
where TRANSACTION_HEADER.TRANS_DATE > '07-03-2009 00:01'
and TRANSACTION_HEADER.TRANS_DATE < '14-03-2009 23:59'
and TRANSACTION_HEADER.FK_STORE = 10026
and TRANSACTION_HEADER.STATUS = 0
and TRANSACTION_HEADER.PTYPE <> 2
and TRANSACTION_SALE.CODE = '1010281'