Can you do this - 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

Can you do this

Author

Topic

mystical
Starting Member

10 Posts

Posted - 2012-08-16 : 14:52:45

The following query returns the single result of 201208
select dsname from u_ukul_martdb.fills where T_Id=2

I wish to use that answer to help name a backup table. I had hoped that the following would give me a back up table named u_ukul_martdb.Fills_backup_201208

CREATE TABLE u_ukul_martdb.Fills_backup_(select dsname from u_ukul_martdb.fills where T_Id=2)/* new table being created */
AS u_ukul_martdb.fills/*existing table */
WITH DATA

The above actually produces the following error message
3707 Syntax error, expected something like a ‘CHECK’ keyword between ‘(‘ and the select keyword.
Could somebody please advise me as to how to correct this.
If it is not possible to do this are there any other options.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:56:39

nope. you've to use dynamic sql for that


DECLARE @Period int

select @period=dsname from u_ukul_martdb.fills where T_Id=2

EXEC('CREATE TABLE u_ukul_martdb.Fills_backup_'+ CAST(@period AS varchar(10))/* new table being created  */
+'AS u_ukul_martdb.fills/*existing table */
WITH DATA')

EDIT: just noticed usage of WITH DATA which is not a t-sql option. Are you using MS SQL Server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-16 : 14:57:17

Are you on Microsoft SQL Server? WITH DATA is not an option available in MS SQL Server.

In MS SQL Server you cannot create a table where the script uses data selected from a table as you are attempting to do.

You can use dynamic SQL to accomplish this, but a better option would be to have a single table perhaps named u_ukul_martdb.Fills_backup and have an additional columnn for dsname. Then, you would backup data for all the dsnames into the same table and fill in dsname value in this new column. This is more scalable, easily searchable and more normalized.

mystical
Starting Member

10 Posts

Posted - 2012-08-16 : 16:00:41

Yes we have MS SQL Server

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 16:03:30

then you should be doing it in two steps

CREATE TABLE ....

to create the table

and

INSERT INTO <tablename>
SELECT...

to insert the data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources