0

In the past, I've worked with Oracle databases.
In order to populate those, I was using commands, like:

Program.Object.Create, Field1="Value1", Field2="Value2", ...

The name of that technology was called "GCC" or "TOC" (depending of the used version).

I would like to use that technology (or a similar one) for populating other databases (like SQL-Server), because currently we are using SQL for this, something like:

INSERT INTO Table1 (Field_1, Field_2, ..., Field_n) VALUES (Value_1, Value_2, ..., Value_n)

As you can see, the link between Field_i and Value_i is not that straightforward.

I have no idea about the name of that technology, I don't even if that technology was something public or written in-house.

In case this technology is something public and if it is usable for other database technologies (like SQL-server), can anybody give me a hint?

Thanks in advance

Edit: What do I mean, not straightforward?

Imagine a table with ten columns and I would like to modify some values. In SQL, this leads to SQL update queries like:

UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (8, 9, 6, 2, 4, 3, 8, 5, 3, 1);
UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (2, 6, 8, 1, 3, 8, 5, 3, 1, 5);
UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (9, 6, 5, 2, 4, 8, 6, 3, 2, 4);

In order to know which are the subfeatures in those statements, I need to count in which column the subfeatures are present. That seems to be sixth one. Then I need to look for the sixth values and I see the results (3, 8 and 8 again).

In GCC/TOC technology this is far more straightforward (after scrolling :-) ):

Program.Table1.Modify,Name="8", ID="9", Cause="6", SubCause="2", Feature="4", SubFeature="3", Call="8", SubCall="5", Type="3", SubType="1";
Program.Table1.Modify,Name="2", ID="6", Cause="8", SubCause="1", Feature="3", SubFeature="8", Call="5", SubCall="3", Type="1", SubType="5";
Program.Table1.Modify,Name="9", ID="6", Cause="5", SubCause="2", Feature="4", SubFeature="8", Call="6", SubCall="3", Type="2", SubType="4";
8
  • Your question is a little unclear, especially to someone who's not familiar with "GCC" / "TOC". Could you provide a resource that explains what those technologies are better? I don't see any functional difference between your Oracle code example and your SQL Server one, only a slight syntactical difference, and therefore I don't understand what you mean by "the link between Field_i and Value_i is not that straightforward" for your SQL Server example. It appears pretty straightforward to me.
    – J.D.
    Commented Feb 23, 2022 at 12:44
  • @J.D.: I edited my question for better understanding. As you see the higher readability of the TOC/GCC command, I'm looking for this (or similar) technology in SQL-server (the DB I'm currently working with.
    – Dominique
    Commented Feb 23, 2022 at 13:58
  • Your UPDATE statement is invalid and should look more like what you want. update table1 set Name=8, ID=9, Cause=6 ... Commented Feb 23, 2022 at 14:20
  • @MichaelKutz: thanks for the remark, I've corrected the statement.
    – Dominique
    Commented Feb 23, 2022 at 14:58
  • @Dominique - SQL is still invalid. Is your problem with INSERT or UPDATE? are you writing the code for a script or stored procedure or client (eg Java)? Commented Feb 23, 2022 at 15:03

1 Answer 1

1

The native T-SQL language also allows inserting / updating via a SELECT statement as opposed to hard-coding values. Then you can utilize aliases for better readability to emulate what you're used to in Oracle. For example:

INSERT INTO Table1 (Field_1, Field_2, Field_n)
SELECT Value_1 AS Field_1, Value_2 AS Field_2, Value_n AS Field_n;

Or my personal preference on formatting to increase readability:

INSERT INTO Table1 (Field_1, Field_2, Field_n)
SELECT 
    Value_1 AS Field_1, 
    Value_2 AS Field_2, 
    Value_n AS Field_n;

As far as updates, the "not straightforward" example you provided is not valid syntax so it wouldn't work on SQL Server anyway. The correct syntax in T-SQL is:

UPDATE Table1 
SET 
    Name = 9, 
    ID = 6, 
    Cause = 5, 
    SubCause = 2, 
    Feature = 4, 
    SubFeature = 8, 
    Call = 6, 
    SubCall = 3, 
    Type = 2, 
    SubType = 4;

This naturally is similar to what you're familiar with in Oracle.

Also, if you only wanted to update a subset of the records, such as the ones with ID = 6 already, then the following example would be how to accomplish that:

UPDATE Table1 
SET 
    Name = 9, 
    Cause = 5, 
    SubCause = 2, 
    Feature = 4, 
    SubFeature = 8, 
    Call = 6, 
    SubCall = 3, 
    Type = 2, 
    SubType = 4
WHERE ID = 6; -- Updates only the records whose ID already = 6
2
  • Thanks for your input. And what about the UPDATE statement?
    – Dominique
    Commented Feb 23, 2022 at 14:59
  • @Dominique Even easier, please see my updated answer.
    – J.D.
    Commented Feb 23, 2022 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.