Insert/Update SP


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Insert/Update SP

  1. #1
    Join Date
    Nov 2006
    Posts
    29

    Question Insert/Update SP

    Background:
    I'm a beginner developer learning when to use the app and when to use the DB to execute what I need. I have two tables in separate databases with employee data. I need to set my asp.net application variable to today so a sp would run based on that.

    Question:
    The sp needs to check for empid in Table B. If exists update based on info in Table A if not found insert from Table A.

    I have this update statement

    Code:
    UPDATE  dbo.employee 
    SET full_name = t.full_name, cost_center = t.cdcstcntr,
        cost_center_desc=t.desccstcntr, job_title=t.job_title,
        supervisor=t.supervisor_name
    FROM dbo.v_WQTimeEmployees as t RIGHT OUTER JOIN 
        dbo.employee ON t.empUserID = dbo.employee.NTUserID
    WHERE t.empUserID = dbo.employee.NTUserID 
        AND (dbo.employee.manual IS NULL or dbo.employee.manual=0)
    I just don't know how to say in SQL, if empuserid found update if not insert.

    Thanks for your assistance.

  2. #2
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    Code:
    UPDATE  dbo.employee 
    SET full_name = t.full_name, cost_center = t.cdcstcntr,
        cost_center_desc=t.desccstcntr, job_title=t.job_title,
        supervisor=t.supervisor_name
    FROM dbo.employee e
    JOIN  dbo.v_WQTimeEmployees as t 
     ON t.empUserID = e.NTUserID
    
    INSERT INTO dbo.employee (NTUserID, full_name, cost_center, cost_center_desc, job_title, supervisor)
    SELECT t.empUserID, t.full_name, t.cdcstcntr,
        t.desccstcntr, t.job_title,
        t.supervisor_name
    FROM dbo.v_WQTimeEmployees as t 
    WHERE NOT EXISTS (SELECT * 
                                 FROM dbo.Employee e 
                                 WHERE e.NTUserID = t.empUserID)
    If you hit a brick wall, you didn't jump high enough!

Similar Threads

  1. insert/update a null values from VB syntax
    By pungky in forum VB Classic
    Replies: 3
    Last Post: 07-27-2005, 01:07 AM
  2. insert/update records
    By Barry Dellecese in forum Database
    Replies: 0
    Last Post: 08-05-2002, 01:23 PM
  3. insert/update records from Unix machine
    By Barry Dellecese in forum oracle.tools
    Replies: 0
    Last Post: 08-05-2002, 01:21 PM
  4. Problem with triggers
    By Miguel in forum Database
    Replies: 0
    Last Post: 06-23-2000, 03:24 PM
  5. Triggers y recursive
    By Miguel in forum Database
    Replies: 0
    Last Post: 06-22-2000, 10:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center