Share via

How to do conditional insert SQL Server

Sudip Bhatt 2,281 Reputation points
2020-12-12T18:14:20.983+00:00

Now i am inserting data into table A from table B. here is a sample dynamic sql which insert data into #TmpZacksCons table from #TmpAll_Broker_LI 

 SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,          
 '+@PeriodCols+'          
 )          
 Select b.Section, b.LineItem,Max(Ord)+1 Ord,          
 '+@AvgSql+'          
  From #TmpAll_Broker_LI b          
  Group By b.Section, b.LineItem'          
 EXEC(@sql)

Now my requirement is bit change. i want to select a table which has many records and each records has type. one is average and other is Median.

i want to select from TableA and check if records type is average then select data from #TmpAll_Broker_LI
if records type is Median then select records from #TmpAll_Broker_LI_Median

so in my above sql i will select and check record type if Median then select data from #TmpAll_Broker_LI_Median else if record type id Average then select data from #TmpAll_Broker_LI

so tell me how could i compose this dynamic sql where based on records type data will be fetched from different table and that data will be inserted in #TmpZacksCons dynamic table.

please guide me with sample sql. thanks

Edit

My requirement is.

for each insert into TmpZacksCons i want to check what is the value of Type from Table3. if value is Average then select data from table2 and insert into TmpZacksCons and if type is Median then select data from table3 and insert into TmpZacksCons table. please give me the code accordingly with a sample code.

Thanks

Developer technologies | Transact-SQL
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 122.6K Reputation points
    2020-12-12T19:54:05.147+00:00

    Consider this approach too: 

    insert into #TmpZacksCons ( columns )
select columns from TableA a inner join #TmpAll_Broker_LI on … and a.[type] = ‘average’
union all
select columns from TableA a inner join #TmpAll_Broker_LI_Median on … and a.[type] = ‘Median’
    1 person found this answer helpful.
  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-12-13T19:20:02.467+00:00

    SELECT (CASE TYpe WHEN 'Average' THEN SELECT col1,col2 FROM Tabel1
    WHEN 'Median' THEN SELECT col1,col2 FROM Tabel2)

    No, you can't do that. THEN must be followed by a scalar expression. That is, you cannot have multiple columns or rows. But assuming that Type is in some other table, you could do: 

    SELECT u.col1, u.col2
FROM   OtherTable ot
CROSS APPLY (SELECT t1.col1, t1.col2
             FROM   Table1 t1
             WHERE  ot.Type = 'Average'
             UNION ALL
             SELECT t2.col1, t2.col2
             FROM   Table2 t2
             WHERE  ot.Type = 'Medium') AS u
  3. MelissaMa-MSFT 24,221 Reputation points
    2020-12-14T06:01:16.677+00:00

    Hi @Sudip Bhatt ,

    Please refer below and check whether it is helpful to you. Thanks. 

      declare @sql nvarchar(max)  
  declare @PeriodCols nvarchar(max)  
  declare @AvgSql nvarchar(max)  
  
  declare @type nvarchar(max)  
  select @type= type from tableA where ...  
  
  SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,            
  '+@PeriodCols+'            
  )            
  Select b.Section, b.LineItem,Max(Ord)+1 Ord,            
  '+@AvgSql+'            
   From '+ case when @type='Average' then '#TmpAll_Broker_LI'  
   when @type='Median' then '#TmpAll_Broker_LI_Median' end + ' b            
   Group By b.Section, b.LineItem'            
   exec(@sql)

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

  4. MelissaMa-MSFT 24,221 Reputation points
    2020-12-15T03:09:19.567+00:00

    Hi @Sudip Bhatt ,

    for each insert into TmpZacksCons i want to check what is the value of Type from Table3. if value is Average then select data from table2 and insert into TmpZacksCons and if type is Median then select data from table3 and insert into TmpZacksCons table. please give me the code accordingly with a sample code.

    It could be better for you to post the DDL and sample data for all tables (TmpZacksCons ,table2 and table3) so that we could provide a better solution.

    Please refer below updated one： 

    declare @sql nvarchar(max)  
declare @PeriodCols nvarchar(max)  
declare @AvgSql nvarchar(max)  
      
declare @type nvarchar(max)  
select @type= type from table3 --where --add your condition here   
      
SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,            
'+@PeriodCols+'            
)            
Select b.Section, b.LineItem,Max(Ord)+1 Ord,            
'+@AvgSql+'            
From '+  
case when @type='Average' then 'Table2'  
when @type='Median' then 'Table3' end + ' b            
Group By b.Section, b.LineItem'            
exec(@sql)

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.