January 26, 2010 at 11:38 pm
Looking for a little bit of help,
Having a little issue with inserting data from one server to another, we only want to include the ID and versions that don't already exist. Simple enough if i was just the ID but need to also with version
insert into server1.databaseA.dbo.tableA
SELECT
ID, version, Versiontype
FROM server2.databaseA.dbo.tableA
where ID not in (select ID from server1.databaseA.dbo.tableA where ....
and Versiontype not in (select ID from server1.databaseA.dbo.tableA where .... (maybe something like this??)
Any help would be gratefully appreciated 🙂
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
January 26, 2010 at 11:43 pm
Are u getting any problem with the syntax given?
January 26, 2010 at 11:53 pm
well no, the problem is that nothing is coming back because its seeing it as two independent fields, where in fact they are related, ei.
Server1
ID, Version
1, 1
1, 2
1, 3
2, 1
2, 2
Server2
ID, Version
1, 1
1, 2
1, 3
2, 1
Based in the before mentioned script it does not insert record (2, 2) because ID exist in Server2 and as does Version(1, 2)
I'm sorry i think i didn't explain properly before :hehe:
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
January 27, 2010 at 12:31 am
Not sure if it would behave differently in linked server context!
DROP TABLE #S1
DROP TABLE #S2
CREATE TABLE #S1(ID int, VERSION int)
CREATE TABLE #S2(ID int, VERSION int)
INSERT INTO #S1 VALUES(1, 1)
INSERT INTO #S1 VALUES(1, 2)
INSERT INTO #S1 VALUES(1, 3)
INSERT INTO #S1 VALUES(2, 1)
INSERT INTO #S1 VALUES(2, 2)
INSERT INTO #S2 VALUES(1, 1)
INSERT INTO #S2 VALUES(1, 2)
INSERT INTO #S2 VALUES(1, 3)
INSERT INTO #S2 VALUES(2, 1)
INSERT INTO #s2
SELECT * FROM #S1 S1
Where NOT EXISTS
(SELECT 1
FROM #S2 S2
WHERE S1.ID = S2.ID
AND S1.VERSION = S2.VERSION)
---------------------------------------------------------------------------------
January 27, 2010 at 12:48 am
Thanks heaps i think this will work.
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
January 27, 2010 at 4:21 am
I think query should be :
insert into server1.databaseA.dbo.tableA (id,version,versionType)
SELECT
A2.ID,
A2.version,
A2Versiontype
FROM server2.databaseA.dbo.tableA as A2
where ID not in
(select ID from server1.databaseA.dbo.tableA as A inner join
server2.databaseA.dbo.tableA as B on
A.id=B.id and a.version=b.version)
January 27, 2010 at 4:21 am
I think query should be :
insert into server1.databaseA.dbo.tableA (id,version,versionType)
SELECT
A2.ID,
A2.version,
A2Versiontype
FROM server2.databaseA.dbo.tableA as A2
where ID not in
(select ID from server1.databaseA.dbo.tableA as A inner join
server2.databaseA.dbo.tableA as B on
A.id=B.id and a.version=b.version)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply