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
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 490 total points
ID: 21770556
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
ID: 21770757
Thanks for this, I'll try this out tomorrow and get back to you :)
0
 

Author Closing Comment

by:jonojones78
ID: 31466596
Excellent, this was bang on. Thank you :)
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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