Update and Select query without cursor

  • 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

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

     

     

     

  • Use table variables to replace your use of temp table.

    declare @tmpTableA table

    (

    MyId uniqueidentifier,

    DatabaseName varchar(500),

    Path varchar(1000)

    )

    SELECT * FROM @tmpTableA


    Everything you can imagine is real.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply