2

I have insert statements like below

INSERT INTO KeyValue (id, key, value) VALUES (101, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (102, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (103, 'color', 'green');

Is there a way to align the column names and values so that it is easy to read the values (value is beneath the column name)?

Like

INSERT INTO KeyValue (id,   key,     value) 
            VALUES   (101, 'color', 'green');
INSERT INTO KeyValue (id,   key,     value) 
            VALUES   (102, 'color', 'green');
INSERT INTO KeyValue (id,   key,     value) 
            VALUES   (103, 'color', 'green');

Is there a tool for this? Ok with any SQL, shell, python, regex trick.

Zoe
  • 27,060
  • 21
  • 118
  • 148
polapts
  • 5,493
  • 10
  • 37
  • 49
  • Any good text editor (I use Sublime Text) can do this for you. see... https://stackoverflow.com/questions/22099813/sublime-text-2-multiple-line-edit – Tolu May 21 '18 at 16:32
  • Here you go! Copy the logic into your language. https://stackoverflow.com/questions/74573661/how-where-to-format-insert-statement-by-aligning-columns-with-values/74609752#74609752 – Guilherme Almeida Girardi Nov 29 '22 at 06:10

1 Answers1

0

I prefer to use INSERT INTO SELECT:

INSERT INTO KeyValue (id, key, value)
SELECT 101 AS id, 'color' AS key, 'green' AS value;
-- FROM dual -- if Oracle

Or multiline:

INSERT INTO KeyValue(
    id
   ,key
   ,value
)
SELECT 
    101     AS id
   ,'color' AS key
   ,'green' AS value;

Remarks:

  • EAV design is common anti-pattern(it simply doesn't scale)
  • key/value could be reserved words, you should avoid such identifiers
  • value column has to be widetype (probably NVARCHAR), then you loose data type information for example DATE/TIMESTAMP/FLOAT/DECIMAL needs conversion
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275