Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Create 5000 records
Fri, Feb 27 2009 1:09 PMPermanent Link

Peter van Mierlo
Hi,

I need to create 5000 records in once based on the following syntax:
- ordernummer (6 numbers), will be entered or must be givin with the query as parameter
- day (28 febr. will be 28), 2 digit
- month (febr will be 02), 2 digit
- year (year wll be 09), 2 digit
- hour (2 digits)
- minute (2 digits)
- seconds (2 digits)
- milliseconds, 2 digits)
- random number (6 digits)

So a ticketnumber can be looked like : 000001 280209 19093030 [random number]
The milliseconds and the random number makes it unique.

Has anyone a suggestion how to do this with a sql statement or a other solutions ?

Regards Peter
Fri, Feb 27 2009 2:18 PMPermanent Link

David
On 2/27/09 1:09 PM, in article
AA28E746-74FC-4D1D-AD8D-6F8593541BDA@news.elevatesoft.com, "Peter van
Mierlo" <p.mierlo@planet.nl> wrote:

> Hi,
>
> I need to create 5000 records in once based on the following syntax:
> - ordernummer (6 numbers), will be entered or must be givin with the query as
> parameter
> - day (28 febr. will be 28), 2 digit
> - month (febr will be 02), 2 digit
> - year (year wll be 09), 2 digit
> - hour (2 digits)
> - minute (2 digits)
> - seconds (2 digits)
> - milliseconds, 2 digits)
> - random number (6 digits)
>
> So a ticketnumber can be looked like : 000001 280209 19093030 [random number]
> The milliseconds and the random number makes it unique.
>
> Has anyone a suggestion how to do this with a sql statement or a other
> solutions ?
>
> Regards Peter
>

For to do loop?
Sat, Feb 28 2009 3:48 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Peter


You'll need a script. Something like

SCRIPT (IN sON INTEGER, sDay INTEGER, IN sMonth INTEGER, IN sYear INTEGER, IN sHour INTEGER, IN sMinute INTEGER, IN sMS INTEGER)
BEGIN

DECLARE Stub VARCHAR;
DECLARE Key VARCHAR;

DECLARE Counter INTEGER;

SET Stub = CAST(sON AS VARCHAR)+' '+CAST(sYear AS VARCHAR).......

SET Counter = 1;

WHILE Counter < 5000 DO

SET Key = Stub + CAST(RANDOM(10000000) AS VARCHAR);

EXECUTE IMMEDIATE 'INSERT INTO table VALUES(....) ' +KEY;

SET Counter = Counter + 1;

END WHILE;

END;

Totally untested of course but it should give you the right idea- you might also want to use VARCHARs as the inputs so you can make sure sYear (for example) is formatted correctly. It would also be faster if you define a cursor and use that for your inserts.

Roy Lambert [Team Elevate]
Sat, Feb 28 2009 3:06 PMPermanent Link

Peter van Mierlo
Hi Roy,

Thanks, is great, i'm going to tryin it the weekend.

Roy Lambert wrote:

Peter


You'll need a script. Something like

SCRIPT (IN sON INTEGER, sDay INTEGER, IN sMonth INTEGER, IN sYear INTEGER, IN sHour INTEGER, IN sMinute INTEGER, IN sMS INTEGER)
BEGIN

DECLARE Stub VARCHAR;
DECLARE Key VARCHAR;

DECLARE Counter INTEGER;

SET Stub = CAST(sON AS VARCHAR)+' '+CAST(sYear AS VARCHAR).......

SET Counter = 1;

WHILE Counter < 5000 DO

SET Key = Stub + CAST(RANDOM(10000000) AS VARCHAR);

EXECUTE IMMEDIATE 'INSERT INTO table VALUES(....) ' +KEY;

SET Counter = Counter + 1;

END WHILE;

END;

Totally untested of course but it should give you the right idea- you might also want to use VARCHARs as the inputs so you can make sure sYear (for example) is formatted correctly. It would also be
faster if you define a cursor and use that for your inserts.

Roy Lambert [Team Elevate]
Mon, Mar 2 2009 7:09 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< You'll need a script. Something like >>

That's a really nice script, but I think Peter is using DBISAM, not
ElevateDB.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Mar 2 2009 7:28 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


I see you spotted the deliberate mistake Smiley- or to put it another way - how on earth did I miss that.

My apologies Peter the script I gave you will not work, and the only way of doing it in DBISAM that I can think of is to write a custom function and unless you need it to be callable from SQL you'd be better off using straight Delphi.

Roy Lambert [Team Egg on face]
Tue, Mar 3 2009 5:01 PMPermanent Link

"G Patena"
Peter,

Here's a possible "untested solution" but it needs a prerequisite,
assume you have access to
   "AnotherTable"
with >5000 records and with
   "SomeUniqueIncrementingField",
then you can use that as a take off for the following SQL:

INSERT INTO "YourTicketTable"  (TicketNumber)
select
 right('000000'+cast(SomeUniqueIncrementingField as char(12)), 6)
 +' '
 +right('00'+cast(extract(day from Current_TimeStamp) as char(10)), 2)
 +right('00'+cast(extract(month from Current_TimeStamp) as char(2)), 2)
 +right(cast(extract(year from Current_TimeStamp) as char(4)), 2)
 +' '
 +right('00'+cast(extract(hour from Current_TimeStamp) as char(2)), 2)
 +right('00'+cast(extract(minute from Current_TimeStamp) as char(2)), 2)
 +right('00'+cast(extract(second from Current_TimeStamp) as char(2)), 2)
 +right('00'+cast(extract(msecond from Current_TimeStamp) as char(3)), 2)
/* you can also use 3 digits */
 +' '
 +right('000000'+cast(rand(1000000) as char(6)), 6)
as TicketNumber
FROM "AnotherTable" top 5000

George Patena

"Peter van Mierlo" <p.mierlo@planet.nl> wrote in message
news:AA28E746-74FC-4D1D-AD8D-6F8593541BDA@news.elevatesoft.com...
> Hi,
>
> I need to create 5000 records in once based on the following syntax:
> - ordernummer (6 numbers), will be entered or must be givin with the query
> as parameter
> - day (28 febr. will be 28), 2 digit
> - month (febr will be 02), 2 digit
> - year (year wll be 09), 2 digit
> - hour (2 digits)
> - minute (2 digits)
> - seconds (2 digits)
> - milliseconds, 2 digits)
> - random number (6 digits)
>
> So a ticketnumber can be looked like : 000001 280209 19093030 [random
> number]
> The milliseconds and the random number makes it unique.
>
> Has anyone a suggestion how to do this with a sql statement or a other
> solutions ?
>
> Regards Peter
>

Image