Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

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

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
jonojones78
Asked:
jonojones78
  • 2
1 Solution
 
chapmandewCommented:
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
 
jonojones78Author Commented:
Thanks for this, I'll try this out tomorrow and get back to you :)
0
 
jonojones78Author Commented:
Excellent, this was bang on. Thank you :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now