Insert into select problem

23 Sep 2005 11:12 AM

TomislaW

example:

use northwind
DECLARE @TemporaryTable TABLE(
Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
CustomerID NCHAR(5),
OrdersNumber int
)
INSERT INTO @TemporaryTable
SELECT   distinct Customers.CustomerID,COUNT(Orders.OrderID)
FROM         Customers INNER JOIN
                        Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID
ORDER BY COUNT(Orders.OrderID) DESC

select * from @TemporaryTable

ERROR:
An explicit value for the identity column in table '@TemporaryTable' can
only be specified when a column list is used and IDENTITY_INSERT is ON.

When I add one fantom column to @TemporaryTable

DECLARE @TemporaryTable TABLE(
Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
CustomerID NCHAR(5),
OrdersNumber int,
x int
)

there is no error!
why?

23 Sep 2005 11:38 AM

Praveen

rewrite the query like this

INSERT INTO @TemporaryTable (CustomerID,OrdersNumber)
SELECT   distinct
Customers.CustomerID,COUNT(Orders.OrderID)
FROM         Customers INNER JOIN
                        Orders ON Customers.CustomerID =
Orders.CustomerID
GROUP BY Customers.CustomerID
ORDER BY COUNT(Orders.OrderID) DESC

23 Sep 2005 11:47 AM

R.D

case1: you are trying to insert customerid into identity column. so it is
asking you insert explicitly. so is the error
case2: as four coolumns are there it is inserting identity on its own other
three by you. So no problem
Regards
R.D

Show quote

"TomislaW" wrote:

> example:
>
> use northwind
> DECLARE @TemporaryTable TABLE(
>   Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
>   CustomerID NCHAR(5),
>   OrdersNumber int
>   )
> INSERT INTO @TemporaryTable
>   SELECT   distinct Customers.CustomerID,COUNT(Orders.OrderID)
>   FROM         Customers INNER JOIN
>                         Orders ON Customers.CustomerID = Orders.CustomerID
>   GROUP BY Customers.CustomerID
>   ORDER BY COUNT(Orders.OrderID) DESC
>
> select * from @TemporaryTable
>
> ERROR:
> An explicit value for the identity column in table '@TemporaryTable' can
> only be specified when a column list is used and IDENTITY_INSERT is ON.
>
> When I add one fantom column to @TemporaryTable
>
> DECLARE @TemporaryTable TABLE(
>   Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
>   CustomerID NCHAR(5),
>   OrdersNumber int,
>   x int
>   )
>
> there is no error!
> why?
>
>
>

23 Sep 2005 11:54 AM

R.D

customers.customerid is an identity? is it

Show quote

"TomislaW" wrote:

> example:
>
> use northwind
> DECLARE @TemporaryTable TABLE(
>   Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
>   CustomerID NCHAR(5),
>   OrdersNumber int
>   )
> INSERT INTO @TemporaryTable
>   SELECT   distinct Customers.CustomerID,COUNT(Orders.OrderID)
>   FROM         Customers INNER JOIN
>                         Orders ON Customers.CustomerID = Orders.CustomerID
>   GROUP BY Customers.CustomerID
>   ORDER BY COUNT(Orders.OrderID) DESC
>
> select * from @TemporaryTable
>
> ERROR:
> An explicit value for the identity column in table '@TemporaryTable' can
> only be specified when a column list is used and IDENTITY_INSERT is ON.
>
> When I add one fantom column to @TemporaryTable
>
> DECLARE @TemporaryTable TABLE(
>   Row int IDENTITY(1,1)PRIMARY KEY NOT NULL ,
>   CustomerID NCHAR(5),
>   OrdersNumber int,
>   x int
>   )
>
> there is no error!
> why?
>
>
>