November 17, 2016 at 1:45 am
Guys,
Kindly help my problem,
[LINKSERVER].m_product = Source Server
master.dbo.m_product = Destination Server
select m_id,m_name from openquery ([Linkserver],'select m_id,m_name from master.dbo.M_PRODUCT') a
left join master.dbo.M_PRODUCT b
on a.m_id = b. m_id
where a.m_id not in (select m_id from master.dbo.M_PRODUCT)
example the result m_id = '0000' is nothing
1 row(s) affected
--When Update the data
Update b set b.m_id = a.m_id
from openquery ([Linkserver],'select m_id,m_name from master.dbo.M_PRODUCT') a
left join master.dbo.M_PRODUCT b
on a.m_id = b. m_id
where a.m_id not in (select m_id from master.dbo.M_PRODUCT)
the Result always 0
May I know if there is something wrong with my Syntax ?
November 17, 2016 at 1:50 am
Is that first query the actual query you ran? I'm surprised you don't get an ambiguous column error for the m_id in your select list.
John
November 17, 2016 at 1:52 am
John Mitchell-245523 (11/17/2016)
Is that first query the actual query you ran? I'm surprised you don't get an ambiguous column error for the m_id in your select list.John
Dear John,
Yes it was my first query that i ran
November 17, 2016 at 2:04 am
You are using SQL Server, aren't you? What version?
Your update statement doesn't make sense, either. You're updating to set m_id in the outer table to be equal to m_id in the inner table. But they're already equal, since the join predicate is on equality between the two columns. The only time you won't have equality (since it's an outer join) is when there's no match in the outer table, but in that case there'll be nothing to update in the outer table, anyway. What are you trying to achieve? Table DDL and sample data would help here.
John
November 17, 2016 at 2:30 am
John Mitchell-245523 (11/17/2016)
You are using SQL Server, aren't you? What version?Your update statement doesn't make sense, either. You're updating to set m_id in the outer table to be equal to m_id in the inner table. But they're already equal, since the join predicate is on equality between the two columns. The only time you won't have equality (since it's an outer join) is when there's no match in the outer table, but in that case there'll be nothing to update in the outer table, anyway. What are you trying to achieve? Table DDL and sample data would help here.
John
I'm using SQL Server 2014
If I select the source data :
Select * from openquery ([linkserver],'select m_id,m_name from master.dbo.m_product')
it result 279 row(s) affected
if i select the destination data:
Select * from master.dbo.m_product
it result 278 row(s) affected
I want to update the data where there is not exist at the Destination server from the Source
November 17, 2016 at 2:42 am
But if the data doesn't exist at the destination server, there's nothing to update. You need to do an INSERT instead. In the absence of the requested table DDL, this is only a guess:
INSERT INTO master.dbo.M_PRODUCT (m_id, m_name)
SELECT
a.m_id
,a.m_name
FROM OPENQUERY (
[Linkserver]
,'SELECT m_id,m_name FROM master.dbo.M_PRODUCT'
) AS a
LEFT JOIN master.dbo.M_PRODUCT b ON a.m_id = b. m_id
WHERE b.m_id IS NULL -- no match in outer table
John
November 17, 2016 at 3:01 am
John Mitchell-245523 (11/17/2016)
But if the data doesn't exist at the destination server, there's nothing to update. You need to do an INSERT instead. In the absence of the requested table DDL, this is only a guess:
INSERT INTO master.dbo.M_PRODUCT (m_id, m_name)SELECT
a.m_id
,a.m_name
FROM OPENQUERY (
[Linkserver]
,'SELECT m_id,m_name FROM master.dbo.M_PRODUCT'
) AS a
LEFT JOIN master.dbo.M_PRODUCT b ON a.m_id = b. m_id
WHERE b.m_id IS NULL -- no match in outer table
John
Yes it works,,
thanks Jhon,,
so i cant do the update table ya ?
November 17, 2016 at 3:12 am
Not if there's no row to update, no. But say, for example, you were testing for rows that have a match on m_id but have different values for m_name. Then you'd use an UPDATE statement to change the value of m_name in the destination to match that in the source.
John
November 17, 2016 at 6:42 pm
John Mitchell-245523 (11/17/2016)
Not if there's no row to update, no. But say, for example, you were testing for rows that have a match on m_id but have different values for m_name. Then you'd use an UPDATE statement to change the value of m_name in the destination to match that in the source.John
i got it,,tahnk you very much..
So if there is no row to update then use insert statement from the source
But if there is a row and one of the column is missing then use update statement
am I right ?
November 18, 2016 at 2:17 am
That's right, yes - also if the column has the wrong value you can update it to the correct one.
You might also consider using the MERGE statement, which inserts and updates all in one. Beware, though - bugs have been reported, so make sure you do a bit of reading if you choose that route.
John
November 30, 2016 at 11:12 am
On an unrelated note, you've created these objects in the master database. That is a very bad idea. If at all possible, you should move those to a dedicated database.
Wes
(A solid design is always preferable to a creative workaround)
November 30, 2016 at 7:56 pm
whenriksen (11/30/2016)
On an unrelated note, you've created these objects in the master database. That is a very bad idea. If at all possible, you should move those to a dedicated database.
Dear Whenricksen,
Im not crerate it at master ddatabase,its in dedicated database 🙂
thanks for reminding me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply