Very Computer

Is there anyway I can copy the structure of a table into a temporary table
without getting constraint and identity information?

currently I am trying this on a table that has an identity column:

select *
into #businessInfo
from tblBusinessInfo
where 1=0

This first statement gets me the identity column. Later, depending on the
results of other queries, I need to populate this table with non-sequential
values in the identity column and then export it. Needless to say, I can't
do inserts because the identity is being copied to the temporary table. I
also can't manually build the temporary table because I need to be able to
accomidate changing table structures.

Am I thinking about this wrong? Can this be done with a SQL 7 table
variable?

Please help!

Luke

Quote:> currently I am trying this on a table that has an identity column:

> select *
> into #businessInfo
> from tblBusinessInfo
> where 1=0

> This first statement gets me the identity column. Later, depending on the
> results of other queries, I need to populate this table with
non-sequential
> values in the identity column and then export it. Needless to say, I can't
> do inserts because the identity is being copied to the temporary table.

ALTER TABLE #businessInfo DROP COLUMN identityColumnName
ALTER TABLE #businessInfo ADD identityColumnName INT

Otherwise,

CREATE TABLE #businessInfo
 (column list WITH identity column but WITHOUT identity property)

Then, INSERT #businessInfo ...

Quote:> Am I thinking about this wrong? Can this be done with a SQL 7 table
> variable?

--
AB
www.aspfaq.com

Good answer. Thanks, you saved me a lot of worrying.

Luke

Quote:> > currently I am trying this on a table that has an identity column:

> > select *
> > into #businessInfo
> > from tblBusinessInfo
> > where 1=0

> > This first statement gets me the identity column. Later, depending on
the
> > results of other queries, I need to populate this table with
> non-sequential
> > values in the identity column and then export it. Needless to say, I
can't
> > do inserts because the identity is being copied to the temporary table.

> Can you then do

> ALTER TABLE #businessInfo DROP COLUMN identityColumnName
> ALTER TABLE #businessInfo ADD identityColumnName INT

> Otherwise,

> CREATE TABLE #businessInfo
>  (column list WITH identity column but WITHOUT identity property)

> Then, INSERT #businessInfo ...

> > Am I thinking about this wrong? Can this be done with a SQL 7 table
> > variable?

> No such thing.  Table variables were introduced with SQL 2000.

> --
> AB
> www.aspfaq.com