How to do an Insert with conditions in SQL?

Question

How to do an Insert with conditions in SQL?

Navigation

#1 by (3 votes)

2

I'm trying to make an insert, where a column 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

asked by anonymous 20.10.2017 / 20:19

1 answer

.click function does not work on buttons that were generated dynamically by the .html function? [duplicate] Get X and Y from the screen using PYTHON

score 3 · Accepted Answer

I suggest two ways to do this:

Form 1 - Condition no select

In this case your insert will always be declared with all fields, and instead of values, you will use a conditional 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
execute is available in SQL Server 2008 onwards. In Oracle, we have the command execute immediate , available from 10g Release 2 (10.2).

Already in this form, you will need to assemble 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)