CODEWITHSUNDEEP
sql-serverstored-proceduresimportgenerate-scripts
Chris
Chris

Reputation: 6760

Import Existing Stored Procedures In SQL Server

I restored my development database from production, and the stored procedures I need in my development environment doesn't exist in my production database. Is there a command Ii can use to import the developmetn stored procedures back into SQL Server. There are about 88 files, as each procedure is in a different text file.

TIA! Chris

Upvotes: 5

Views: 34249

Answers (6)

pramod rai
pramod rai

Reputation: 19

  1. Right click on the database from where you want to transfer the data
  2. Select Data Transfer
  3. Select Tables or Store Procedure (what you want to transfer)
  4. Select the location where you want to transfer the data (either on server or localhost or any file)

Upvotes: 1

David Hogan
David Hogan

Reputation: 569

If like me you have to deal with a bunch of sql files in a hierarchy of folders, this one liner will combine them into a single file called out.sql which you can easily execute in SQL Management studio. It will only include files that END in .sql, and ignore files such as *.sqlsettings.

Run it from the root folder of the hierarchy of .sql files. Be sure you have nothing of value in out.sql, as it will be replaced.

del out.sql && for /f %f in ('dir /b /s ^| findstr /E \.sql') do type %f >> out.sql

Upvotes: 0

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421978

Oops, you did the painful way of generating scripts. You should have created a single script for all procedures by right clicking on the database in SSMS, choosing Tasks -> Generate Scripts.

However, if you don't want to go through that process again, open up a cmd shell in the folder and remember those old batch file days:

for %f in (*.sql) do sqlcmd -i %f

This should do the trick! You could add other parameters to sqlcmd if required (i.e. login, password, server name, ...). To see a list of switches just do a sqlcmd -h.

Upvotes: 11

Austin Salonen
Austin Salonen

Reputation: 50215

For SQL 2K & 2K5, you want this tool.

I asked a similar question awhile ago and got this advice from Mike L (give him votes here).

Upvotes: 1

BFree
BFree

Reputation: 103740

I don't know if there's a command line way to do it, but if you have them all in text files, it shouldn't be difficult at all to write a quick down and dirty app that just loops through all the files, and runs the create statements on your production server using whatever language you choose.

Upvotes: 0

Martijn Laarman
Martijn Laarman

Reputation: 13536

Right click on the development database Hit Generate SQL Scripts and then only select stored precedures. If you need need additional filtering you can even select the stored procedures you dont want.

Then just run that query on development.

Upvotes: 0

Related Questions