Reading Table to see if data exists

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

Reading Table to see if data exists

Author

Topic

mbageant
Starting Member

2 Posts

Posted - 2009-03-20 : 10:41:17

Hi,

I need to insert a new record into a MS SQL table if the data on the record does not already exist in the table for the student ID.

I have a file of input data that has one record for each student. On that one record are several fields - ex: fieldA fieldB fieldC

I need to insert a separate record for the student for each of those 3 fields on the input file - however - I need to make sure that the student does not already have a record in the SQL table with that value before adding it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:46:22

put the records from text file into a staging table using OPENROWSET or Export Import task . the query should be like
SELECT FieldA ..
UNION ALL
SELECT FieldB...

then use below


INSERT INTO Student
SELECT ID,Field
FROM Stage stg
LEFT JOIN Student s
ON s.ID=stg.ID
AND s.Field=stg.Field
WHERE s.ID IS NULL

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-20 : 10:52:32

INSERT INTO TestTable (StudentID, fieldA fieldB fieldC)
SELECT StudentID, fieldA fieldB fieldC
FROM ......
--I'm not sure where the input data is - is it another table, a text files ...?
WHERE StudentID not in (Select StudentID from TestTable)

mbageant
Starting Member

2 Posts

Posted - 2009-03-20 : 11:26:35

Thanks bunches!!! The input data is in another table.

darkdusky - it worked! Thanks.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-20 : 11:36:25

welcome - you should also try the join method visakh showed and use the fastest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources