March 6, 2007 at 2:43 am
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
March 6, 2007 at 4:27 am
-- 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*/
-- expected output would be
#tmpTableA
-------------
MyId DatabaseName Path
210B0219-2A6F-4B98-821A-E924C4FD1FB0 TestDb FolderA>FolderB
March 6, 2007 at 9:21 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply