I find myself having to move specific portions of data from a final staging table to a live table on another server pretty often. It's rather tedious writing out the inserts, so I wrote this to do it for me.
Since the columns are the same, and I just have to move new data, I only have to select the column list from the table I'm moving to. I can also specify a query to go along with it. One thing I always have to do is make sure statusflag = 0; that's for valid records. Hope this helps someone.
I did see some other SPs that do similar things, but I wanted to try it without a cursor to build the column list.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
alter procedure [dbo].[sampleinsert]
--EXECUTE sampleinsert 'from table', 'to table', 'where reps = 1'
@query varchar(512) = ''
declare @liststr varchar(max)
declare @sql varchar(max)
declare @moveto varchar(max)
select @liststr = coalesce(@liststr+',' ,'') + column_name
from information_schema.columns where table_name = '' + @tablefrom + ''
set @moveto = (select (left(@liststr, len(@liststr))))
set @sql = 'insert into [tableto]
from [tablefrom] [query]
where statusflag = 0'
set @sql = replace(@sql, '[tableto]', @tableto)
set @sql = replace(@sql, '[moveto]', @moveto)
set @sql = replace(@sql, '[tablefrom]', @tablefrom)
set @sql = replace(@sql, '[query]', @query)