SQL Server Stored Procedure to create a table

Can someone help me in writing a sp that checks for an existing table, if it's there deletes it and regardless of present status inserts 203 records to it? should run at 2am.

Not sure on what you're platform is, but I'll assume Windows since you're using MS SQL.

Your best and easiest bet would be to write script using VBScript and save it as '.vbs' file. Then use Windows schedular to run it at 2am.

Not sure if MS SQL has any kind of built-in scheduling. And even if you wrote it as a SP, you would still need a vbs or bat file to call it that could be scheduled.

Below is part of the script that was generated by scripting the Customers table on the Northwind db (Action>All tasks>Generate SQL). You'd probably be best following the same method for dropping your own table.

You'd just need to create an SP with the amended script.

Code:

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Customers]
GO

---

You can set the SP to run under as a Job (Management>SQL Server Agent>Jobs). Create a new Step for the Job and set it to type T-SQL and insert the name of the SP in the command box. Then add a new Schedule to run the Job at 2am. Then just start the Job to make sure it's running.

I stand corrected :o