June 15, 2015 at 8:00 am
Hi,
I need to get data from one table to another table (in another database) But I want to check if the data is already existing there
What I have is this.
Table 1
select appname from hfm.dbo.HSX_DATASOURCES
result
appname
----------
COMMA4DIM
test
---------
Table 2
SELECT * FROM HFM_Auxiliar.dbo.HFM_Applications
result
APP_ID Appname
----------- --------------------
1 COMMA4DIM
--------------------------------
I want to have the second table having the appname updated from the first table
But also I want to check if it already exists
If I do this.
USE HFM_Auxiliar
GO
IF NOT EXISTS (Select a.Appname from HFM.dbo.HSX_datasources a
where (select b.AppName from HFM_Applications b) = a.appname)
BEGIN
Insert into HFM_applications
Select appname from HFM.dbo.hsx_datasources
END
---------------
It does not update the second appname into my other table. Because it just sees the first one already and no updates are done.
What do I do wrong. Do I need to loop something so it checks on all appnames?
Kind Regards Peter
June 28, 2015 at 1:37 am
I have a hard time understanding your example, so I don't know if this is the answer you're looking for, but have you looked into the syntax for a MERGE statement? It gives you a lot of control over moving data from one table into another. When the data is not matched (doesn't exist in the destination table), you can do an INSERT, whereas if the data is matched (duplicate row), you can update it. There's more information here, give it a go...
https://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
June 28, 2015 at 3:39 pm
mtlsql (6/28/2015)
I have a hard time understanding your example, so I don't know if this is the answer you're looking for, but have you looked into the syntax for a MERGE statement? It gives you a lot of control over moving data from one table into another. When the data is not matched (doesn't exist in the destination table), you can do an INSERT, whereas if the data is matched (duplicate row), you can update it. There's more information here, give it a go...https://msdn.microsoft.com/en-us/library/bb510625(v=sql.105).aspx
I agree. A merge should work. An example is below:
merge into table1 u -- destination table
using table2 uq -- source table
on u.account_number=uq.account_number -- condition to test
when not matched then insert
(
account_name,
account_number,
affiliate,
email_address,
phone_number,
staff_number,
create_date)
values (
uq.account_name,
uq.account_number,
uq.affiliate,
uq.email_address,
uq.phone_number,
uq.staff_number,
uq.create_date);
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
July 10, 2015 at 9:23 am
You might be able to get away with the following:
INSERT INTO HFM_Auxiliar.dbo.HFM_Applications (Appname)
SELECT DISTINCT Appname
FROM hfm.dbo.HSX_DATASOURCES
EXCEPT
SELECT DISTINCT Appname
FROM HFM_Auxiliar.dbo.HFM_Applications
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply