?
Solved

SQL Server to Sybase Create Table with IDENTITY field

Posted on 2010-11-18
5
Medium Priority
?
1,705 Views
Last Modified: 2012-05-10
Hi,
  I'm porting create table scripts from SQL Server to Sybase 15.5
what would be the equivalent of this identity field in Sybase?

CREATE TABLE [dbo].[test]
(
    TestID INT NOT NULL IDENTITY (1001, 1),
    TestName NVARCHAR(50) NOT NULL
)
GO

I'm having a problem getting the syntax for the Identity Start = 1001
Thanks
0
Comment
Question by:carl-
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 34168084
So you want an identity column with a starting value of 1001? What size do you want the column to be able to hold? In previous versions identities were strictly numerics but you have the option in this version of having it as an integer.

If I've described your requirement correctly, in Sybase ASE you can't do this in one statement. You'd create the table with the appropriately sized identity, and then separately seed its initial value.
0
 
LVL 4

Author Comment

by:carl-
ID: 34190987
do you know the command to separately seed its initial value?
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 2000 total points
ID: 34192381
I think you need to do it during the first insert or by updating the first row inserted. For the first option set identity insert on for that table by using below syntax.

set identity_insert [database.[owner.]]table_name {on | off}

Then insert the first row with the desired initial identity value. And then turn identity insert  off.

The second option is doing it after inserting the first row. You can update the identity value after setting identity_update on for that table:

set identity_update table_name {on | off}

More details from command reference:

identity_insert
determines whether explicit inserts into a table’s IDENTITY column are allowed. (Updates to an IDENTITY column are never allowed.) This option can be used only with base tables. It cannot be used with views or set within a trigger.

Setting identity_insert table_name on allows the table owner, Database Owner, or System Administrator to explicitly insert a value into an IDENTITY column. Inserting a value into the IDENTITY column allows you to specify a seed value for the column or to restore a row that was deleted in error. Unless you have created a unique index on the IDENTITY column, Adaptive Server does not verify the uniqueness of the inserted value; you can insert any positive integer.

The table owner, Database Owner, or System Administrator can use the set identity_insert table_name on command on a table with an IDENTITY column to enable the manual insertion of a value into an IDENTITY column. However, only the following users can actually insert a value into an IDENTITY column, when identity_insert is on:

Table owner

Database Owner:

if granted explicit insert permission on the column by the table owner

impersonating the table owner by using the setuser command

Setting identity_insert table_name off restores the default behavior by prohibiting explicit inserts to IDENTITY columns. At any time, you can use set identity_insert table_name on for a single database table within a session.

identity_update
With set identity_update on, you can explicitly update the value of the IDENTITY column on a table. identity_update changes the identity column value for the qualified rows. When identity_update is enabled, you can update the identity value to any value greater than 0. However, if the input value is greater than the identity burn max value, a new set of ID values is allocated, and the identity burn max value on the OAM page is updated accordingly. If update is included in a transaction, the new identity burn max value cannot be rolled back. You can use syb_identity to point to the identity column for update. For example:

update table_name set syb_identity = value
where clause

Adaptive Server does not check for duplicates entries or verify that entries are unique. You can update an existing value to any positive integer within the range allowed by the column's declared precision. You can check for duplicate entries by creating a unique index on the identity column

0
 
LVL 4

Author Closing Comment

by:carl-
ID: 34192534
Thanks!!!
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 34271586
As a note, you can do it by sp_chgattribute as well:

sp_chgattribute [table_name], "identity_burn_max", 0, "[new_value]"

Check that discussion for further info:

http://www.experts-exchange.com/Database/Sybase/Q_23288053.html
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
If anyone asked you to network diagram of the internet, it was drawn in the form of a fluffy cloud which further became known as cloud computing. Popularly cloud computing is defined as workloads that run over the internet in a commercial provider’s…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question