How to make an insert with conditions in SQL?

Asked

Viewed 2,174 times

2

I am trying to make an Insert, a column of which depends on a result

INSERT INTO TABELA (ID, (CASE 'opcao1'
                             WHEN 'opcao1' THEN "campo1"
                             WHEN 'opcao2' THEN "campo2"
                             ELSE campo3
                         END))
VALUES ('11'),'20')
sql sql-insert

  • What result? Can you explain the doubt better? Welcome to Stackoverflow, I recommend reading on tour to understand a little more about the operation of the site.

  • 1

    That sounds like a hell of a gambiarra =]

  • Hello, my suggestion is to make this logic in the code.

  • Which SQL engine?

  • Try to explain why you’re doing this, you might have other ideas.

1 answer

3


I suggest two ways of doing:

Form 1 - Condition in select

In this case your Index will always be declared with all fields, and instead of values, you will use a conditioned query.

DECLARE @opcao VARCHAR(10) = 'opcao3'

INSERT INTO TABELA (ID, CAMPO1, CAMPO2, CAMPO3)
SELECT 11, CASE @opcao WHEN 'opcao1' THEN 20 ELSE NULL END 'Campo1'
         , CASE @opcao WHEN 'opcao2' THEN 20 ELSE NULL END 'Campo2'
         , CASE @opcao WHEN 'opcao3' THEN 20 ELSE NULL END 'Campo3'

Form 2 - Running a custom query

Compatibilities
The execute is available in SQL Server 2008 onwards.
On the Oracle, we have the command execute immediate, available from 10g Release 2 (10.2).

Already in this form, you will need to mount a string with your query by mounting the Insert exactly as you need it.

DECLARE @opcao VARCHAR(10) = 'opcao3'
DECLARE @cmd VARCHAR(MAX)
SET @cmd = 'INSERT INTO TABELA (ID, '+ CASE @opcao
                                            WHEN 'opcao1' THEN 'campo1'
                                            WHEN 'opcao2' THEN 'campo2'
                                            ELSE 'campo3'
                                            END +')'+
            ' VALUES (''11'', ''20'')'
EXECUTE(@cmd)

  • this method is unique to SQL Server 2008 forward isn’t it? I think it would be good to identify this, just to complement

  • Thank you @Dudaskank for telling me this. I’ve updated my answer.

Browser other questions tagged sql sql-insert

You are not signed in. Login or sign up in order to post.