Search code examples
sqlstored-proceduresselect-into

Creating stored proc for select into statements

I am loading multiple tables by using Select into statments. Now, I need to create a job that runs everyday. My syntax goes like that: drop table select * into from

so what it will do, it will drop tables everyday and create them again. This helps in cathcing any changes in the table. They are small tables so it doesnt take long to drop and create again. Someone told me that I should do a stored proc for this but I have no idea about stored proc. There are about 50 tables I am doing the select into statements for. How do I write a stored proc for all these tables? I am confused please help!

Solution

  • There are different approaches:

    1.Encapsulating job script in a stored procedure : in this approach you will create a stored procedure and put all of your insert into scripts in it and run this procedure in your job, like this

    create procedure spCopyMyTables 
as
drop table t1
select * into t1 from Table1
...
drop table t50
select * into t50 from Table50

    2.Creating a procedure for drop and select into, in this approach you write a stored procedure like this

    Create Procedure spMakeCopyOfTable(@tableName varchar(100),@tempTableName varchar(100)) 
as
   declare @sql nvarchar(max)
   set @sql = 'if object_id('''+@tempTableName+''') is not null Drop table '+@tempTableName
   exec sp_executesql @sql
   set @sql = 'select * into'+tempTableName+' from '+@tableName
   exec sp_executesql @sql

    then you call this procedure like Exec spMakeCopyOfTable('Customers','CopyOFCustomers') in your job

    Also you can combine this two approach like this

    create procedure spCopyMyTables 
as
Exec spMakeCopyOfTable('Table1','t1')
Exec spMakeCopyOfTable('Table2','t2')
...
Exec spMakeCopyOfTable('Table50','t50')

    I hope this helps