A test: if in table1, but not table2 then...

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

A test: if in table1, but not table2 then...

Author

Topic

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-14 : 14:50:47

Hi,
I have the following code to transfer from table "ULchangefrom" to table "ULchangeto".

INSERT INTO ULchangeto (UserName, SSN)
SELECT UserName, SSN
FROM ULchangefrom

But can anyone tell me how to code this...
"if ULchangefrom.UserName is in ULchangefrom but not ULchangeto,
then (my current code which is to put the entire row in ULchangefrom into ULchangeto)"???

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 14:55:42

really?

Can you write a query that would show you that

Been a LOT of post recently....is there an exam or project that's upcoming or due?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-14 : 14:57:36

NO i don't go to school

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 15:07:57


IF EXISTS (
SELECT *
  FROM ULchangefrom o
WHERE NOT EXISTS (
SELECT * 
  FROM ULchangeto i 
 WHERE o.UserName = i.UserName 
   AND o.SSN = i.SSN )
)

INSERT INTO ULchangeto (UserName, SSN)
SELECT UserName, SSN
FROM ULchangefrom
WHERE NOT EXISTS (
SELECT * 
  FROM ULchangeto i 
 WHERE o.UserName = i.UserName 
   AND o.SSN = i.SSN )

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 15:09:24

You can use "where not exists()"

No, you're never too old to Yak'n'Roll if you're too young to die.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-14 : 15:10:20

Just tested on the server....it works!!!!

Thanks much!

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 15:22:39

quote:
Originally posted by JoshBrigade

Just tested on the server....it works!!!!

Thanks much!

but of course...you must be new here

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 15:43:12

Brett,
I have my doubts.
The insert statement itself is without any condition.
So if there is only ONE record fitting to the IF statement then all other records get inserted too.

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 15:46:07

quote:
Originally posted by webfred

Brett,
I have my doubts.
The insert statement itself is without any condition.
So if there is only ONE record fitting to the IF statement then all other records get inserted too.

No, you're never too old to Yak'n'Roll if you're too young to die.

I like Germans...they are always so precise

You can actually now remove the IF, If you want

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 15:48:27

but of course...I would remove the if. No need to do it double

No, you're never too old to Yak'n'Roll if you're too young to die.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-14 : 16:30:54

You are right in one sence,

It will terminate if even one line is the same giving ...

"Server: Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'aaaaaULchangeto1_PK'. Cannot insert duplicate key in object 'ULchangeto'.
The statement has been terminated."

My file will have some that are in there some that aren't

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-14 : 16:33:44

but, let me ask you all this........what i REALLy want to do is to "if table 1 is in table 2, then UPDATE the row, if not then INSERT the row. Could you all point me in the right direction to do that? That would save the error i got and kill 2 scripts with 1 shot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-14 : 16:49:14

[code]
-- First step is always to update the existing

-- But be aware: it makes no sense to update the fields
-- that are used to join. i.e. UserName and SSN in this example
update ut
set col1 = uf.col1,
col2 = uf.col2,
... some more columns
...
...
from ULchangeto as ut
join ULchangefrom as uf
on uf.UserName = ut.UserName AND
uf.SSN = ut.SSN

-- Second step is to insert the new records
INSERT INTO ULchangeto (UserName, SSN)
SELECT UserName, SSN
FROM ULchangefrom
WHERE NOT EXISTS (
SELECT *
FROM ULchangeto i
WHERE o.UserName = i.UserName
AND o.SSN = i.SSN )
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 18:00:53

update the key to itself?

I'd rather have a hefeweizen

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-15 : 07:35:56

Thanks i will try that this morning.

The 2 fields are just a test so i know i'm getting the process right, the only real fields i'd update are the 40+ fields next to them that i was just too lazy to type test into into.

You are a life saver!

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-15 : 08:38:51

confirmed! it works!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-15 : 09:37:27

cool!

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-15 : 12:57:45

I save code generators to a utility folder so they I don't have to type them

Here's an example




SET NOCOUNT ON
GO

SELECT '-- ' + CONVERT(varchar(25), GetDate(), 101) + ': Generated INSERT INTO...SELECT '  AS [-- Comments]

DECLARE @TB1 varchar(256), @TB2 varchar(256) 

SELECT @TB1 = 'LNAME', @TB2 = 'LNAME' 

SELECT SQL AS [-- SQL] FROM (
	   SELECT 'INSERT INTO ' + @TB1 + '( ' + CHAR(13)+CHAR(10) + REPLICATE(' ',12) +COLUMN_NAME AS SQL
	, 1 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION = 1
		  AND TABLE_NAME = @TB1
	UNION ALL
	   SELECT + REPLICATE(' ',10) + ', ' + COLUMN_NAME AS SQL
	, 2 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION <> 1
		  AND TABLE_NAME = @TB1	UNION ALL
	   SELECT ')' AS SQL
	, 3 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION = 1
		  AND TABLE_NAME = @TB1
	UNION ALL
	   SELECT '     SELECT ' + @TB2 + '( ' + CHAR(13)+CHAR(10) + REPLICATE(' ',12) +COLUMN_NAME AS SQL
	, 4 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION = 1
		  AND TABLE_NAME = @TB2
	UNION ALL
	   SELECT + REPLICATE(' ',10) + ', ' + COLUMN_NAME AS SQL
	, 5 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION <> 1
		  AND TABLE_NAME = @TB2
	UNION ALL
	   SELECT '       FROM ' + @TB2 AS SQL
	, 6 AS SQLORDER, ORDINAL_POSITION
		 FROM INFORMATION_SCHEMA.Columns
		WHERE ORDINAL_POSITION = 1
		  AND TABLE_NAME = @TB2
) AS XXX
ORDER BY  SQLORDER, ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources