| Author |
 Topic  |
|
|
ashishmgupta
Starting Member
12 Posts |
 Posted - 03/06/2007 : 04:38:15
|
I have a table 'TableA'
Create Table TableA
(
MyId uniqueidentifier,
DatabaseName varchar(500),
)
The "DatabaseName" column for a "MyId" actually contains name of a database which contains a view say 'vw_GetPath' when supplied a "MyId". Now for each "MyId" ,I need to query the <DatabaseName>..vw_GetPath to get the path and display the path along with other values of TableA
Currently,I am pouring the contents of TableA into a temp table which also contains the "Path" column.And using a cursor on the temp table to fetch "DatabaseName", querying the <DatabaseName>..vwGetPath for each id,getting the path,updating in the "Path" column of the temp table and at last selecting from the temp table.
As anybody would think,this is very expensive operation and so I am looking for a solution without a cursor.
Thanks in advance.
Ashish |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/06/2007 : 04:40:09
|
UPDATE TableA
SET TableA.DatabaseName = vw_GetPath.DatabaseName
FROM TableA
INNER JOIN vw_GetPath ON vw_GetPath.MyID = TableA.MyID
Peter Larsson
Helsingborg, Sweden |
 |
|
|
ashishmgupta
Starting Member
12 Posts |
Posted - 03/06/2007 : 05:30:06
|
Thanks for replying Peter,
DatabaseName is not a column in the vw_GetPath. DatabaseName is a column in the TableA which contains names of actual physical databases for each MyID and each of these databases have the view "vw_GetPath". This view returns a "Path" when supplied an Id. For each MyId in the TableA I need to get the "Path" by querying the view which is there in the database for that particular id.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/06/2007 : 05:34:56
|
You better supply some sample data and your expected output...
Peter Larsson
Helsingborg, Sweden |
|
|
|
ashishmgupta
Starting Member
12 Posts |
Posted - 03/06/2007 : 06:21:02
|
-- Herewith I am supplying some sample script and data:-
-- Using 1st database
USE NorthWind
GO
IF Object_id('Northwind..TableA') IS NOT NULL
BEGIN
DROP TABLE tableA
END
--Creating table
Create Table TableA
(
MyId uniqueidentifier,
DatabaseName varchar(500),
)
--Inserting dome sample data, Notice 'TestDb'. This would be actual physical database
INSERT INTO TableA VALUES
('210B0219-2A6F-4B98-821A-E924C4FD1FB0','TestDb')
IF Object_Id('tempdb..#tmpTableA') IS NOT NULL
BEGIN
DROP TABLE #tmpTableA
END
-- Creating the temp table
Create Table #tmpTableA
(
MyId uniqueidentifier,
DatabaseName varchar(500),
Path varchar(1000)
)
-- Pouring data from to the temp table
INSERT INTO #tmpTableA SELECT MyId,DatabaseName,null FROM TableA
-- Selecting to see if everything is OK
SELECT * FROM TableA
SELECT * FROM #tmpTableA
--#####################################################################
--Now I would create a database with name 'TestDb'
IF EXISTS(SELECT TOP 1 Name FROM Master..sysdatabases WHERE Name='TestDb')
BEGIN
PRINT 'here'
DROP DATABASE TestDb
END
CREATE DATABASE TestDb
USE TestDb
-- Create a table
CREATE TABLE TableB
(
MyId uniqueidentifier,
Path varchar(2000)
)
-- Have some values
INSERT INTO TableB VALUES
('210B0219-2A6F-4B98-821A-E924C4FD1FB0','FolderA>FolderB')
GO
-- Create a view to access the table
IF Object_Id('TestDb..vwGetPath') IS NOT NULL
BEGIN
DROP VIEW vwGetPath
END
GO
CREATE VIEW vwGetPath
AS
SELECT * FROM TableB
GO
-- Select from view
SELECT * FROM vwGetPath
/* Now the question is I want to update the path column of the Northwind..#tmpTableA table
from the TestDb..vwGetPath view making use of DatabaseName value of the Northwind..#tmpTableA table*/
|
|
|
|
ashishmgupta
Starting Member
12 Posts |
Posted - 03/06/2007 : 06:23:50
|
expected output would be
#tmpTableA
-------------
MyId DatabaseName Path
210B0219-2A6F-4B98-821A-E924C4FD1FB0 TestDb FolderA>FolderB
Please note that I can not hardcode the database name as there are thousands of records for different databases.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/06/2007 : 06:26:16
|
Same thing as before, just other column names.
What is so hard for you to understand the concept about this?
UPDATE #TableA
SET #TableA.Path = vw_GetPath.Path
FROM TableA
INNER JOIN vw_GetPath ON vw_GetPath.MyID = #TableA.MyID
Peter Larsson
Helsingborg, Sweden |
|
|
|
ashishmgupta
Starting Member
12 Posts |
Posted - 03/07/2007 : 07:43:06
|
Peter,
Thanks for replying. Your query:-
UPDATE #TableA
SET #TableA.Path = vw_GetPath.Path
FROM TableA
INNER JOIN vw_GetPath ON vw_GetPath.MyID = #TableA.MyID
This does not address the problem as vw_GetPath does not exist in the NorthWind database as #TableA.vw_GetPath exists in the databases names of which are there in the #TableA.DatabaseName. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/07/2007 : 07:49:44
|
What more can I help you with?
Do you not see where I am heading?
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/07/2007 : 07:55:14
|
You have to prefix all columns and tables with DATABASE name as well when working over differenet databases.
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
Update and Select query without cursor
