Prev: Database is in Transistion. There is already an open datareader associated with this data
Next: Using RETURN
From: tshah on 22 Jul 2008 14:58
Hello all,

I am trying to create a staging table using CREATE TABLE statement on a file
group with the file group name decided at the exectution time. Is there any
way, I can supply the file group name in the form of variable or using CTE
when I create the table using CREATE TABLE statement in a stored procedure or
in a SQL batch in SQL 2005?

If you happen to know, please show me how or direct me to the resource.

Thanks,

tshah
From: Razvan Socol on 22 Jul 2008 15:05
tshah wrote:

> Hello all,
>
> I am trying to create a staging table using CREATE TABLE statement on a
> file group with the file group name decided at the exectution time. Is
> there any way, I can supply the file group name in the form of variable
> or using CTE when I create the table using CREATE TABLE statement in a
> stored procedure or in a SQL batch in SQL 2005?
>
> If you happen to know, please show me how or direct me to the resource.
>
> Thanks,
>
> tshah

You can use dynamic sql, like this:

DECLARE @fgname sysname, @sql nvarchar(max)
SET @fgname='PRIMARY'
SET @sql='CREATE TABLE tbl (x int) ON '+QUOTENAME(@fgname)
EXEC(@sql)

--
Razvan Socol
SQL Server MVP
From: tshah on 22 Jul 2008 15:28
Thanks Razvan for your reply; it worked out for me.

Regards,

tshah.

"Razvan Socol" wrote:

> tshah wrote:
>
> > Hello all,
> >
> > I am trying to create a staging table using CREATE TABLE statement on a
> > file group with the file group name decided at the exectution time. Is
> > there any way, I can supply the file group name in the form of variable
> > or using CTE when I create the table using CREATE TABLE statement in a
> > stored procedure or in a SQL batch in SQL 2005?
> >
> > If you happen to know, please show me how or direct me to the resource.
> >
> > Thanks,
> >
> > tshah
>
> You can use dynamic sql, like this:
>
> DECLARE @fgname sysname, @sql nvarchar(max)
> SET @fgname='PRIMARY'
> SET @sql='CREATE TABLE tbl (x int) ON '+QUOTENAME(@fgname)
> EXEC(@sql)
>
> --
> Razvan Socol
> SQL Server MVP
>
 | 
Pages: 1
Prev: Database is in Transistion. There is already an open datareader associated with this data
Next: Using RETURN