November 12, 2009 at 5:39 am
Hi,
Small requirement
I have two databases "db1" and "db2".
I have table in my database say "db1".
create table Metadata
(tname varchar(100),
cols varchar(1000)
)
insert into Metadata
select 't1','(period varchar(10),name varchar(10),sname varchar(100))'
union all
select 't2','(period varchar(10),name varchar(10),sname varchar(100))'
select * from Metadata ;
Inside,db1 database i also have a stored procedure which will be generating create table script based on metadata table. Here is the stored procedure for which i will be passing a parameter which will
be prefixed in forming the tablename dynamically inside my stored procedure.
create proc usp_generate_create_stmts
@period varchar(100)
as
begin
select 'create table '+@period+'_'+tname+cols from metadata
end
Sample execution
-----------------
usp_generate_create_stmts 'Q1'
Sample output
---------------------
create table Q1_t1(period varchar(10),name varchar(10),sname varchar(100))
create table Q1_t2(period varchar(10),name varchar(10),sname varchar(100))
Now my requirement is that i need to execute the generated output on "db2" database.
In otherwords i want to create tables on "db2" database.
How to accomplish this??
Any help would be greatly appreciated.
November 12, 2009 at 6:13 am
Sorry not tested it. 'USE DB' wont help?
---------------------------------------------------------------------------------
November 12, 2009 at 6:54 am
Nabha has the right idea. You need to have a Use DB2 GO in order to change the database context.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2009 at 9:37 am
if the login that is running the script has rights to both db1 and db2, you could simply qualify the script fully like:
create table [db2].dbo.[<table name>] as .... select * from [db1].dbo.[<some table>] etc..
This also assumes that the databases are on the same server.
Just a thought. 😎
November 12, 2009 at 10:45 pm
Hi All,
I am able to generate the scripts dynamically. just i need the code which can execute the generated sql inside the stored procedure.
The login has all the required permissions to create the tables in "db2".
🙂
November 13, 2009 at 12:31 am
hmm.. along with your script include 'USE db2 GO' in your dynamic SQL and use sp_executesql to execute it.
http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx
BTW, as Doug.Williams mentions, you can use the fully qualified name also!
---------------------------------------------------------------------------------
November 13, 2009 at 7:32 am
Is this what you mean when you want to know how to execute the script?
create procedure test
as
begin
declare @script varchar(1000)
set @script = 'select getDate() as MyDate'
exec(@script)
end
May be way off in understanding the problem. Please clarify.
Doug:cool:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply