In SQL, can I combine two or more fields into one field during the same insert statement?-CodePudding

For this scenario, I have a table like this: ID (Autoincrement, PK), PartType (VarChar), and DesignItemID (VarChar). I would like to combine the columns ID and PartType into column DesignItemID using a single INSERT statement.

Is this possible?

The purpose for this scenario spawns from trying to use an external SQL database for a part library in Altium Designer. Altium Designer needs a unique ID to maintain a proper link to the part that is placed and the DB. Ordinarily, an autoincrement PK could work, however, I need to keep the different types of parts in separate tables (such at resistors in a resistor table and capacitors in a capacitor table, etc.). So, if I have two or more different tables with an autoincrement PK ID column, I will have multiple IDs all starting at 1.

My proposed solution is to make a table with column ID using autoincrement for the PK, column PartType using a char or varchar, and column DesignItemID also using a char or varchar. Upon an INSERT command, I will enter the value RES for resistor or CAP for capacitor for column PartType and somehow LPAD ID to about 6 places and CONCAT with PartType to create DesignItemID RES000001 or CAP000001 for example. Both tables have 1 as PK ID, but, with the part type and padding, a unique column can be made for Altium Designer.

I understand that in a SQL admin interface, I could structure a query to create this unique piece of data, but Altium Designer requires this unique ID to be in a column.

I can accomplish this task in Access by using a calculate field, but Access is limited to number of concurrent users and cannot scale like an external SQL DB can.

Please note that I will have far more columns in the Database that corresponds to the part. I am only focusing on the columns that I do not know if what I am asking can be done.

CodePudding user response：

depending on your database,

it seems you are asking for a unique number that spans across multiple tables. This could be called ultimately a GUID - if it should also be unique across databases.

this could be done with a single SEQUENCE. or you can look up GUID generators.

exporting multiple tables with such a GUID would be no problem - you just query from wherever they live and send them to your output stream.

Importing on the other hand is more difficult - since you will need to know where each GUID lives (in which table). You can do this with another table that maps each GUID to the table it belongs in.

CodePudding user response：

A little bit of walking instead of just talking. Code you'll see is Oracle, but I guess other databases offer the same or similar options. Note that I don't know Altium Designer.

Question you asked was:

can I combine two or more fields into one field during the same insert statement?

Yes, you can; you already know the operator - it is concatenation. In Oracle, it is either the concat function or double pipe || operator. Here's how.

First, two sample tables (resistors and capacitors):

SQL> create table resistor
  2    (id_res     varchar2(10) constraint pk_res primary key,
  3     name       varchar2(10) not null
  4    );

Table created.

SQL> create table capacitor
  2    (id_cap     varchar2(10) constraint pk_cap primary key,
  3     name       varchar2(10) not null
  4    );

Table created.

Sequence will be used to create unique numbers:

SQL> create sequence seqalt;

Sequence created.

Database trigger which creates the primary key value by concatenating a constant (RES for resistors) and the sequence number, left-padded with zeros up to 7 characters in length (so that the full value length is 10 characters):

SQL> create or replace trigger trg_bi_res
  2    before insert on resistor
  3    for each row
  4  begin
  5    :new.id_res := 'RES' || lpad(seqalt.nextval, 7, '0');
  6  end trg_bi_res;
  7  /

Trigger created.

SQL> create or replace trigger trg_bi_cap
  2    before insert on capacitor
  3    for each row
  4  begin
  5    :new.id_cap := 'CAP' || lpad(seqalt.nextval, 7, '0');
  6  end trg_bi_cap;
  7  /

Trigger created.

Let's insert some rows:

SQL> insert into resistor (name) values ('resistor 1');

1 row created.

SQL> select * from resistor;

ID_RES     NAME
---------- ----------
RES0000001 resistor 1

Capacitors:

SQL> insert into capacitor (name) values ('capac 1');

1 row created.

SQL> insert into capacitor (name) values ('capac 2');

1 row created.

SQL> select * From capacitor;

ID_CAP     NAME
---------- ----------
CAP0000002 capac 1
CAP0000003 capac 2

My suggestion is a view instead of a new table to be used by the Altium Designer - of course, if it is possible (maybe Designer requires a table, and nothing but a table ...):

SQL> create or replace view v_altium (designitemid, name) as
  2  select id_res, name from resistor
  3  union all
  4  select id_cap, name from capacitor;

View created.

SQL> /

View created.

SQL> select * from v_altium;

DESIGNITEM NAME
---------- ----------
RES0000001 resistor 1
CAP0000002 capac 1
CAP0000003 capac 2

You'd now make the Altium Designer read the view and - from my point of view - it should work just fine.

If it has to be a table (let's call it altium), then it would look like this:

SQL> create table altium
  2    (designitemid   varchar2(10) constraint pk_alt primary key,
  3     name           varchar2(10)
  4    );

Table created.

Triggers will now be changed so that they also insert a row into the altium table (see line #7):

SQL> create or replace trigger trg_bi_res
  2    before insert on resistor
  3    for each row
  4  begin
  5    :new.id_res := 'RES' || lpad(seqalt.nextval, 7, '0');
  6    insert into altium (designitemid, name) values (:new.id_res, :new.name);
  7  end trg_bi_res;
  8  /

Trigger created.

SQL> create or replace trigger trg_bi_cap
  2    before insert on capacitor
  3    for each row
  4  begin
  5    :new.id_cap := 'CAP' || lpad(seqalt.nextval, 7, '0');
  6    insert into altium (designitemid, name) values (:new.id_cap, :new.name);
  7  end trg_bi_cap;
  8  /

Trigger created.

Let's try it:

SQL> insert into resistor (name) values ('resistor 4');

1 row created.

SQL> insert into resistor (name) values ('resistor 5');

1 row created.

SQL> insert into capacitor (name) values ('capac 5');

1 row created.

Altium table contents reflects contents of resistor and capacitor:

SQL> select * from altium;

DESIGNITEM NAME
---------- ----------
RES0000011 resistor 4
RES0000012 resistor 5
CAP0000013 capac 5

SQL>

However: why do I prefer a view over a table? Because consistency might suffer. What if you delete a row from the capacitor table? You'd have to delete appropriate row from the new altium table as well, and vice versa.

You can't create a foreign key constraint from the altium table to reference primary keys in other tables because as soon as you try to insert a row into the altium table that references resistor, it would fail as there's no such a primary key in capacitor. You can create constraints, but - that's pretty much useless:

SQL> drop table altium;

Table dropped.

SQL> create table altium
  2    (designitemid   varchar2(10) constraint pk_alt primary key,
  3     name           varchar2(10),
  4     --
  5     constraint fk_alt_res foreign key (designitemid) references resistor (id_res),
  6     constraint fk_alt_cap foreign key (designitemid) references capacitor (id_cap)
  7    );

Table created.

OK, table was successfully created, but - will it work?

SQL> insert into resistor (name) values ('resistor 7');
insert into resistor (name) values ('resistor 7')
            *
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ALT_CAP) violated - parent key not
found
ORA-06512: at "SCOTT.TRG_BI_RES", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_RES'


SQL>

Nope, it won't as such a primary key doesn't exist in the capacitor table.

It means that you'd have to maintain consistency manually, and that's always tricky.

Therefore, if possible, use a view.