Checking if information exists before copy

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

Checking if information exists before copy

Author

Topic

Ernie57
Starting Member

27 Posts

Posted - 2007-04-01 : 18:51:05

I am getting an error between the IF THEN Else statement. The copy works after the THEN! I need some help!

Ernie

I have two tables with script in a view. 1. newusers 2. blacklist I want to compare that "PresentEmail, RemoteComputer,Email" thats in newuser table is not in blacklists, and if this is true, then copy from newuser (the record not on blacklist) to blacklist using a where statement for second condition.

IF
(NOT EXISTS
(SELECT *
FROM newusers AS d
WHERE ISNULL(PresentEmail, 'NULL') IS BadEmail))

THEN

Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)

Select NewEmail, RemoteComputerName, PresentEmail

From newusers

Where Account_Active = 'Spammer'

Else
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 19:16:06

[code]
IF (NOT EXISTS
(
SELECT *
FROM newusers AS d
WHERE ISNULL(PresentEmail, 'NULL') LIKE BadEmail
)
)
BEGIN
Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)
Select NewEmail, RemoteComputerName, PresentEmail
From newusers
Where Account_Active = 'Spammer'
END
[/code]

Ernie57
Starting Member

27 Posts

Posted - 2007-04-01 : 19:32:59

I am getting an error message "Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BadEmail'."

Table Blacklist has BadEmail col

Table newusers has PresentEmail col

Ernie

quote:

Originally posted by khtan


IF (NOT EXISTS 
	(
		SELECT * 
		FROM 	newusers AS d 
		WHERE 	ISNULL(PresentEmail, 'NULL') LIKE BadEmail
	)
    )
BEGIN
	Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)
	Select 	NewEmail, RemoteComputerName, PresentEmail
	From 	newusers
	Where 	Account_Active = 'Spammer'
END

I see another problem! The statement above is not going to work the way I want it too! I am really lost!

Ernie57
Starting Member

27 Posts

Posted - 2007-04-01 : 20:31:16

This generates something

SELECT PresentEmail, BadEmail
FROM newusers,blacklist AS d
WHERE ISNULL(PresentEmail, 'NULL') LIKE BadEmail

data

PresentEmail BadEmail

I have two col. of data!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 21:01:49

LIKE '%' + BadEmail +'%'

Ernie57
Starting Member

27 Posts

Posted - 2007-04-01 : 23:14:04

When Blacklist table has nothing stored in the table, then the data copies from newusers to Blacklist when the statement below runs. The problem is if more spammers enter the into new users table, when this script runs again, nothing happens. Also, if only one record copies over, still same problem, no more data copies over! I don't know why yet or what needs to happen in the statement. This NOT EXISTS is causing the problem, but I don't know how to work around it!

Ernie

IF (NOT EXISTS
(
SELECT *
FROM newusers,blacklist AS d
WHERE PresentEmail LIKE BadEmail AND BadIpaddress LIKE RemoteComputerName
)
)
BEGIN
Insert into blacklist (BadNewEmail, BadIpAddress, BadEmail)
Select NewEmail, RemoteComputerName, PresentEmail
From newusers
Where Account_Active = 'Spammer'
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 23:17:02

You better post your table DDL, some sample data with the required result.

Kristen
Test

22859 Posts

Posted - 2007-04-02 : 02:24:00

"This generates something

SELECT PresentEmail, BadEmail
FROM newusers,blacklist AS d
WHERE ISNULL(PresentEmail, 'NULL') LIKE BadEmail"

BadEmail is probably a column in [blacklist], and not [newusers]:


IF (NOT EXISTS 
	(
		SELECT * 
		FROM 	newusers AS d 
		WHERE 	ISNULL(PresentEmail, 'NULL') LIKE BadEmail
	)
    )

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources