Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server to Sybase Create Table with IDENTITY field

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you are looking at this article, you have most likely been hit by some version of ransomware and are trying to find out if there is anything you can do, or what way you should react - READ ON!
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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