Solved

Stored Procedure Help in SQL

Posted on 2014-01-14
3
133 Views
Last Modified: 2014-02-20
I need help with a stored procedure that handle insert/update in a SQL table. SQL table on server A and converted data on server B (linked Server).

I have provided columns from both tables and attached a screen shot.



-- Stored procedure to insert/update a table from a linked server.

------- Table One columns in server A

Insert  tablename
            ID
            FormID,
            StateProvinceCode,
            StatusCodeID,
            EffectiveDate,
            ExpirationDate,
            ApprovedBy,
            ApprovalDateTime


---Table two columns coming from a linked server B

Select
      cc
      state
      efdt
      expdt
      crby
      crdt
      mtby
      mtdt
      exc
      cnrownumber
      cnxSyncDate ---  (optional column)
 
-----  Note
cc column should be inserted/update ---->  StatusCodeID
state column should be inserted/update  --->  StateProvinceCode
cnrownumber column should be  inserted/update  ---->  ID

FormID numbers to stay the same.
Screen-shot-of-the-result-sets-f.docx
0
Comment
Question by:Favorable
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39780917
You can use MERGE for this:
    MERGE tablename AS target
    USING (SELECT Select
      cc,
      state,
      cnrownumber FROM LinkedServer.DbName.TableName) AS source 
(StatusCodeID, StateProvinceCode, ID)
    ON (target.ID = source.ID)
    WHEN MATCHED THEN 
        UPDATE SET StatusCodeID = source.StatusCodeID, 
              StateProvinceCode = source.StateProvinceCode
	WHEN NOT MATCHED THEN	
	    INSERT (StatusCodeID, StateProvinceCode, ID)
	    VALUES (source.StatusCodeID, source.StateProvinceCode, source.ID)

Open in new window

It is not clear where FormID comes from, and what is default value when inserting new rows
0
 

Author Comment

by:Favorable
ID: 39780934
Formid is a default number already assign, so we will ignore that column.
0
 

Author Closing Comment

by:Favorable
ID: 39873581
Thank you so much
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 36
SQL Replication question 9 43
SQL Insert parts by customer 12 34
Microsoft Access Write errors seem to be caused by bit fields 4 36
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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