CODEWITHSUNDEEP
sql-server-2008sql-insert
JiuDong
JiuDong

Reputation: 130

Insert into temp table by selecting from second table if no row inserted from first table

I have the following code, of course, my real code is long and ugly, no one wants to see it. The logic is quite simple, if table_1 has any row inserted, ignore table_2.

(long_common_column_list and long_common_where_list exists in both table_1 and table_2)

insert into tmp_table
  select id, long_common_column_list from table_1 where long_common_where_list = 1 and
  column_only_in_table_1 = @param1

if @@rowcount=0
  insert into tmp_table
    select id, long_common_column_list from table_2 where long_common_where_list = 1 and
   column_only_in_table_2 = @param2

How can I combine the two inserts and reuse the long long list? The insert script in my code is much longer with nearly 2000 characters each.

I searched but no luck. Any help or hint is appreciated.

Upvotes: 0

Views: 647

Answers (1)

dferidarov
dferidarov

Reputation: 602

You can use dynamic sql - put common Sql text in variables and then build both queries by concatane with the uncommon sql texts.

Something like this

Declare @sql nvarchar(max),@sql1 nvarchar(max),@sql2 nvarchar(max)

set @sql =  ' select id, long_common_column_list from '
SEt @sql1 = ' where long_common_where_list = 1 and '


SEt @sql2 = @sql + '  table_1 '+@sql1 +' column_only_in_table_1 = @param1' + char(13)+
        ' if @@rowcount=0 ' +  char(13) +
        @sql + ' table_2 ' +  @sql1 + ' column_only_in_table_2 = @param2'

--print @sql2
exec sp_executesql @SQl2

Upvotes: 1

Related Questions