How to insert into one table values from a variable and other table

Question

I have written the following query to insert values from one table to other along with some variables.

But the syntax seems to be incorrect as i am unable to get the result:

INSERT INTO [tran].[ohlc_price]
                   ([symbol_id]
                   ,[timestamp]  
                   ,[datetime]
                   ,[open_price]
                   ,[high_price]
                   ,[low_price]
                   ,[close_price]
                   ,[digits]
                   ,[type]
                   ,[created_date]
                   ,[created_by])
        values ( 
                @get_symbol_id, select 
                #temp_ohlc.timestamp,
                #temp_ohlc.datetime,
                #temp_ohlc.open_price,
                #temp_ohlc.close_price,
                #temp_ohlc.high_price,
                #temp_ohlc.low_price,
                #temp_ohlc.digits,
                #temp_ohlc.type from #temp_ohlc,
                @get_date,
                user_name()
                )

What is wrong in syntax. Please help. I am getting error:

Msg 156, Level 15, State 1, Procedure ohlc_price_save, Line 119 [Batch Start Line 9] Incorrect syntax near the keyword 'select'.

you should, when asking these kind of questions, ALWAYS add the complete error message. — Luuk, Feb 01 '22 at 11:47
And, before asking, please read the complete manual page of, when you use MySQL, [INSERT](https://dev.mysql.com/doc/refman/8.0/en/insert.html), or when you use MSSQL, see [INSERT](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15). BTW: What database are you using ? — Luuk, Feb 01 '22 at 11:48

score 0 · Answer 1 · answered Feb 01 '22 at 11:54

You wouldn't use values and select but just a select:

INSERT INTO [tran].[ohlc_price]
                   ([symbol_id]
                   ,[timestamp]  
                   ,[datetime]
                   ,[open_price]
                   ,[high_price]
                   ,[low_price]
                   ,[close_price]
                   ,[digits]
                   ,[type]
                   ,[created_date]
                   ,[created_by])
        select 
                @get_symbol_id,  
                #temp_ohlc.timestamp,
                #temp_ohlc.datetime,
                #temp_ohlc.open_price,
                #temp_ohlc.close_price,
                #temp_ohlc.high_price,
                #temp_ohlc.low_price,
                #temp_ohlc.digits,
                #temp_ohlc.type,
                @get_date,
                user_name()
                from #temp_ohlc;

As you can see you can have your constant value variables (@get_date, @get_symbol_id) and scalar functions (user_name()) as if they were fields of #temp_ohlc temporary table. They would be generated as values per insert row.

(Please tag your database, it is SQL server, isn't it?)

score 0 · Accepted Answer · answered Feb 01 '22 at 11:55

The syntax for INSERT does not allow what you are doing, so try:

INSERT INTO [tran].[ohlc_price](
    [symbol_id]
   ,[timestamp]  
   ,[datetime]
   ,[open_price]
   ,[high_price]
   ,[low_price]
   ,[close_price]
   ,[digits]
   ,[type]
   ,[created_date]
   ,[created_by])
        
select 
   @get_symbol_id,
   #temp_ohlc.timestamp,
   #temp_ohlc.datetime,
   #temp_ohlc.open_price,
   #temp_ohlc.close_price,
   #temp_ohlc.high_price,
   #temp_ohlc.low_price,
   #temp_ohlc.digits,
   #temp_ohlc.type,
   @get_date,
   user_name()
FROM #temp_ohlc;

How to insert into one table values from a variable and other table

2 Answers2