Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key Expand / Collapse
Author
Message
Posted Saturday, June 01, 2013 6:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 15, 2014 2:44 AM
Points: 54, Visits: 97
Hi Folks,

Please help me on below few queries

My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)

1) now im trying SET IDENTITY_INSERT USERS.ID OFF

But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.

2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.


Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?

Post #1458941
Posted Saturday, June 01, 2013 7:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 15, 2014 2:44 AM
Points: 54, Visits: 97
I Also tried
SET IDENTITY_INSERT ON

but the error as above. Please help on both of above.
Post #1458944
Posted Saturday, June 01, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:50 PM
Points: 1,867, Visits: 2,275
Hi,

As per my concern we can not apply identity property on alphanumeric field.

to set the identity property on you have to use like this,
SET IDENTITY_INSERT table_name ON/OFF



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1458946
Posted Saturday, June 01, 2013 3:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 11,941, Visits: 10,975
kiran.vaichalkar (6/1/2013)
Hi Folks,

Please help me on below few queries

My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)

1) now im trying SET IDENTITY_INSERT USERS.ID OFF

But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.

2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.


Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?



Kapil answered the question about identity insert.

You can't have an identity that is a varchar. What are you hoping to gain here by using that type of construct? I would run away from that idea screaming. Keep it simple, go with an int (or bigint) identity and be done with it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1458971
Posted Saturday, June 01, 2013 6:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 15, 2014 2:44 AM
Points: 54, Visits: 97
Thanks Folks!!


2nd question is now addressed

an you also let me know what special we need to do to get the below executed correctly

SET IDENTITY_INSERT USERS.ID ON

Where USERS(ID INT PRIMARY KEY, Name nvarchar(20))

I tried but it throws error saying, 'Either the object 'USERS' does not exist or you do not have permissions'

Please Help..
Post #1458977
Posted Sunday, June 02, 2013 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 11,941, Visits: 10,975
kiran.vaichalkar (6/1/2013)
Thanks Folks!!


2nd question is now addressed

an you also let me know what special we need to do to get the below executed correctly

SET IDENTITY_INSERT USERS.ID ON

Where USERS(ID INT PRIMARY KEY, Name nvarchar(20))

I tried but it throws error saying, 'Either the object 'USERS' does not exist or you do not have permissions'

Please Help..


You don't set identity insert on the column, just the table.

SET IDENTITY_INSERT USERS ON

Then when you are done you need to set it back off.

SET IDENTITY_INSERT USERS OFF



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1458996
Posted Sunday, June 02, 2013 10:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 22,475, Visits: 30,148
Now a question for kiran.vaichalkar, what is it you think that IDENTITY_INSERT does?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1459014
Posted Sunday, June 02, 2013 3:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 15, 2014 2:44 AM
Points: 54, Visits: 97
Hi Lynn,
i was mistaken about including the Column name in the syntax for IDENTITY_INSERT.

Well i was looking continuity for one of my table column entries which is the primary key (Column - USERS.ID) was also set to IDENTITY(1,1).
The continuity on USERS.ID was missed due to series of transactions on that table.(INSERT / DELETE / UPDATE)

Now I wanted manually to enter the missing records.

Help me if am getting IDENTITY_INSERT wrong.

Post #1459037
Posted Sunday, June 02, 2013 3:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 22,475, Visits: 30,148
Why do you need the user ids to be sequential? You will also get gaps if you delete users from the table as well?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1459039
Posted Sunday, June 02, 2013 11:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:26 PM
Points: 42, Visits: 94
kiran.vaichalkar (6/1/2013)
Hi Folks,

Please help me on below few queries

My table is
Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References ID)

1) now im trying SET IDENTITY_INSERT USERS.ID OFF

But error message i received is 'Cannot find the object 'Users' because it does not exists or you do not have permissions'.

2) In case if want my PRIMARY KEY to be alphanumeric as well as autoincrementing. For example the existing values alike 'E1, E2, E3 ans so on.


Why I am getting that error? and How to eradicate it?
&
How to Set our Primary KEY to Alphanumeric AutoIncrementing?



I wonder if the table exists. The reason I say this, is that the syntax above is incorrect/incomplete. Maybe I'm wrong on this. But will it create the table if the type of the first column is not identified? You have:
ID IDENTITY(1,1) PRIMARY KEY,

I would write it like this:
ID INT IDENTITY(1,1) PRIMARY KEY,

I have never tried to do this without giving the INT type, so I don't really know if SQL Server will assume INT and is forgiving about this. But considering the error message, I am leaning to thinking no.

Dana


Post #1459082
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse