Solved

How can I insert a row to a table if the id isn't already present in the destination copy of the table?

Posted on 2008-06-12
3
168 Views
Last Modified: 2010-04-21
I have 2 tables.

One table is a copy of the other with two extra fields, an internal id and a boolean field.

We'll call the TableA and TableB.

TableA Fields
A_Id
A_name
A_username


TableB Fields
B_ID
B_IdFromA
B_nameFromA
B_usernameFromA
B_AlreadyLinked


I need to run a stored procedure each night which copies rows from TableA into TableB where the values aren't already present (the way to know if it's present or not is if the id is already there, where A_Id=B_IdFromA). I guess an INSERT is needed.

The stored procedure also needs to do an UPDATE, where it updates the values B_nameFromA and B_usernameFromA in TableB with the values A_name and A_username respectively - WHEN the id is already there. This is to leave the B_AlreadyLinked value as is set by the governing system.

In layman's terms, this is to copy users from a system into another database when they are new and to just update the name and username if they are already there.

Any help appreaciated.

Jono


0
Comment
Question by:jonojones78
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 490 total points
Comment Utility
this should do it..you'll need to swap in your real field names

insert into tableb(fieldname)
select a.fieldname
from tablea a
left join tableb on a.id = b.id
where b.fieldname is null

update b
set name1 = a.name1, name2 = a.name2
from tablea a
join tableb b on a.id = b.id
0
 

Author Comment

by:jonojones78
Comment Utility
Thanks for this, I'll try this out tomorrow and get back to you :)
0
 

Author Closing Comment

by:jonojones78
Comment Utility
Excellent, this was bang on. Thank you :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now