Solved

Stored Procedure Help in SQL

Posted on 2014-01-14
3
138 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
[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 25

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 Sever Import/export problem 7 52
Return Rows as per Quantity of Columns Value In SQL 6 29
SQL Instance service gone? 5 39
MS SQL Server Management Studio R2 4 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

730 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