jump to navigation

Creating almost identical test tables in Oracle and Sybase March 27, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
trackback

A question as below was put in one of the forums and I quote:

“I would like to create an equivalent of the following [Oracle] test data in Sybase ASE. I did a search for generator in ASE but it does not seem to exist. This code generates 25 rows of test data in Oracle 11gR2:


CREATE TABLE dummy2
 AS
 WITH GENERATOR AS (
 SELECT            rownum            id
 FROM        dual
 CONNECT BY
 rownum <= 5
 )
 SELECT
 rownum                                          id
 , trunc((rownum - 1) / 1000)                clustered
 , mod(rownum - 1,1000)                      scattered
 , trunc(dbms_random.value(0,1000))          randomised
 , substr(dbms_random.string('A',15),1,15)   random_string
 , lpad(rownum,10)                           rownumchar
 , rpad('x',10,'x')                          xchar
 FROM
 generator   g1,
 generator   g2
 ;
 ALTER TABLE dummy2 ADD CONSTRAINT dummy2_pk PRIMARY KEY(id);

FYI, this will generate 25 rows of data which the first few rows will look like:


ID  CLUSTERED  SCATTERED RANDOMISED RANDOM_STRING   ROWNUMCHAR XCHAR
 ---------- ---------- ---------- ---------- --------------- ---------- ----------
 1          0          0        968 NrsHaXarDZNFwkh          1 xxxxxxxxxx
 2          0          1        194 tJFaWzmfEEhCmHr          2 xxxxxxxxxx
 3          0          2        159 tVRDLHAaqIAAJgz          3 xxxxxxxxxx
 4          0          3        912 XPmeibdSvaKMfSz          4 xxxxxxxxxx
 5          0          4        949 ufIHshljSFagfla          5 xxxxxxxxxx
 6          0          5        542 TUhUiVapvbPqZzY          6 xxxxxxxxxx
 7          0          6        710 nIHBNkeqVNKNNEu          7 xxxxxxxxxx
 8          0          7        297 urhNnamqGXyRgJU          8 xxxxxxxxxx
 9          0          8        123 OihsWFSphsivZVE          9 xxxxxxxxxx
 10          0          9        810 SwENZKbugGTuuyw         10 xxxxxxxxxx

I know few things. For example for rownum it may be possible to use identity in ASE. Other functions I can find out for myself. the SQL does a cartesian join using the generator.”

OK fair enough. Now my answer to the question

Sybase ASE does not have a constructor like generator. However, that is no big deal. As ever we can use a temporary table in ASE (a #table) to create a cartesian join.

So let us try this

--
--- create the temporary generator table here, one column increasing number bigint
--
create table #generator(id bigint not null)
go
--
-- And your dummy2 table here
--
create table dummy2
(
id bigint not null
, clustered1 int   -- Note clustered is a reserved word in ASE
, scattered int
, randomised int
, random_string varchar(15)
, rownumchar varchar(10)
, xhar varchar(10)
)
go
ALTER TABLE dummy2 ADD CONSTRAINT dummy2_pk PRIMARY KEY(id)
go
--
-- Now simply populate generator in a while loop
--
declare @i bigint
declare @rows bigint
set @rows = 5  -- number of rows in generator
set @i = 1
while @i <=@rows
begin
  insert into #generator values (@i)
  set @i = @i + 1
end
insert into
dummy2
select
rs.id
, floor(rs.id - 1)/1000
, abs(rs.id - 1 % 1000)
, abs(rs.random % 1000)
, password_random(15)
, right(replicate(" ",10) + convert(varchar,id), 10)
, REPLICATE('x',10)
from    ( select
@rows * ( g1.id - 1 ) + g2.id AS id    -- This will create incrementing values without the use of identity column
, CAST(NEWID() AS VARBINARY)  AS random  -- prefer this one to rand which is seed dependent etc
from
#generator g1,
#generator g2
) rs
go

It should do something the OP wanted.


id                   clustered1  scattered   randomised  random_string   rownumchar xhar
-------------------- ----------- ----------- ----------- --------------- ---------- ----------
1           0           0         991 U)[u5{2U74Pw_RT          1 xxxxxxxxxx
2           0           1         760 qVn`S8^7YVqI-;P          2 xxxxxxxxxx
3           0           2         425 iv/c`Rd&Pa4-qqF          3 xxxxxxxxxx
4           0           3         422 c.uEslwyji5K<-j          4 xxxxxxxxxx
5           0           4         589 :(Z~uFZpJ`r%3Lx          5 xxxxxxxxxx
6           0           5         153 K]cwplm*;iYjRX#          6 xxxxxxxxxx
7           0           6         755 }iPKeG|^s?%9hi\          7 xxxxxxxxxx
8           0           7          87 v/+aL,M$H<rXJTZ          8 xxxxxxxxxx
9           0           8         988 S;ZUY.ZWG)1mg%$          9 xxxxxxxxxx
10           0           9         216 ,tLD|X;(*KCB3>&         10 xxxxxxxxxx
11           0          10         581 U!zQ>;InB_b>!GA         11 xxxxxxxxxx
12           0          11         938 r8B}ewup^;ivn1*         12 xxxxxxxxxx
13           0          12         620 :RhXb8.>K_-Q{W1         13 xxxxxxxxxx
14           0          13         602 GE,a3V$(M6FU08/         14 xxxxxxxxxx
15           0          14         186 YHIlB.C$}~Gvz?]         15 xxxxxxxxxx
16           0          15         740 )t>R~N9r;4C{G[A         16 xxxxxxxxxx
17           0          16         486 E%+@3n;6hv2E%34         17 xxxxxxxxxx
18           0          17           9 c6sX&rl;{wXXmdO         18 xxxxxxxxxx
19           0          18         897 lld~Au+GwFWyA!i         19 xxxxxxxxxx
20           0          19         350 Nu@(OEPJ1!*5iY#         20 xxxxxxxxxx
21           0          20         155 )Zloy!MPX0n?%*a         21 xxxxxxxxxx
22           0          21          44 S6ouhI=]$Y?=4Kv         22 xxxxxxxxxx
23           0          22         510 w:^z@Ub*S1ykP?x         23 xxxxxxxxxx
24           0          23         613 #j{2_Fyz<*Wzsn/         24 xxxxxxxxxx
25           0          24         917 4B/-JK.Dia>yi3\         25 xxxxxxxxxx

(25 rows affected)

Obviously that random string is not exactly what was created in the Oracle code ( dbms_random.string(‘A’, 15), i.e. upper and lower case alpha characters ). password_random function in ASE generates something like dbms_random.string(‘P’, 15)  meaning ‘any printable character’.

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: