Insert # of records based on a field in another table

English
#1

I am now attempting to populate a table (Registers) with the correct number of records for each store.

So, store 100 has a value of 10 in Reg_cnt.

I want to create 10 records in Register (100,1 - 100,2 - 100,3) up to the number based on the Reg_cnt field in Stores.

Each store has a different number of registers (well, some are the same, but it CAN be different).

I guess I could auto-populate by the highest number, then delete ones I don’t need, but that seems too manual to me.

The database is HSQLDB. (Sorry!, but that’s the only choice I had at the time!)

The other fields in Registers can be null for now, they will be updated when the registers are setup.

I’ve seen statements using INSERT INTO … FROM, and some on looping for inserts, but nothing that will do the looping (or whatever) for the number of times based on the value of a field.

It could be because I don’t know what exactly to search for. I’m sorry if this is a duplicate.

Thanks!

Meg

My tables are this:

image descriptionimage description

#2

Hello,

This may be possible with an HSQLDB procedure (see Hsqldb User Guide) or by writing specific macro to accomplish.

Based upon the request, it may be quicker to manually enter the needed records.

Edit:

Here is a Q&D sample using a portion of your tables and a macro. There are probably other checks & balances you may wish to add on but it works as is:

Sample ----- SQLmacro.odb

Macro is run from button on form.

#3

It might be if I only needed to do it once. But there could be 100 new stores every year. That would get might tedious! Thanks for link, I will go see what I can figure out.

#4

@mmeyer27,

Based upon your comment, you seem to be anticipating many records in the database - possibly many thousand. If so, seriously consider not using HSQLDB embedded. This is a very old database - v1.8 and is currently being replaced in LO Base with Firebird embedded v3.x which has been available in LO for quite some time now. HSQLDB embedded has also been known to lose all data on occasion without any apparent reason.

There are many other databases to choose from - HSQLDB split, MySQL, PostgreSQL, MariaDB and others.

Any of these and many other are a better choice than HSQLDB embedded.

#5

I’m not sure of total number of records. My intent here was to create a working db, then have the company put it into their normal platform. (so the final product will not be LO at all) I have PostGre, but my initial setup was all done in the HSQLDB of LO 6.2.8.2. I had issues getting the schema into Postgre.

I really appreciate everyone’s help here!

#6

@mmeyer27,

Based upon your last comment, this all seems to be an exercise in futility.

so the final product will not be LO at all

So what is the point in providing any samples as none of it will be used.

have the company put it into their normal platform

which you do not actually mention. You state something about PostgreSQL but this is not confirmed to be what is the final goal. As databases can be quite different, and in fact because of its’ age HSQLDB embedded is a prime candidate of that, you will be doing all of this over again to fit into the actual environment intended.

Sorry but I don’t understand. Also not certain you comprehend Base. Base is not a database. It is simply a front end to databases. It just happens LO does ship a couple of embedded DB’s with the package for those who choose to use one or the other. Fact is you can use most any DB with Base.

#7

It may seem to be an exercise in futility to you. I am the person responsible for tracking the status of new stores as they are prepared to open. I have a google sheet, that is clunky, at best, and easily screwed up. I do not have the software to do ACCESS, or anything else (really - at least not on my work laptop).

I wanted to research, and create a locally functional system, before presenting it to our corporate people, so they know there is a working model. THEN, they might spend the time/expense to create it at the corporate level. I wanted to have something solid to present to them.

I know Base is a front end. I needed SOMETHING to create all this in.

Just as an outside observation (because I’ve been reading a lot on this forum while I try to get a grip on the whole thing), you are extremely helpful, but have a really bad attitude.There are ways to say things that don’t come off as you thinking everyone with a question is either an idiot, or wasting their time.

#8

@mmeyer27,

Best to you in your endeavors.

#9

hello mmeyer27,

EDIT 2020-04-16 02:33 SORRY DID NOT SEE @Ratslinger SAMPLE.

have updated the SQL so that only stores which do not already exist in table “Registers” are inserted.

this means that the code can be safely reused & will only insert new store data.

you have not declared which version HSQLDB you use so i must assume the default HSQLDB 1.8.0.10 (embedded).
HSQLDB is not a bad database provided you use a split version with the most recent hsqldb.jar.
i have not used HSQLDB on a regular basis for some years and have/will never use FIREBIRD.

here i simply provide an answer but in respect to database normalisation can not condone your intentions.

the macro should be embedded within your database document and executed directly.

Sub InsertRegNum
	oController=thisdatabasedocument.currentcontroller
	if (not oController.isconnected()) then oController.connect()
	oCon=oController.activeconnection
	oStatement1=oCon.createStatement()
	oStatement2=oCon.createStatement()
	sSQL="select s.""StNum"", s.""Reg_cnt"" from ""Store"" s " _
	& "left join (select ""StNum"" from ""Registers"") r on r.""StNum"" = s.""StNum"" " _
	& "where r.""StNum"" is null"
	oResult=oStatement1.executequery(sSQL)
	while oResult.next()
		iStore=oResult.getint(1)
		iRegCount=oResult.getint(2)
		for i=1 to iRegCount
			sSQL="insert into ""Registers""(""StNum"",""RegNum"") values(" & iStore & "," & i & ")"
			oStatement2.executeupdate(sSQL)
		next i
	wend
End Sub

a simple query to demonstrate success.

select "StNum", "RegNum",
"StNum" || '.' || "RegNum" "RegID"
from "Registers"
#10

Thank you! I will play with this.

Why do think this is bad in respect to normalization. Normalization is why I split the registers table from the store table to begin with. The other fields in the registers table have to do with each individual register. (When each item is completed when the register gets setup - it won’t work right without each of those things being done)

#11

the most critical element of a database is the design/structure.

the table “Store” contains the field “Reg_cnt” (the number of registers in store). you are hard coding a value. what will you do if an additional register is required at some future time? your paper notes will contain this info but the database does not need to know, it can calculate this value using the table “Registers”. the field “Reg_cnt” is superfluous to requirements. a database must be able to change and adapt.

you create a number of records in the table “Registers” (one for each and every register in store) all of which have eleven fields with all meaningful fields containing NULL data, a definite no-no. what is the thinking behind programmatically creating many dozens possibly hundreds of records which contain no data? what form of normalisation is this? what would Edgar Codd have to say?

#12

three fields can be considered mandatory in the table “Registers”: “ID” auto incrementing primary key (used by the PC to identify registers, “StNum” foreign key references “Store”.“StNum”, “Name” (used by humans to identify a register which in this case is '1,2,3…etc.

two of these fields “ID” and “StNum” require no user input. “Name” requires at most two key strokes, i just cannot fathom the logic.

please do not take these comments as a criticism they are meant to be constructive.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.