Update MS Sql from OpenQuery command

I need to update a MS Sql table from a Openquery that reaches over a link and get information form an Oracle server.  Not able to get the correct syntax on this.
Select * from OPENQUERY(METRICS, ' 
select  I.OBJECT_NM, 
        f.KPI_SID,  
        a.ACTUAL_VALUE, 
        a.TARGET_VALUE, 
        a.TOLERANCE_VALUE, 
        a.BENCHMARK_VALUE, 
        a.BENCHMARK2_VALUE, 
        a.BENCHMARK3_VALUE, 
        a.BENCHMARK4_VALUE, 
        a.BENCHMARK5_VALUE, 
        a.TIME_PERIOD_SID, 
        b.INTERNAL_FISCAL_YEAR_VALUE, 
        b.language_text_id  
from    METRIC_STORE.KPI_PERIOD_HISTORY a,  
        METRIC_STORE.TIME_PERIODS b,  
        METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and  
        f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        f.kpi_sid = a.kpi_sid and  
        A.time_period_sid = b.time_period_sid and b.time_period_sid = F.LATEST_AVAILABLE_TIME_PERIOD  
union  
select  I.OBJECT_NM, 
		f.KPI_SID,   
        null,null,null,null,null,null,null,null,null,null,null 
from    METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        (f.kpi_sid not in (select kpi_sid from METRIC_STORE.KPI_PERIOD_HISTORY) or  
        f.latest_available_time_period is null)  
order by 1,2') 
Update CognosMetrics.dbo.CognosMetrics set 
		MName = I.OBJECT_NM, 
		KpiSid =  f.KPI_SID, 
		ActualVal, 
		TargetVal, 
		ToleranceVal, 
		Bench1,  
        Bench2, 
		Bench3, 
		Bench4, 
		Bench5, 
		TimePeriodSid, 
		FYTime, 
		Month  
where	f.KPI_SID = KpiSid

Open in new window

kdeutschAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
try like this
;with CTE as (
Select * from OPENQUERY(METRICS, '  
	select  I.OBJECT_NM,  
        	f.KPI_SID,   
	        a.ACTUAL_VALUE,  
        	a.TARGET_VALUE,  
	        a.TOLERANCE_VALUE,  
        	a.BENCHMARK_VALUE,  
	        a.BENCHMARK2_VALUE,  
        	a.BENCHMARK3_VALUE,  
	        a.BENCHMARK4_VALUE,  
        	a.BENCHMARK5_VALUE,  
	        a.TIME_PERIOD_SID,  
        	b.INTERNAL_FISCAL_YEAR_VALUE,  
	        b.language_text_id   
	from    METRIC_STORE.KPI_PERIOD_HISTORY a,   
        	METRIC_STORE.TIME_PERIODS b,   
	        METRIC_STORE.KPI f,   
	        METRIC_STORE.OBJECT_LANGUAGE_TEXT i   
	where   I.CONTENT_OBJECT_SID = f.kpi_sid and   
        	f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and   
	        f.kpi_sid = a.kpi_sid and   
        	A.time_period_sid = b.time_period_sid and b.time_period_sid = F.LATEST_AVAILABLE_TIME_PERIOD   
	union   
	select  I.OBJECT_NM,  
                f.KPI_SID,    
	        null,null,null,null,null,null,null,null,null,null,null  
	from    METRIC_STORE.KPI f,   
        	METRIC_STORE.OBJECT_LANGUAGE_TEXT i   
	where   I.CONTENT_OBJECT_SID = f.kpi_sid and f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and   
        	(f.kpi_sid not in (select kpi_sid from METRIC_STORE.KPI_PERIOD_HISTORY) or   
	        f.latest_available_time_period is null)   
	order by 1,2')  
)
Update a
	 set  
                a.MName = b.OBJECT_NM,  
                a.ActualVal = b.ACTUAL_VALUE,  
                a.TargetVal = b.TARGET_VALUE,  
                a.ToleranceVal = b.TOLERANCE_VALUE,  
                a.Bench1 = b.BENCHMARK_VALUE ,   
        	a.Bench2 = b.BENCHMARK2_VALUE,  
                a.Bench3 = b.BENCHMARK3_VALUE,  
                a.Bench4 = b.BENCHMARK4_VALUE,  
                a.Bench5 = b.BENCHMARK5_VALUE,  
                a.TimePeriodSid = b.TIME_PERIOD_SID,  
                a.FYTime = b.INTERNAL_FISCAL_YEAR_VALUE  
from CognosMetrics.dbo.CognosMetrics a
inner join CTE b b.KPI_SID = a.KpiSid

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kdeutschAuthor Commented:
ralmada:
I also figured from a web example I finally found like this.
Update  t1 set
            MName = t2.OBJECT_NM,
            KpiSid =  t2.KPI_SID,
            ActualVal = t2.ACTUAL_VALUE,
            TargetVal = t2.TARGET_VALUE,
            ToleranceVal = t2.TOLERANCE_VALUE,
            Bench1 = t2.BENCHMARK_VALUE,  
        Bench2 = t2.BENCHMARK2_VALUE,
            Bench3 = t2.BENCHMARK3_VALUE,
            Bench4 = t2.BENCHMARK4_VALUE,
            Bench5 = t2.BENCHMARK5_VALUE,
            TimePeriodSid = t2.TIME_PERIOD_SID,
            FYTime = t2.INTERNAL_FISCAL_YEAR_VALUE,
            Month = t2.language_text_id  
From      CognosMetrics.dbo.CognosMetrics t1 INNER Join
(Select * from OPENQUERY(METRICS, '
select  I.OBJECT_NM,
        f.KPI_SID,  
        a.ACTUAL_VALUE,
        a.TARGET_VALUE,
        a.TOLERANCE_VALUE,
        a.BENCHMARK_VALUE,
        a.BENCHMARK2_VALUE,
        a.BENCHMARK3_VALUE,
        a.BENCHMARK4_VALUE,
        a.BENCHMARK5_VALUE,
        a.TIME_PERIOD_SID,
        b.INTERNAL_FISCAL_YEAR_VALUE,
        b.language_text_id  
from    METRIC_STORE.KPI_PERIOD_HISTORY a,  
        METRIC_STORE.TIME_PERIODS b,  
        METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and  
        f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        f.kpi_sid = a.kpi_sid and  
        A.time_period_sid = b.time_period_sid and b.time_period_sid = F.LATEST_AVAILABLE_TIME_PERIOD  
union  
select  I.OBJECT_NM,
            f.KPI_SID,  
        null,null,null,null,null,null,null,null,null,null,null
from    METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        (f.kpi_sid not in (select kpi_sid from METRIC_STORE.KPI_PERIOD_HISTORY) or  
        f.latest_available_time_period is null)  
order by 1,2')) t2 on t2.KPI_SID = t1.KpiSid
0
ralmadaCommented:
Yep, same concept. I've just used CTE because I find it easier to read. (Just a preference).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The above solution is missing a ON keyword:

inner join CTE b ON  b.KPI_SID = a.KpiSid
Of course you won't need to use a CTE, it is also working without, but both versions do not have any real advantages over each other.

Update a
         set  
                a.MName = b.OBJECT_NM,  
                a.ActualVal = b.ACTUAL_VALUE,  
                a.TargetVal = b.TARGET_VALUE,  
                a.ToleranceVal = b.TOLERANCE_VALUE,  
                a.Bench1 = b.BENCHMARK_VALUE ,   
                a.Bench2 = b.BENCHMARK2_VALUE,  
                a.Bench3 = b.BENCHMARK3_VALUE,  
                a.Bench4 = b.BENCHMARK4_VALUE,  
                a.Bench5 = b.BENCHMARK5_VALUE,  
                a.TimePeriodSid = b.TIME_PERIOD_SID,  
                a.FYTime = b.INTERNAL_FISCAL_YEAR_VALUE  
from CognosMetrics.dbo.CognosMetrics a
inner join (select * from OPENQUERY(METRICS, '  
        select  I.OBJECT_NM,  
                f.KPI_SID,   
                a.ACTUAL_VALUE,  
                a.TARGET_VALUE,  
                a.TOLERANCE_VALUE,  
                a.BENCHMARK_VALUE,  
                a.BENCHMARK2_VALUE,  
                a.BENCHMARK3_VALUE,  
                a.BENCHMARK4_VALUE,  
                a.BENCHMARK5_VALUE,  
                a.TIME_PERIOD_SID,  
                b.INTERNAL_FISCAL_YEAR_VALUE,  
                b.language_text_id   
        from    METRIC_STORE.KPI_PERIOD_HISTORY a,   
                METRIC_STORE.TIME_PERIODS b,   
                METRIC_STORE.KPI f,   
                METRIC_STORE.OBJECT_LANGUAGE_TEXT i   
        where   I.CONTENT_OBJECT_SID = f.kpi_sid and   
                f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and   
                f.kpi_sid = a.kpi_sid and   
                A.time_period_sid = b.time_period_sid and b.time_period_sid = F.LATEST_AVAILABLE_TIME_PERIOD   
        union   
        select  I.OBJECT_NM,  
                f.KPI_SID,    
                null,null,null,null,null,null,null,null,null,null,null  
        from    METRIC_STORE.KPI f,   
                METRIC_STORE.OBJECT_LANGUAGE_TEXT i   
        where   I.CONTENT_OBJECT_SID = f.kpi_sid and f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and   
                (f.kpi_sid not in (select kpi_sid from METRIC_STORE.KPI_PERIOD_HISTORY) or   
                f.latest_available_time_period is null)') b
on b.KPI_SID = a.KpiSid

Open in new window

0
ralmadaCommented:
or u could try
Update  t1 set 
            MName = t2.OBJECT_NM, 
            KpiSid =  t2.KPI_SID, 
            ActualVal = t2.ACTUAL_VALUE, 
            TargetVal = t2.TARGET_VALUE, 
            ToleranceVal = t2.TOLERANCE_VALUE, 
            Bench1 = t2.BENCHMARK_VALUE,  
        Bench2 = t2.BENCHMARK2_VALUE, 
            Bench3 = t2.BENCHMARK3_VALUE, 
            Bench4 = t2.BENCHMARK4_VALUE, 
            Bench5 = t2.BENCHMARK5_VALUE, 
            TimePeriodSid = t2.TIME_PERIOD_SID, 
            FYTime = t2.INTERNAL_FISCAL_YEAR_VALUE, 
            Month = t2.language_text_id  
From      CognosMetrics.dbo.CognosMetrics t1 
INNER Join OPENQUERY(METRICS, ' 
select  I.OBJECT_NM, 
        f.KPI_SID,  
        a.ACTUAL_VALUE, 
        a.TARGET_VALUE, 
        a.TOLERANCE_VALUE, 
        a.BENCHMARK_VALUE, 
        a.BENCHMARK2_VALUE, 
        a.BENCHMARK3_VALUE, 
        a.BENCHMARK4_VALUE, 
        a.BENCHMARK5_VALUE, 
        a.TIME_PERIOD_SID, 
        b.INTERNAL_FISCAL_YEAR_VALUE, 
        b.language_text_id  
from    METRIC_STORE.KPI_PERIOD_HISTORY a,  
        METRIC_STORE.TIME_PERIODS b,  
        METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and  
        f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        f.kpi_sid = a.kpi_sid and  
        A.time_period_sid = b.time_period_sid and b.time_period_sid = F.LATEST_AVAILABLE_TIME_PERIOD  
union  
select  I.OBJECT_NM, 
            f.KPI_SID,   
        null,null,null,null,null,null,null,null,null,null,null 
from    METRIC_STORE.KPI f,  
        METRIC_STORE.OBJECT_LANGUAGE_TEXT i  
where   I.CONTENT_OBJECT_SID = f.kpi_sid and f.scorecard_id = ''MNARNG (NEW)-SC (Sep 09)'' and  
        (f.kpi_sid not in (select kpi_sid from METRIC_STORE.KPI_PERIOD_HISTORY) or  
        f.latest_available_time_period is null)  
order by 1,2') t2 on t2.KPI_SID = t1.KpiSid

Open in new window

0
kdeutschAuthor Commented:
Thanks, i will use this instead, it is easier to use and it gets me using T-sql developed for 2005 and up.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.