Solved

SQL Server to Sybase Create Table with IDENTITY field

Posted on 2010-11-18
5
1,517 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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
A procedure for exporting installed hotfix details of remote computers using powershell
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now