Update and Select query without cursor

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update and Select query without cursor

Author

Topic

ashishmgupta
Starting Member

12 Posts

Posted - 2007-03-06 : 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

30421 Posts

Posted - 2007-03-06 : 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 - 2007-03-06 : 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

30421 Posts

Posted - 2007-03-06 : 05:34:56

You better supply some sample data and your expected output...

Peter Larsson
Helsingborg, Sweden

ashishmgupta
Starting Member

12 Posts

Posted - 2007-03-06 : 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 - 2007-03-06 : 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

30421 Posts

Posted - 2007-03-06 : 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 - 2007-03-07 : 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

30421 Posts

Posted - 2007-03-07 : 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

30421 Posts

Posted - 2007-03-07 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources