identity columns

Hi,
How can i insert from a select statement where in the select part has 2
identity columns.
When i insert it in a table which i create on the fly, the insert fails.Jason
You can have only one column defined as IDENTITY in the table.
create test(id int identity)
insert test default values
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucD0Uf1PFHA.4004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can i insert from a select statement where in the select part has 2
> identity columns.
> When i insert it in a table which i create on the fly, the insert fails.
>|||Jason,
Are you using "SELECT ... INTO ..."?. If so, then you have to create the
table first (a table can have just one identity column) and use "INSERT INTO
... SELECT ..." instead.
AMB
"Jason" wrote:

> Hi,
> How can i insert from a select statement where in the select part has 2
> identity columns.
> When i insert it in a table which i create on the fly, the insert fails.
>
>|||If you're talking about a SELECT INTO that both queries an IDENTITY column
and tries to create one with the IDENTITY() function, all you need to do to
get read of the source IDENTITY propert is to perform some manipulation on
the base column, e.g., col1 + 1 AS col1:
USE Northwind
SELECT IDENTITY(INT, 1, 1) AS surkey, OrderID + 0 AS OrderID,
CustomerID, EmployeeID, OrderDate
INTO #T
FROM Orders
BG, SQL Server MVP
www.SolidQualityLearning.com
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ucD0Uf1PFHA.4004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can i insert from a select statement where in the select part has 2
> identity columns.
> When i insert it in a table which i create on the fly, the insert fails.
>|||You can't have two IDENTITY columns in a table. Why do you want to?
I expect you are using SELECT INTO. Instead, I suggest you create the table
and then INSERT into it - that way you have more control over the datatypes,
constraints, etc in the new table.
If you want to use SELECT INTO with a column that you no longer want to be
an IDENTITY then try the following (id_col is the IDENTITY column in this
example):
SELECT id_col + 0, col1, col2, ...
INTO NewTable
FROM YourTable
David Portas
SQL Server MVP
--