0

I have the following query :

   ALTER procedure [dbo].[jk_insertAllLocation]
   @locationTbl as locationTable readonly,
   @TableName varchar(100)
   as
   declare @tbl as locationTable,@sql varchar(max)
   begin
        set @sql = 'insert into ' + @TableName +'(location_id,name,address,latitude,longitude,distance,state,sub_cat,id_cat,icon_link,checkinsCount,IDSearch)
        select * from ' +  @locationTbl
        exec sp_executesql @sql
   end

I need to pass a table and a table name as parameter and I need to insert in the table name (@TableName) passed as parameter all the data in the table (@locationTbl) passed as parameter

but I know that I cannot concatenate the table (@locationTbl) in the query ...

so how can I fix this?

Malachi
  • 3,205
  • 4
  • 29
  • 46
User7291
  • 1,095
  • 3
  • 29
  • 71
  • 1
    This is usually a sign of a broken data model - each of those tables must have an identical structure, which probably suggests that part of the *data* has been misused and has ended up in the table *names*. What you probably ought to have is a single table with this common structure and additional column(s) which contain the data that is currently being used to decide which table a row belongs to. I'd hope you wouldn't have `MaleEmployees` and `FemaleEmployees` tables... – Damien_The_Unbeliever Dec 02 '13 at 15:56
  • 1
    @Damien_The_Unbeliever True, but unhelpful if she doesn't have design control. – Doug_Ivison Dec 02 '13 at 20:21
  • This is not efficient or pretty, so I won't submit it as an "answer"... but it would work: first write out @locationTbl to a PERMANENT table, then put that `workTable` in your `select`. Two approaches: either (1) re-use the permanent table, with unique columns (like a primary key that changes for every row, and `initialPrimaryKey_andDateTime` that stays the same for all rows in **THIS EXECUTION** of the sproc), and then reference that unique column in your insert/select, or (2) build a unique permanent tablename for each execution, having the sproc `DROP` it, at start and end. – Doug_Ivison Dec 02 '13 at 20:37

1 Answers1

1

You can use temp tables (Temporary tables section on link):

ALTER procedure [dbo].[jk_insertAllLocation]
  @locationTbl as locationTable readonly,
  @TableName varchar(100)
as
begin
  declare @tbl as locationTable,@sql varchar(max)
  if object_id('#_tmp_location_table') is not null drop table #_tmp_location_table
  select * into #_tmp_location_table from @locationTbl
  set @sql = 'insert into ' + @TableName + '(location_id,name,address,latitude,longitude,distance,state,sub_cat,id_cat,icon_link,checkinsCount,IDSearch) select * from #_tmp_location_table'
  exec sp_executesql @sql
end
shibormot
  • 1,638
  • 2
  • 12
  • 23