In ASE you can combine a select into statement with a union operator and an identity column generated with the identity function. In MS SQL Server this is not allowed.

• Syntax of the identity function in ASE:

-- This creates an auto increment column with data type numeric(x,0)

Identity( precision )

• Syntax of the identity function in MS SQL Server:

Identity( data type [, seed, increment] )

-- for instance Identity( int, 1, 1 )

Examples

-- This is allowed in both systems

-- ASE

-- select distinct ID = Identity( 6 )

-- , DescType = Cast( 'Action' as char(10) )

-- , Description

-- MS SQL Server

-- select distinct ID =Identity( int, 1, 1 )

-- , DescType = Cast( 'Action' as char(10) )

-- , Description

into #Tmp

from T_ActionGrp

-- The following only works in ASE (note the ‘union’)

-- ASE

-- select ID = Identity( 6 )

-- , DescType = Cast( 'Action' as char(10) )

-- , Description

-- MS SQL Server

-- select ID = Identity( int, 1, 1 )

-- , DescType = Cast( 'Action' as char(10) )

-- , Description

into #Tmp

from T_ActionGrp

union

-- ASE

-- select ID=Identity( 6 )

-- , DescType = Cast( 'User' as char(10) )

-- , Description

-- MS SQL Server

-- select ID = Identity( int, 1, 1 )

-- , DescType = Cast( 'User' as char(10) )

-- , Description

from T_UserGrp

Note: On MS SQL Server, you can create a temporary table beforehand, instead of creating it with ‘Select Into’. Another option is creating an empty temporary table with ‘Select Into’ by adding the ‘Where’ clause ‘Where 1 = 0’. After that, use an ‘Insert Into’ instead of a ‘Select Into’ statement to populate the table.