Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-07-09 : 14:26:38

I hae to update the tableA. Server names are same in both tables but in tableB we have more Appnames for the same severs. So, I have to pull those AppNames with server names. Here I am showing the samll example.

We need to get the missing appnames from tableB

TableA

Server ||||| AppName

ServerA ||||| App1
ServerB ||||| App2
ServerC ||||| App3
ServerD ||||| App4
ServerE ||||| App5

TableB

Server ||||| AppName

ServerA ||||| App1
ServerA ||||| App11
ServerA ||||| App111
ServerB ||||| App2
ServerB ||||| App22
ServerC ||||| App3
ServerC ||||| App33
ServerD ||||| App4
ServerD ||||| App44
ServerD ||||| App444
ServerE ||||| App5
ServerE ||||| App55
ServerE ||||| App555

Required Output in Table A

Server ||||| AppName

ServerA ||||| App1
ServerA ||||| App11
ServerA ||||| App111
ServerB ||||| App2
ServerB ||||| App22
ServerC ||||| App3
ServerC ||||| App33
ServerD ||||| App4
ServerD ||||| App44
ServerD ||||| App444
ServerE ||||| App5
ServerE ||||| App55
ServerE ||||| App555

Please help me.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 14:30:43

Did you want to insert the missing rows into TableA, or just write a query to retrieve the data using both tables? If you want to insert the missing data, use this:

INSERT INTO TableA 
SELECT
	Server, AppName
FROM
	TableB b
WHERE NOT EXISTS
(SELECT * FROM TableA a WHERE
a.SERVER = b.SERVER AND a.Appname = b.Appname);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 14:32:40

[code]
INSERT INTO TableA (Server,AppName)
SELECT Server,AppName
FROM TableB b
WHERE NOT EXISTS (SELECT 1
FROM Table A
WHERE Server = b.Server
AND AppName = b.AppName
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aswindba1
Yak Posting Veteran

62 Posts

Posted - 2013-07-09 : 15:33:56

Thank you folks..I appreicate your help.

That query absolutely worked for me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-10 : 03:27:59

Use MERGE

MERGE	dbo.TableA AS tgt
USING	dbo.TableB AS src ON src.[Server] = tgt.[Server]
		AND src.AppName = tgt.AppName
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					[Server],
					AppName
				)
			VALUES	(
					src.[Server],
					src.AppName
				);

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources