create procedure and tables from one database to another and inserting data

Question

Post reply

create procedure and tables from one database to another and inserting data

u4umang2001

SSC Rookie

Points: 49
More actions
April 13, 2009 at 12:07 am

#201915

I have a script to create tables and procedures and to insert data into the tables.
I have to pass the database name dynamically.
Means This script should be executed on the database, which is passed as a parameter.
I have written the script as a procedure but when i execute it, it creates all the tables and procedures in the current database.
CREATE PROCEDURE [dbo].[USP_ExecuteStructureScript]
@DabaseName NVarchar(max)
AS
Declare @QueryStr NVarchar(max)
set @QueryStr= 'USE [' + @DabaseName + ']'
exec (@QueryStr)
---some code to create tables and procedures
upper code also create the tables/procedures in the current database.
how can i create these tables and procedures in another database???

Viewing 4 posts - 1 through 3 (of 3 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this

CREATE PROCEDURE [dbo].[USP_ExecuteStructureScript]

@DabaseName NVarchar(10)

AS

BEGIN

Declare @QueryStr NVarchar(100),

@QueryStr1 NVarchar(1000)

set @QueryStr= N'USE ' + @DabaseName+ CHAR(13)

set @QueryStr1 = N'create table ABCDE('+

'COL1 int,'+

'COL2 int)'

exec (@QueryStr + @QueryStr1 )

END

----

exec USP_ExecuteStructureScript 'TEST'

ARUN SAS

krayknot SSCertifiable Points: 7998 More actions · Answer 2

u4umang2001 (4/13/2009)
I have a script to create tables and procedures and to insert data into the tables.
I have to pass the database name dynamically.
Means This script should be executed on the database, which is passed as a parameter.
I have written the script as a procedure but when i execute it, it creates all the tables and procedures in the current database.
CREATE PROCEDURE [dbo].[USP_ExecuteStructureScript]
@DabaseName NVarchar(max)
AS
Declare @QueryStr NVarchar(max)
set @QueryStr= 'USE [' + @DabaseName + ']'
exec (@QueryStr)
---some code to create tables and procedures
upper code also create the tables/procedures in the current database.
how can i create these tables and procedures in another database???

Use this syntax

[dbo].[server].[tablename]

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

arun.sas SSChampion Points: 11831 More actions · Answer 3

krayknot (4/13/2009)
u4umang2001 (4/13/2009)
I have a script to create tables and procedures and to insert data into the tables.
I have to pass the database name dynamically.
Means This script should be executed on the database, which is passed as a parameter.
I have written the script as a procedure but when i execute it, it creates all the tables and procedures in the current database.
CREATE PROCEDURE [dbo].[USP_ExecuteStructureScript]
@DabaseName NVarchar(max)
AS
Declare @QueryStr NVarchar(max)
set @QueryStr= 'USE [' + @DabaseName + ']'
exec (@QueryStr)
---some code to create tables and procedures
upper code also create the tables/procedures in the current database.
how can i create these tables and procedures in another database???
Use this syntax
[dbo].[server].[tablename]

Hi Krayknot,

How I use this syntax [dbo].[server].[tablename]?

ARUN SAS