Execute SQL script in DB 'A' and stored in DB 'B'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execute SQL script in DB 'A' and stored in DB 'B'

Author

Topic

bhushan_juare
Starting Member

45 Posts

Posted - 2012-10-03 : 05:08:41

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 06:47:37

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;

bhushan_juare
Starting Member

45 Posts

Posted - 2012-10-03 : 08:06:08

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..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 08:35:50

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 :)

bhushan_juare
Starting Member

45 Posts

Posted - 2012-10-03 : 09:22:52

Thanks for your rply...
I changed the script and it worked for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources