SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key


IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key

Author
Message
kiran.vaichalkar
kiran.vaichalkar
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 127
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?
kiran.vaichalkar
kiran.vaichalkar
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 127
I Also tried
SET IDENTITY_INSERT ON

but the error as above. Please help on both of above.
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3119 Visits: 2766
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/
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26408 Visits: 17557
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 Modens 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)
kiran.vaichalkar
kiran.vaichalkar
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 127
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..
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26408 Visits: 17557
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 Modens 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)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40102 Visits: 38567
Now a question for kiran.vaichalkar, what is it you think that IDENTITY_INSERT does?

Cool
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)
kiran.vaichalkar
kiran.vaichalkar
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 127
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.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40102 Visits: 38567
Why do you need the user ids to be sequential? You will also get gaps if you delete users from the table as well?

Cool
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)
danaanderson
danaanderson
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 185
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search