SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Execute SQL script in DB 'A' and stored in DB 'B'		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bhushan_juare
Starting Member

44 Posts
Posted - 10/03/2012 :  05:08:41  Show Profile  Reply with Quote
Hi All,
I am monitoring application performance using SQL script where i am using several performance counters. My Counter_Matrix table is in other dabasse where i am inserting data. Now I want some modification in script i.e. no matter where ever my Counter_Matrix table is I want to run that script so that it can insert data in that table only...
Ex: If I run script in DB "A" and my Table is in DB "B" then also I want script to be excuted sucessfully and insert records..

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/03/2012 :  06:47:37  Show Profile  Reply with Quote
Assuming that all the databases are on the same server, you can use 3-part naming convention for the table to insert into a single database regardless of where the query is run from. For example:
INSERT INTO DatabaseA.dbo.Counter_Matrix
(col1, col2, col3)
SELECT colA,colB,colC FROM PerformanceTable;
Go to Top of Page

bhushan_juare
Starting Member

44 Posts
Posted - 10/03/2012 :  08:06:08  Show Profile  Reply with Quote
Hey Sunita,
Thanks for your reply. Here my point of concern is "Database".
Is it necessary that my table should exist in DB where i am goin to execute script ?? I meant I want to track my production DB performance(i.e. DB "A") but I dont have rights to create table in it (Production DB) so what I did is I have created a table in other database say DB "B" and In my script i used " insert into Table A" statement .. and executing for database "B" i.e. I am getting DB "B" performance counters details using that script not DB "A" which I exactly want..
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/03/2012 :  08:35:50  Show Profile  Reply with Quote
You can run the command from any database if you fully qualify the names. For example, it wouldn't matter where you run the command from:
-- Database B is where you have the table to store the info
INSERT INTO DatabaseB.dbo.Counter_Matrix 
(col1,col2,col3)
SELECT colA,colB,colC FROM DatabaseA.dbo.PerformanceTable;
Alternatively, and especially if you are using views, functions, tables etc. from the production database you can change to the production database and then run it:
USE DatabaseA -- Production database
GO

-- Database B is where you have the table to store the info
INSERT INTO DatabaseB.dbo.Counter_Matrix 
(col1,col2,col3)
SELECT colA,colB,colC FROM dbo.PerformanceTable;
Hope that is what you are asking :)
Go to Top of Page

bhushan_juare
Starting Member

44 Posts
Posted - 10/03/2012 :  09:22:52  Show Profile  Reply with Quote
Thanks for your rply...
I changed the script and it worked for me
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000