Newbie Design / T-SQL question

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

Database Design and Application Architecture

Newbie Design / T-SQL question

Author

Topic

sqlghost
Starting Member

23 Posts

Posted - 2007-02-06 : 10:32:31

Hi all,

I need to pull server names from 4 different data sources; A, B, C, and D. My objective is to determine which servers are missing from each data source. So for example, let's say I have the following data files:

A.txt:
Server1
Server2
Server3
Server4

B.csv:
Server2
Server3

C.csv:
Server3
Server4

D.xls:
Server1
Server2
Server3

I want to import this data into SQL Server and run a query that would show me that;
B is missing Server1 and Server4
C is missing Server1 and Server2
D is missing Server4

Would I import each data file into it's own 1 column database, or would I import each data file into 4 1 column tables within the same database? Also, what T-SQL query would I use to get the desired result?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 10:41:13

4 single column tables


declare @tableA table
(
	server	varchar(10)
)

declare @tableB table
(
	server	varchar(10)
)

declare @tableC table
(
	server	varchar(10)
)

declare @tableD table
(
	server	varchar(10)
)

insert into @tableA
select	'Server1'	union all
select	'Server2'	union all
select	'Server3'	union all
select	'Server4'

insert into @tableB
select	'Server2'	union all
select	'Server3'	

insert into @tableC
select	'Server3'	union all
select	'Server4'

insert into @tableD
select	'Server1'	union all
select	'Server2'	union all
select	'Server3'	

select	s.server
from	@tableB b right join
	(
		select	server from @tableA union
		select	server from @tableC union
		select	server from @tableD
	) s
	on	b.server	= s.server
where	b.server	is null

select	s.server
from	@tableC c right join
	(
		select	server from @tableA union
		select	server from @tableB union
		select	server from @tableD
	) s
	on	c.server	= s.server
where	c.server	is null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 02:10:47

[code]-- Prepare sample data
declare @tableA table (server varchar(10))

insert @tableA
select 'Server1' union all
select 'Server2' union all
select 'Server3' union all
select 'Server4'

declare @tableB table (server varchar(10))

insert @tableB
select 'Server2' union all
select 'Server3'

declare @tableC table (server varchar(10))

insert @tableC
select 'Server3' union all
select 'Server4'

declare @tableD table (server varchar(10))

insert @tableD
select 'Server1' union all
select 'Server2' union all
select 'Server3'

-- Stage the data
DECLARE @Stage TABLE (Source VARCHAR(1), Server VARCHAR(10))

INSERT @Stage
(
Source,
Server
)
SELECT a.Source,
b.Server
FROM (
SELECT 'A' AS Source
UNION
SELECT 'B'
UNION
SELECT 'C'
UNION
SELECT 'D'
) AS a
CROSS JOIN (
SELECT Server
FROM @tableA
UNION
SELECT Server
FROM @tableB
UNION
SELECT Server
FROM @tableC
UNION
SELECT Server
FROM @tableD
) AS b
LEFT JOIN (
SELECT 'A' AS Source,
Server
FROM @TableA
UNION
SELECT 'B',
Server
FROM @TableB
UNION
SELECT 'C',
Server
FROM @TableC
UNION
SELECT 'D',
Server
FROM @TableD
) AS c ON c.Source = a.Source AND c.Server = b.Server
WHERE c.Source IS NULL

-- Create the output
DECLARE @Output TABLE (Message VARCHAR(8000))

DECLARE @CurrSource VARCHAR(1),
@MaxSource VARCHAR(1),
@msg VARCHAR(8000)

SELECT @CurrSource = MIN(Source),
@MaxSource = MAX(Source)
FROM @Stage

WHILE @CurrSource <= @MaxSource
BEGIN
SELECT @msg = NULL

SELECT @msg = ISNULL(@msg + ' and ', '') + d.Server
FROM (
SELECT DISTINCT TOP 100 PERCENT Server
FROM @Stage
WHERE Source = @CurrSource
ORDER BY Server
) AS d

INSERT @Output
(
Message
)
SELECT @CurrSource + ' is missing ' + @msg

SELECT @CurrSource = MIN(Source)
FROM @Stage
WHERE Source > @CurrSource
END

SELECT Message
FROM @Output
ORDER BY Message[/code]

Peter Larsson
Helsingborg, Sweden

sqlghost
Starting Member

23 Posts

Posted - 2007-02-07 : 11:28:19

Thanks a ton!!

Here's what I did to get the desired result:

I created a DB called Servers and imported the data into 4 one column tables (TableA, TableB, TableC, and TableD). Next, I ran the following query:

select s.ServerName
from TableB b right join
(
select ServerName from TableA union
select ServerName from TableC union
select ServerName from TableD
) s
on b.ServerName = s.ServerName
where b.ServerName is null

The example above, returns all of the servers not in TableB, but found in at least one of Tables other than TableB. I did the corresponding SQL query for each other Table. This achieved my desired results. However, I realized that I'd like as a secondary objective, the ability to show where the servers DO exist. So in the example above, my results would be:

ServerName
Server1
Server4

What I'd like to show is this:

ServerName A B C D
Server1 x Null Null x
Server4 x Null x Null

I thought I would add 4 columns to each of my 4 tables; A, B, C, D and populate the appropriate column with an 'x' for each record to designate it's origin. Next, I thought the following would give me what I needed:

select s.ServerName, s.A, s.B, s.C, s.D
from TableB b right join
(
select ServerName, A, B, C, D from TableA union
select ServerName, A, B, C, D from TableC union
select ServerName, A, B, C, D from TableD
) s
on b.ServerName = s.ServerName
where b.ServerName is null

This kinda gave me what I wanted, except for the fact that it listed duplicate servername records:

ServerName A B C D
Server1 x Null Null Null
Server1 Null Null Null x
Server4 x Null Null Null
Server4 Null Null x Null

Ie, there cannot be two "x"'s in the same row. Is there an easy way to do this?

Thanks so much for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 13:11:06

The only thing different from previous suggestion is the change from LEFT JOIN to INNER JOIN and the removal of WHERE c.Source IS NULL

-- Prepare sample data
declare @tableA table (server varchar(10))

insert	@tableA
select	'Server1' union all
select	'Server2' union all
select	'Server3' union all
select	'Server4'

declare @tableB table (server varchar(10))

insert	@tableB
select	'Server2' union all
select	'Server3'	

declare @tableC table (server varchar(10))

insert	@tableC
select	'Server3' union all
select	'Server4'

declare @tableD table (server varchar(10))

insert	@tableD
select	'Server1' union all
select	'Server2' union all
select	'Server3'	

-- Stage the data
DECLARE	@Stage TABLE (Source VARCHAR(1), Server VARCHAR(10))

INSERT	    @Stage
	    (
		Source,
		Server
	    )
SELECT	    a.Source,
	    b.Server
FROM	    (
		SELECT	'A' AS Source
		UNION
		SELECT	'B'
		UNION
		SELECT	'C'
		UNION
		SELECT	'D'
	    ) AS a
CROSS JOIN  (
		SELECT	Server
		FROM	@tableA
		UNION
		SELECT	Server
		FROM	@tableB
		UNION
		SELECT	Server
		FROM	@tableC
		UNION
		SELECT	Server
		FROM	@tableD
	    ) AS b
INNER JOIN  (
		SELECT	    'A' AS Source,
			    Server
		FROM	    @TableA
		UNION
		SELECT	    'B',
			    Server
		FROM	    @TableB
		UNION
		SELECT	    'C',
			    Server
		FROM	    @TableC
		UNION
		SELECT	    'D',
			    Server
		FROM	    @TableD
	    ) AS c ON c.Source = a.Source AND c.Server = b.Server

-- Create the output
DECLARE	@Output TABLE (Message VARCHAR(8000))

DECLARE	@CurrSource VARCHAR(1),
	@MaxSource VARCHAR(1),
	@msg VARCHAR(8000)

SELECT	@CurrSource = MIN(Source),
	@MaxSource = MAX(Source)
FROM	@Stage

WHILE @CurrSource <= @MaxSource
    BEGIN
	SELECT	@msg = NULL

	SELECT	@msg = ISNULL(@msg + ' and ', '') + d.Server
	FROM	(
		    SELECT DISTINCT TOP 100 PERCENT Server
		    FROM	    @Stage
		    WHERE	    Source = @CurrSource
		    ORDER BY	    Server
		) AS d

	INSERT	@Output
		(
		    Message
		)
	SELECT	@CurrSource + ' are having ' + @msg

	SELECT	@CurrSource = MIN(Source)
	FROM	@Stage
	WHERE	Source > @CurrSource
    END

SELECT	    Message
FROM	    @Output
ORDER BY    Message

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-07 : 13:39:04

[code]

declare @tableA table (server varchar(10))
declare @tableB table (server varchar(10))
declare @tableC table (server varchar(10))
declare @tableD table (server varchar(10))

insert @tableA
select 'Server1' union all
select 'Server2' union all
select 'Server3' union all
select 'Server4'

insert @tableB
select 'Server2' union all
select 'Server3'

insert @tableC
select 'Server3' union all
select 'Server4'

insert @tableD
select 'Server1' union all
select 'Server2' union all
select 'Server3'

select
server,
InTableA = max(s1),
InTableB = max(s2),
InTableC = max(s3),
InTableD = max(s4)
from
(
select server, s1 = 1, s2 = 0, s3 = 0 , s4=0 from @tableA union all
select server, s1 = 0, s2 = 1, s3 = 0 , s4=0 from @tableB union all
select server, s1 = 0, s2 = 0, s3 = 1 , s4=0 from @tableC union all
select server, s1 = 0, s2 = 0, s3 = 0 , s4=1 from @tableD
) a
group by
Server
order by
Server
[/code]

Results:
[code]
server InTableA InTableB InTableC InTableD
---------- ----------- ----------- ----------- -----------
Server1 1 0 0 1
Server2 1 1 0 1
Server3 1 1 1 1
Server4 1 0 1 0

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources