(Resolved) Insert into a table with an identity column

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:

Code:

---

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.

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.

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..

Code:

---

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

---

You don't included the ID column

Code:

---

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

---

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.

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

Code:

---

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

---

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?

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

Code:

---

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

---