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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Update table with new records
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfslater98
Starting Member

7 Posts

Posted - 05/13/2004 :  08:39:34  Show Profile
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

USA
552 Posts

Posted - 05/13/2004 :  08:45:32  Show Profile
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)
Go to Top of Page

jfslater98
Starting Member

7 Posts

Posted - 05/13/2004 :  09:38:10  Show Profile
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
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 05/13/2004 :  11:38:33  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000