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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update and Select query without cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashishmgupta
Starting Member

12 Posts

Posted - 03/06/2007 :  04:38:15  Show Profile  Reply with Quote
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
30265 Posts

Posted - 03/06/2007 :  04:40:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE TableA
SET TableA.DatabaseName = vw_GetPath.DatabaseName
FROM TableA
INNER JOIN vw_GetPath ON vw_GetPath.MyID = TableA.MyID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashishmgupta
Starting Member

12 Posts

Posted - 03/06/2007 :  05:30:06  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/06/2007 :  05:34:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You better supply some sample data and your expected output...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ashishmgupta
Starting Member

12 Posts

Posted - 03/06/2007 :  06:21:02  Show Profile  Reply with Quote
-- 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*/
Go to Top of Page

ashishmgupta
Starting Member

12 Posts

Posted - 03/06/2007 :  06:23:50  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/06/2007 :  06:26:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

ashishmgupta
Starting Member

12 Posts

Posted - 03/07/2007 :  07:43:06  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/07/2007 :  07:49:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What more can I help you with?
Do you not see where I am heading?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/07/2007 :  07:55:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have to prefix all columns and tables with DATABASE name as well when working over differenet databases.


Peter Larsson
Helsingborg, Sweden
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.12 seconds. Powered By: Snitz Forums 2000