Query Help

Question

Post reply

Query Help

DBA12345

Right there with Babe

Points: 742
More actions
February 5, 2013 at 12:48 pm

#273893

I need to write a query by comparing the two tables and add the new data if any.
Table A and TableB
TableA has follwoing rows:
Server name
Application ID
TableB has follwoing rows:
Server name
Application ID
I need to compare TableA with TableB. We need to figure out what are the new serevrs are addedd in the tableB need to be inserted in TableA.
As IS
TableATableB
server1 server1
server2server2
server3server3
server4server4
server5
server6
server7
server8
To BE
TableATableB
server1 server1
server2server2
server3server3
server4server4
server5server5
server6server6
server7server7
server8server8

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

This should do the trick:

--Setup

IF OBJECT_ID('tempdb..#t1') IS NOT NULL

DROP TABLE #t1;

IF OBJECT_ID('tempdb..#t2') IS NOT NULL

DROP TABLE #t2;

CREATE TABLE #t1 ([Server] varchar(10) primary key,

[Name] varchar(10) NOT NULL,

AppID int NOT NULL);

CREATE TABLE #t2([Server] varchar(10) primary key,

[Name] varchar(10) NOT NULL,

AppID int NOT NULL);

INSERT INTO #t1 VALUES ('server1','x',1),('server2','x',2), ('server3','x',3);

INSERT INTO #t2 VALUES ('server1','x',1);

-- The data

SELECT * FROM #t1

SELECT * FROM #t2

-- The Update

INSERT INTO #t2

SELECT [Server], Name, AppID

FROM #t1 WHERE [Server] NOT IN (SELECT Server FROM #t2)

SELECT * FROM #t1

SELECT * FROM #t2

DROP TABLE #t1;

DROP TABLE #t2;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DBA12345 Right there with Babe Points: 742 More actions · Answer 2

DBA12345

Right there with Babe

Points: 742

February 5, 2013 at 1:20 pm

#1584577

Thanks alot...I appreciate your help

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

DBA12345 (2/5/2013)
Thanks alot...I appreciate your help

NP

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Another way:

INSERT INTO TableB

SELECT * FROM TableB

EXCEPT

SELECT * FROM TableA

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St