0

i am trying to send a datatable into sql server 2008 table by a stored procedure.

here is my sql stored proc.

create type dbo.orderUDT AS Table
(   
    dealerid int,
    dealername varchar(50),
    productname varchar(50),
    packetqua int,
    productprice varchar(50),
    orderdatetime datetime2
)
GO

ALTER proc [dbo].[send_order]
(
    @datatable AS dbo.orderUDT READONLY
)
AS
Insert into Gopal_Namkeen.dbo.OrderTable 
    Select *,SYSDATETIME() from @datatable

i am getting the following error =

An explicit value for the identity column in table 'Gopal_Namkeen.dbo.OrderTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

also when i hover on the "@datatable AS dbo.orderUDT READONLY"..it shows that @datatable has an invalid datatype.

Please guide me where i am wrong. also note that i want to use stored procedure only. Thank you.

Andrea
  • 11,801
  • 17
  • 65
  • 72
user3160395
  • 15
  • 4
  • 3
    first never ever do an insert without specifying the columns in both the insert statement and the select. Never use select * in anything that is going to production. Thesse are all bad practices. When you do this you will realize that you are trying to insert into an autogenerated field and that will not work. – HLGEM Jan 16 '14 at 13:58
  • 1
    Take a look into this post [An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server](http://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a) – Maryam Arshi Jan 16 '14 at 13:59
  • any suggestions about the errors i am getting? – user3160395 Jan 16 '14 at 14:15
  • also tell me if my declaration of variable "@datatable" is correct or not? – user3160395 Jan 16 '14 at 14:24

0 Answers0