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

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'.

CodePudding user response：

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;

CodePudding user response：

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?)