Home I've generated an sql file full of inserts but can't find any documentation of executing this script from a stored procedure

# I've generated an sql file full of inserts but can't find any documentation of executing this script from a stored procedure

Jack Tyler
1#
Jack Tyler Published in 2018-01-12 11:57:48Z
 I'm creating a stored procedure that will delete all the data in my database and then insert the data from my sql file. The reason I am using the delete and insert instead of a restore is because a restore requires that no one is connected to the database where as deleting and inserting allows people to still be connected. Stored Procedure: CREATE PROCEDURE DropAndRestore -- Add the parameters for the stored procedure here @filepath nvarchar(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here Exec sp_MSFOREACHTABLE 'delete from ? RESTORE DATABASE [landofbeds] -- These lines are what needs to be replaced FROM DISK = @FilePath -- END GO 
L. Ivicz
2#
L. Ivicz Reply to 2018-01-12 12:29:49Z
 You can use bulk insert like this: BULK INSERT landofbeds.dbo.SalesOrderDetail FROM '\\computer\share\folder\neworders.txt' 
sepupic
3#
 The reason I am using the delete and insert instead of a restore is because a restore requires that no one is connected to the database where as deleting and inserting allows people to still be connected If all you need is minimum downtime you can restore your database in db_copy. Then drop your db and rename db_copy to db. Yes you should disconnect all the users to be able to drop your db, but it will take minimum time, while if you delete your data the table will still be unavailable for the whole duration of the delete, and as delete is always fully logged your users will wait. To launch your script you can use xp_cmdshell that calls sqlcmd with -i but it's not a good idea. You have no control on your script execution and if something goes wrong you will have even more downtime for your users. Does your tables have FK defined? Exec sp_MSFOREACHTABLE 'delete from ?  will try to delete everything in order it decides and you may end up with errors when you try to delete rows that are referenced in other tables.