Error Importing Excel into SQL

[m.peck]

Im am trying to import data from excel into a sql table heres my code

SET IDENTITY_INSERT Groups ON
INSERT INTO [sdata3].[dbo].[Groups]
Select [Group_ID],[Group_Name],[Group_PID] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Groups.xls;HDR=YES',
'SELECT [Group_ID],[Group_Name],[Group_PID] FROM [Sheet1$]')

The error I get is

Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'sdata3.dbo.Groups' can only be specified when a column list is used and IDENTITY_INSERT is ON.

If I dont import the Identity column then there is no error, can someone tell me what I need to do to bring in all the data. Thanks

 

[giadich]

When SET IDENTITY_INSERT is used, you'll need to specified the column list.

Something like this:

SET IDENTITY_INSERT Groups ON
INSERT INTO [sdata3].[dbo].[Groups] ([Group_ID],[Group_Name],[Group_PID])
Select [Group_ID],[Group_Name],[Group_PID] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', .............

You get the idea...