Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 7 of 7 total |
Create 5000 records |
Fri, Feb 27 2009 1:09 PM | Permanent 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I see you spotted the deliberate mistake - 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 PM | Permanent 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 > |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |