Update table with new records - 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

Old Forums

CLOSED - General SQL Server

Update table with new records

Author

Topic

jfslater98
Starting Member

7 Posts

Posted - 2004-05-13 : 08:39:34

Hello,

Part of my cheesy single sign on solution is to import data from one MS-SQl 2000 DB to another, using a holding table in the target DB. I have that running successfully in DTS. Here are the fields I am importing:

User_id, user_name, login, pass, email

Now the source DB gets new users on a daily basis and I want to import just the new users from the "sa_IDs" holding table to the "users" table. So I this handy bit of code http://www.sqlteam.com/item.asp?ItemID=277, but my implementation is not working very well:

INSERT users (user_id, user_name, login, pass, email)
SELECT user_id, user_name, login, pass, email
FROM sa_IDs
WHERE NOT exists (select * from users)

I imagine my "not exists" needs some help, but my experiments with it produce no new rows in "users", even though I have entered data into the "sa_IDs" table. I hope that I have properly explained things, let me know if you need more info. Thank you.

John

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-13 : 08:45:32

I guessed at the "JOIN" field

INSERT users (user_id, user_name, login, pass, email)
SELECT user_id, user_name, login, pass, email
FROM sa_IDs a
WHERE NOT exists (select 1 from users b where a.user_id = b.user_id)

jfslater98
Starting Member

7 Posts

Posted - 2004-05-13 : 09:38:10

Bingo! Thanks immensely! One final question: is there a good place for me to read up on this? I didn't entirely understand the new query you wrote. The problem I have is with the "1" in the statement:

.. exists (select 1 from ..

But I really appreciate the help you've given me, thanks!

John

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-05-13 : 11:38:33

quote:
Originally posted by jfslater98

Bingo! Thanks immensely! One final question: is there a good place for me to read up on this?
John

To read more on T-SQL refer
a)Advanced Transact-SQL for SQL Server 2000 (2nd Pri by Ben-Gan, Itzik)
b) Gurus guide to T-SQL

------------------------
I think, therefore I am

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources