sql IDENTITY_INSERT

[Papa_Don]

Group,

I'm trying to write the commands to update a row within a SQL table. I've written the following:

USE DATADESIGNSOLUTIONS
SET IDENTITY_INSERT LocationID ON
INSERT INTO LOCATIONS_SETUP(LocationID, LocationNumber, LocationName, LocationAddress1, LocationAddress2, LocationAddress3, LocationCity, LocationState, LocationZipCode, LocationZipCodeExt, PhoneNumber, FaxNumber, BillToAnswer, BillToLocation)
VALUES(10, 1, 'Traditions in Tile / Buford RDC', '4325 Hamilton Mill Rd', '', '', 'Buford', 'GA', '30518', '', '[phone]', '[phone]', 'N', 36)
GO

I'm getting the following error:

"Cannot find the object "LocationID" because it does not exist or you do not have permissions."

I've confirmed the LocationID exists:

LocationID  LocationNumber  LocationName                                LocationAddress1        LocationAddress2    LocationAddress3    LocationCity    LocationState   LocationZipCode LocationZipCodeExt  PhoneNumber         FaxNumber           BillToAnswer    BillToLocation
1           7               Traditions in Tile / Alpharetta Showroom    3065 Trotters Parkway                                           Alpharetta      GA              30004                               [phone]      [phone]      N               36
2           36              Traditions in Tile / Corporate              3065 Trotters Parkway                                           Alpharetta      GA              30004                               [phone]      [phone]      Y               36
10          1               Traditions in Tile / Buford RDC             4325 Hamilton Mill Rd                                           Buford          GA              30518                               [phone]      [phone]      N               36

Am I writing this correctly to allow the edit? If I need "permissions", how is it that I get these?

In advance, thanks for your help.

Don

[pritaeas]

You need ALTER permissions for IDENTITY_INSERT.

[Papa_Don]

Pritaeas,

I was hoping your suggestions was litteral. However "ALTER permissions for IDENTITY_INSERT" doesn't work. SQL Manager didn't like "permissions". I hate to ask, but what is the correct syntax for writing this? I've not been able to find it.

Thanks for your help.

Don

[lolafuertes]

The SQL SET IDENTITY_INSERT must refer to a table, but in your code it refers to a column in the table. (see here)

In your example, the sentence would be:

SET IDENTITY_INSERT LOCATIONS_SETUP ON

Just to comment that, in general, is not a good idea to insert specific values in an identity column.
If you want to do that, first you need to verify that the value to insert in the table does not already exists.

Assuming that the identity colum in your example is LoacationId, you should rewrite your sentence like:

IF NOT EXISTS (SELECT LocationId FROM LOCATIONS_SETUP WHERE LocationId=10) INSERT INTO LOCATIONS_SETUP(LocationID, LocationNumber, LocationName, LocationAddress1, LocationAddress2, LocationAddress3, LocationCity, LocationState, LocationZipCode, LocationZipCodeExt, PhoneNumber, FaxNumber, BillToAnswer, BillToLocation)
VALUES(10, 1, 'Traditions in Tile / Buford RDC', '4325 Hamilton Mill Rd', '', '', 'Buford', 'GA', '30518', '', '[phone]', '[phone]', 'N', 36)
ELSE ....

On the else clause you must take an action when the value to be inserted already exists, like raising a SQL error.
Once you have inserted the value you must revert the permission to insert identity values using

SET IDENTITY_INSERT LOCATIONS_SETUP OFF

Hope this helps

[lolafuertes]

And yes, as said by @priteas, you will need the permissions to alter the database to use the sentence

SET IDENTITY_INSERT ....

[Papa_Don]

lolafuertes,

I have no desire to make any changes to the ID field itself. What I want to do is to change the values within that specific ID row. In this specific case I want to edit the fields

LocationNumber, LocationName, LocationAddress1, LocationAddress2, LocationAddress3, LocationCity, LocationState, LocationZipCode, LocationZipCodeExt, PhoneNumber, FaxNumber, BillToAnswer, BillToLocation

Am I asking the wrong question here? Since I want to edit one specific row of the table, is there a "right" way of doing this?

Thanks for you help!

Don

[Papa_Don]

Group,

I found the correct code syntax:

SET TermsPcnt = @tPcnt, TermsDescription = @tDescription, TermsDays = @tDays, TermsProx = @tProx, TermsBillType = @tBillType
WHERE TermsCodeID = @svID
            cmd.Parameters.AddWithValue("@tID", trmID)
            cmd.Parameters.AddWithValue("@tPcnt", termsPcnt)
            cmd.Parameters.AddWithValue("@tDescription", termsDesc)
            cmd.Parameters.AddWithValue("@tDays", tDays)
            cmd.Parameters.AddWithValue("@tProx", sProx)
            cmd.Parameters.AddWithValue("@tBillType", billType)

This is written in Visual Basic.

Hopefully others will find benefit from this.

Thanks, group!

Don