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

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#
sepupic Reply to 2018-01-12 13:38:22Z

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.

VipinK
4#
VipinK Reply to 2018-01-12 13:58:38Z

To execute your sql file from Stored procedure .. you can use xp_cmdshell. See steps below

  1. First Create a Batch File (C:\testApps\test.bat) and execute your sql file from there..
    e.g. osql -S TestSQlServer -E -I C:\testApps\test.sql > C:\testApps\tlog.txt

  2. Then add this line to your Calling Stored procedure exec xp_cmdshell 'C:\testApps\test.bat'

  3. Execute your procedure **Please note you will need to enable xp_cmdshell

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30886 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO