How to do dynamic insert with SQL

Question

Problem: I have two tables and I want to retrieve one value from Estatus table like:

var @status = SELECT ID FROM [adm].[Estatus] WHERE Nombre = 'ACTIVA'

and then insert it into another table like:

INSERT INTO [adm].[Users] 
VALUES ('pepe@gmail.com', + @status + 'new')

but I get error into var @status

Incorrect syntax near '@status'

I try to use DECLARE instead var but it doesn't work too

What do I need to get value and the use it in my insert query? Regards

https://stackoverflow.com/questions/548090/dynamic-sql-execsql-versus-exec-sp-executesqlsql — RoMEoMusTDiE, May 02 '17 at 22:16

score 0 · Answer 1 · answered May 03 '17 at 00:28

0

Assuming you are using SQL Server and column ID is of type int:

DECLARE @status INT

SELECT  TOP 1
        @status = ID
FROM    adm.Estatus
WHERE   Nombre = 'ACTIVA'

INSERT  INTO adm.[Users]
VALUES  ( 'pepe@gmail.com', CONCAT(@status, 'new') )

answered May 03 '17 at 00:28

JanR

101
3

score 0 · Answer 2 · answered May 03 '17 at 06:29

0

Try this:

INSERT INTO adm.[Users] (EmailColumn, StatusColumn)
SELECT 'pepe@gmail.com', CONVERT(VARCHAR, Id) + 'new'
FROM adm.[EStatus]
WHERE Nombre = 'ACTIVA'

answered May 03 '17 at 06:29

CoridRocket

71
1
4

How to do dynamic insert with SQL

2 Answers2