how to import stored procedure from one database to another? using query....

Question

Post reply

how to import stored procedure from one database to another? using query....

karthimca07

Mr or Mrs. 500

Points: 578
More actions
July 18, 2009 at 6:45 am

#133971

actually i need to import tables and stored procedures from one database to another new database using an sql query. how to do it? please help me out.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Hi there,

check out Server Generate SQL Server Scripts Wizard — http://msdn.microsoft.com/en-us/library/ms181421.aspx

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

You can use three part naming, database.schema.table to reference the other database. The write an insert select statement.

If you try and have issues, we can help, but we don't do work for you. Make an attempt.

karthimca07 Mr or Mrs. 500 Points: 578 More actions · Answer 3

Steve Jones - Editor (7/18/2009)
You can use three part naming, database.schema.table to reference the other database. The write an insert select statement.
If you try and have issues, we can help, but we don't do work for you. Make an attempt.

i have database named DBOIL

under this i hv stored procedures named Insert_IndentRaising

my new database name is DBOIL0910

when im tried as you suggested..,

insert select DBOIL.dbo.Insert_IndentRaising

it gives error..

how to use insert select syntax..?

ps. One Orange Chip Points: 29422 More actions · Answer 4

karthimca07 (7/18/2009)[hr
i have database named DBOIL
under this i hv stored procedures named Insert_IndentRaising
my new database name is DBOIL0910
when im tried as you suggested..,
insert select DBOIL.dbo.Insert_IndentRaising
it gives error..
how to use insert select syntax..?

That will only work for tables not for SPs. You can script out the SPs on the source database and apply the script on the target database.

Also, the insert statement you wrote is syntactically incorrect.

correct syntax is

Insert into mytable from db2.owner2.table. This will create a table myTable in your database similar to the table "table" in db2 and will move all the data.

Pradeep Singh

karthimca07 Mr or Mrs. 500 Points: 578 More actions · Answer 5

ps (7/19/2009)
karthimca07 (7/18/2009)[hr
i have database named DBOIL
under this i hv stored procedures named Insert_IndentRaising
my new database name is DBOIL0910
when im tried as you suggested..,
insert select DBOIL.dbo.Insert_IndentRaising
it gives error..
how to use insert select syntax..?
That will only work for tables not for SPs. You can script out the SPs on the source database and apply the script on the target database.
Also, the insert statement you wrote is syntactically incorrect.
correct syntax is
Insert into mytable from db2.owner2.table. This will create a table myTable in your database similar to the table "table" in db2 and will move all the data.

i script out all stored procedures into an single file.

then i redd the entire content of the file in string

and executed from vb.net.

it shows syntax error

incorrect syntax near "Go"

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

The script is a T-SQL script. You need to execute it in SSMS.