locked
Update physical tables as per table variable RRS feed

  • Question

  • Hello all,

    I have one result set in my table variable 

    Now , i want to make dynamic update query to update my DestTablename with SourcetableName 


    for 1st row..

    Update t1

    Set ParticipatingProviderCode= Case when [PAR_FLAG]='Blank/Null' Then 'U'

    From [JSA_CIE_Staging].[Clm].Claim t1

    Inner Join [LandingZone].[dbo].[CPS_Claims] t2 on t1.DestID = t2.SourceID

    but here im getting error of t1.DestID = t2.SourceID (Invalid column)

    tell me how to update this on the basis of t1.DestID = t2.SourceID return by the table variable


    Dilip Patil..

    Tuesday, January 26, 2016 10:04 AM

Answers

All replies

  • Because a table JSA_CIE_Staging].[Clm].Claim or LandingZone].[dbo].[CPS_Claims do not have Destid or SourceID column

    Is possible to have the columns (Destid, SourceID) in that table?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 26, 2016 10:18 AM
    Answerer
  • No tables wont have that columns see the Insert statement tell me if i can update without using table variable

    DECLARE @Temp_tbl TABLE(SourceID int,SourceTable_Name VARCHAR(100),Source_Col_Name VARCHAR(50),Source_Value varchar(50),DestID int,DestTable_name VARCHAR(100),Dest_Col_Name VARCHAR(50),Dest_Value varchar(50))

    --Declare @Des--t_Db_Path varchar(200)='JSA_CIE_Staging.Dbo.' --- Path for Database and schema in which to update as a Destination table 
    insert @Temp_tbl

    SELECT top 5 cwr.CrossWalkRulesID,ISNULL(Source_tbl.DBName,'')+'.'+ISNULL(Source_tbl.SchemaName,'')+'.'+ISNULL(Source_tbl.TableName,'') AS SourceTable_Name,Source_Col.ColumnName ,cwrd.SourceValue
    ,cwrd.CrossWalkRulesID,ISNULL(Dest_tbl.DBName,'')+'.'+ISNULL(Dest_tbl.SchemaName,'')+'.'+ISNULL(Dest_tbl.TableName,'') AS DestTable_name,Dest_Col.ColumnName,cwrd.DestValue
    FROM MetaDataMaster.dbo.CrossWalkRules cwr
    inner join MetaDataMaster.DBO.CrossWalkRuleDetails cwrd ON cwr.CrossWalkRulesID= cwrd.CrossWalkRulesID
    inner join MetaDataMaster.dbo.etltables as Source_tbl on Source_tbl.ETLTablesID =  cwr.SourceETLTableMetaDataID
    inner join MetaDataMaster.dbo.ETLTableMetaData as Source_Col on Source_Col.ETLTablesID= Source_tbl.ETLTablesID 
    and Source_Col.ETLTableMetaDataID= cwrd.[Source Column]
    inner join MetaDataMaster.dbo.etltables as Dest_tbl on Dest_tbl.ETLTablesID =  cwr.DestETLTableMetaDataID
    inner join MetaDataMaster.dbo.ETLTableMetaData as Dest_Col on Dest_Col.ETLTablesID= Dest_tbl.ETLTablesID
     and   Dest_Col.ETLTableMetaDataID= cwrd.[Dest Column]

    where

    select * from @Temp_tbl will give me above screenshot 


    Dilip Patil..

    Tuesday, January 26, 2016 10:25 AM
  • If you want to perform an update to join these tables you must have some columns to join them...

    What are the keys? You can use self join only on temporary table in order get the needed columns to update .


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 26, 2016 10:40 AM
    Answerer