Creating a unique temp table.

Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go

Alter Procedure spReport_SomeFooReport
@.SearchFromThisDate datetime = null, @.SearchToThisDate datetime = null

As
Declare @.TableUniqueIdentifier varchar(80), @.SQLString varchar(5000)

set @.TableUniqueIdentifier = newid()
set @.TableUniqueIdentifier = 'Report_SomeFooReport' + @.TableUniqueIdentifier
set @.TableUniqueIdentifier = replace(@.TableUniqueIdentifier, '-', '7')
set @.SQLString = 'Create Table ' + @.TableUniqueIdentifier + ' (xxx varchar(40))'
exec @.SQLString

Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go

--------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00This looks like Sql Server. If that's the case, try using

EXEC (@.SQLString)|||RedNeckGeek is right, the EXEC without the parentheses means you're calling a stored procedure.|||also, it's bad form to create permanent tables on the fly from sprocs.

If your database schema is well designed, there would be no need for this. Perhaps use a temp table instead?

Or use a single permanent table for all your reports since they all have exactly the same structure, with just a single column, xxx varchar(40). You could add another column to identify the report instance.|||I would like to know howyou plan to reference that table in the future|||I would like to know howyou plan to reference that table in the future

the only possible way I know of would be to return the table name as an out param from the sproc.

Creating a unique constarint on a multiple null column

HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

Yes.

Since a Primary Key CONSTRAINT requires NOT NULL values, a UNIQUE index is the best alternative method to force a constraint on columns that can contain NULL values.

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.
|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>
|||Your welcomes. ;-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>