-1

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!

Stephanie
  • 496
  • 7
  • 26

1 Answers1

0

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

Reza
  • 18,865
  • 13
  • 88
  • 163
  • Thank you so much! So when you encapsulate them does that mean all the codes I have for multiple tables will be used under one stored proc? and about the 2nd approach, it seems like it is written for only one table. So I have to write 50 stored procs for 50 tables? Wouldn't that be 50 exec jobs? sorry just a newbie. I appreciate your help! – Stephanie Apr 10 '14 at 16:01
  • I have edit my answer to cover your questions in comment – Reza Apr 10 '14 at 16:06
  • Thanks! Just for confirmation: First create stored procs for those fifty tables then create the storedproc1 to call those 50 tables and storedproc1 will be executed under the job? Right? Also, if those fifty tables in future increases to maybe over hundred, is that okay to create over hundred stored procs? – Stephanie Apr 10 '14 at 16:22
  • No, you just need to create 2 procedure for no matter 50 or 100 tables, first `spMakeCopyOfTable` which is a general dynamic procedure, then `spCopyMyTables` and in the body you call `spMakeCopyOfTable` for all of your table via parameters, so if you had 100 tables you only need to add them in `spCopyMyTables` – Reza Apr 10 '14 at 16:26
  • Got it! greatly explained. Thanks once again! – Stephanie Apr 10 '14 at 17:37