Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server to Sybase Create Table with IDENTITY field

Posted on 2010-11-18
5
Medium Priority
?
1,674 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-
[X]
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
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

With the evolution of technology, we have finally reached a point where it is possible to have home automation features like having your thermostat turn up and door lock itself when you leave, as well as a complete home security system. This is a st…
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

618 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