Table Variable with Order BY

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  October 24, 2006 at 9:32 am

  #113981

  This query fails with the error 'An explicit value for the identity column in table  @tbl can only be specified when a column list is used and IDENTITY_INSERT is ON'

  DECLARE @OrdDate datetime

  DECLARE @tbl TABLE (ID INT IDENTITY(1,1), CompanyName varchar(40) )

  INSERT INTO @tbl

  SELECT DISTINCT CompanyName

  FROM NorthWind..Customers c

  INNER JOIN NorthWind..Orders o ON o.CustomerID = c.CustomerID

  WHERE o.OrderDate between '1996-07-01' and '1996-07-30'

  ORDER BY c.CompanyName

  When I remove the ORDER BY the query runs without any error. How do I get the above query to work ?

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2006 at 9:38 am

  #667622

  Add the column name to the INSERT:

  DECLARE @OrdDate datetime

  DECLARE @tbl TABLE (ID INT IDENTITY(1,1), CompanyName varchar(40) )

  INSERT INTO @tbl (CompanyName)

  SELECT DISTINCT CompanyName

  FROM NorthWind..Customers c

  INNER JOIN NorthWind..Orders o ON o.CustomerID = c.CustomerID

  WHERE o.OrderDate between '1996-07-01' and '1996-07-30'

  ORDER BY c.CompanyName

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  October 24, 2006 at 9:48 am

  #667627

  That was quick.

  Thanks Lynn