Finding no values in a table. - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Finding no values in a table.

Author

Topic

paultervit
Starting Member

10 Posts

Posted - 2004-07-19 : 04:34:23

Hello
I am trying to insert values into a table for every row that does not have a related value in another table i.e. I need to insert a password history row into every row where one does not already exist for each UaserId in the user id table.

Any ideas how this is done.

Cheers

Paul

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-19 : 05:07:35

USE NOT EXISTS.

EG.

CREATE TABLE #Test1(ColID INT, ColStuff VARCHAR(20));
CREATE TABLE #Test2(ColID INT, ColStuff VARCHAR(20));

INSERT INTO #Test1 VALUES(1, 'Test 1')
INSERT INTO #Test1 VALUES(2, 'Test 2')
INSERT INTO #Test1 VALUES(3, 'Test 3')
INSERT INTO #Test1 VALUES(4, 'Test 4')
INSERT INTO #Test1 VALUES(5, 'Test 5')

INSERT INTO #Test2 VALUES(1, 'Test 1')
INSERT INTO #Test2 VALUES(3, 'Test 3')
INSERT INTO #Test2 VALUES(5, 'Test 5')

SELECT * FROM #Test1 ORDER BY 1
SELECT * FROM #Test2 ORDER BY 1

INSERT INTO #Test2
SELECT a.ColID, a.ColStuff
FROM #Test1 a
WHERE NOT EXISTS(SELECT b.ColID FROM #Test2 b WHERE a.ColID = b.ColID)

SELECT * FROM #Test1 ORDER BY 1
SELECT * FROM #Test2 ORDER BY 1

Duane.

paultervit
Starting Member

10 Posts

Posted - 2004-07-19 : 05:25:43

Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources