Error near 'AS' for CREATE TABLE statement

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 2005 Forums

Transact-SQL (2005)

Error near 'AS' for CREATE TABLE statement

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-07-17 : 06:28:27

I keep getting an Error near statement 'AS' in the following code.

I can't work out what is wrong. Can anyone let me know?

-------------------------------------------

CREATE PROCEDURE procSubmissionDeadline AS

/* Clear any resubmissions */
EXEC procProcessSubmittedCPsBeingValidated

CREATE TABLE tmpCPs  AS (SELECT * FROM CPsSubmissionThisMonth)

UPDATE tbl_tmp_CP
SET [Completed] = 2
WHERE [Completed] = 1 AND
	EXISTS 
	(
		SELECT * 
		FROM tmpCPs
		WHERE tmpCPs.CP = tbl_tmp_CP.CP
	)

DROP TABLE tmpCPs

GO

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-17 : 06:51:16

Your create table statement is incorrect. You should right-click on the table CPsSubmissionThisMonth and choose "script table as". This will give you the proper syntax and save you a lot of typing. You should also look at BOL under CREATE TABLE -- always a good place to start. Why are you making a copy of CPsSubmissionThisMonth and then selecting from the copy?

UPDATE tbl_tmp_CP
SET [Completed] = 2
WHERE [Completed] = 1 AND
EXISTS
(
SELECT *
FROM CPsSubmissionThisMonth tmpcps
WHERE tmpCPs.CP = tbl_tmp_CP.CP
)

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 06:54:53

quote:
CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)

I have never seen this syntax before. Is this a valid syntax for MS SQL ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-17 : 07:01:48

quote:
Originally posted by adjones1980

I keep getting an Error near statement 'AS' in the following code.

I can't work out what is wrong. Can anyone let me know?

-------------------------------------------

CREATE PROCEDURE procSubmissionDeadline AS

/* Clear any resubmissions */
EXEC procProcessSubmittedCPsBeingValidated

CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)

UPDATE tbl_tmp_CP
SET [Completed] = 2
WHERE [Completed] = 1 AND
EXISTS
(
SELECT *
FROM tmpCPs
WHERE tmpCPs.CP = tbl_tmp_CP.CP
)

DROP TABLE tmpCPs

GO

CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)
should be

SELECT * INTO tmpCPs FROM CPsSubmissionThisMonth

in SQL Server

But better create table tmpCPs and then insert data to it

Create table tmpCPs(col1............)
Insert into tmpCPs(columns)
Select columns from CPsSubmissionThisMonth

Madhivanan

Failing to plan is Planning to fail

adjones1980
Starting Member

36 Posts

Posted - 2007-07-17 : 07:03:58

I need to make a copy as CPsSubmissionThisMonth is a VIEW. I basically need to capture the original content of the VIEW as it will change during the update process if I don't.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-17 : 07:04:10

quote:
Originally posted by khtan

quote:
CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)

I have never seen this syntax before. Is this a valid syntax for MS SQL ?

KH
[spoiler]Time is always against us[/spoiler]

It is ORACLE and MYSQL syntax

Madhivanan

Failing to plan is Planning to fail

adjones1980
Starting Member

36 Posts

Posted - 2007-07-17 : 07:04:26

quote:
Originally posted by khtan

quote:
CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)

I have never seen this syntax before. Is this a valid syntax for MS SQL ?

KH
[spoiler]Time is always against us[/spoiler]

What should the right syntax be then?

adjones1980
Starting Member

36 Posts

Posted - 2007-07-17 : 07:06:54

quote:
Originally posted by madhivanan

CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)
should be

SELECT * INTO tmpCPs FROM CPsSubmissionThisMonth

in SQL Server

But better create table tmpCPs and then insert data to it

Create table tmpCPs(col1............)
Insert into tmpCPs(columns)
Select columns from CPsSubmissionThisMonth

Madhivanan

Failing to plan is Planning to fail

Why whould it be better? It means I have to write more

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-07-17 : 07:14:20

quote:
--------------------------------------------------------------------------------
CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)
--------------------------------------------------------------------------------

Instead of this can work out with this Query

SELECT * INTO tmpCPs FROM CPsSubmissionThisMonth WHERE 1 = 2

will create the table with structure of table CPsSubmissionThisMonth ...
without data

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-17 : 07:45:31

quote:
Originally posted by adjones1980

quote:
Originally posted by khtan

quote:
CREATE TABLE tmpCPs AS (SELECT * FROM CPsSubmissionThisMonth)

I have never seen this syntax before. Is this a valid syntax for MS SQL ?

KH
[spoiler]Time is always against us[/spoiler]

What should the right syntax be then?

Read my first reply

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-17 : 08:29:32

Is CPsSubmissionThisMonth a view on tbl_tmp_cp?

adjones1980
Starting Member

36 Posts

Posted - 2007-07-17 : 09:06:55

Thanks Madhivanan... worked a treat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources