(Resolved) Insert into a table with an identity column

[FastEddie]

I am trying to insert some data into a SQL server that uses a uniqueidentifier for the invId field. My SQL statement doesn't include that field so I assumed that the field would just increment automaticaly as it does in Access. However I get an error that I can not insert Nulls into my InvId field. I did find something on the net about IDENTITY_INSERT but I can't seem to get it to work.

Here is my SQL:

INSERT INTO Reports.dbo.tblMeInventory( 
	invId IDENTITY,
	CategoryCode, 
	ItemCode, 
	UnitCode, 
	ItemCount, 
	UserName, 
	DateStamp )
SELECT
	StoreItems.CategoryCode, 
	StoreItems.ItemCode, 
	StoreItems.UnitCode, 
	0 AS ItemCount, 
	'' AS UserName, 
	'' AS DateStamp
FROM 
	lubeShop.dbo.StoreItems
WHERE(
	((StoreItems.StoreID)<>999) AND 
	((StoreItems.IsDeleted)=0))
ORDER BY 
	StoreItems.StoreID, 
	StoreItems.CategoryCode, 
	StoreItems.ItemCode

Oops I meant to post this in the database forum. It is a VB related question but I think it should have probably been posted there instead of here.

[GaryMazzone]

If the invID field is set to an Identity then you do not include that field in the insert. It should auto increment to the next value on insert. If the database field is set to int but not also to Indetity then you need to set the value youself some how (maybe coming from another table), I don't think that is what you need.

[FastEddie]

Ok I am closer but I am now getting this error (Operand type clash: numeric is incompatible with uniqueidentifier) so I think I need to set it as an integer and to autoincrement but I am not sure how..

INSERT INTO Reports.dbo.tblMeInventory( 
	invId,
	CategoryCode, 
	ItemCode, 
	UnitCode, 
	ItemCount, 
	UserName, 
	DateStamp )
SELECT
	@@Identity AS integer,
	StoreItems.CategoryCode, 
	StoreItems.ItemCode, 
	StoreItems.UnitCode, 
	0 AS ItemCount, 
	'' AS UserName, 
	'' AS DateStamp
FROM 
	lubeShop.dbo.StoreItems
WHERE(
	((StoreItems.StoreID)<>999) AND 
	((StoreItems.IsDeleted)=0))
ORDER BY 
	StoreItems.StoreID, 
	StoreItems.CategoryCode, 
	StoreItems.ItemCode

[GaryMazzone]

You don't included the ID column

INSERT INTO Reports.dbo.tblMeInventory( 
	CategoryCode, 
	ItemCode, 
	UnitCode, 
	ItemCount, 
	UserName, 
	DateStamp )
SELECT
	StoreItems.CategoryCode, 
	StoreItems.ItemCode, 
	StoreItems.UnitCode, 
	0 AS ItemCount, 
	'' AS UserName, 
	'' AS DateStamp
FROM 
	elube.dbo.StoreItems
WHERE(
	((StoreItems.StoreID)<>999) AND 
	((StoreItems.IsDeleted)=0))
ORDER BY 
	StoreItems.StoreID, 
	StoreItems.CategoryCode, 
	StoreItems.ItemCode

[FastEddie]

If I drop the fields I get this error: Cannot insert the value NULL into column 'invID', table 'Reports.dbo.tblMeInventory'; column does not allow nulls. INSERT fails.

[GaryMazzone]

What is the table definition look like? The example I gave last will work if the table is setup to have invID as an Identity column.

This creates a table with the PK set as an Identity column

Create Table ActivitySchedDates (
	ActivitySchedDatesPK	int IDENTITY,
	ActivitySchedNamesPK	int NOT NULL,
	DueDate					datetime NOT NULL
)
go

[FastEddie]

I created my table with Enterprise manager and for some reason the Identity Specification field is grayed out. I think if I could modify that and set its properties it would work.

How can I set the IsIdentity to true after the table has been created?

[FastEddie]

I got it. I needed to add a NewID() to my Select. Thanks.

 INSERT INTO Reports.dbo.tblMeInventory( 
	invId,
	StoreID,
	CategoryCode, 
	ItemCode, 
	UnitCode, 
	ItemCount, 
	UserName, 
	DateStamp )
SELECT	
	NEWID(),
	StoreItems.StoreID,
	StoreItems.CategoryCode, 
	StoreItems.ItemCode, 
	StoreItems.UnitCode, 
	0 AS ItemCount, 
	'' AS UserName, 
	'' AS DateStamp