Solved

Help with update or append statement

Posted on 2013-01-14
4
260 Views
Last Modified: 2013-01-16
I would like to use the dbo.hale table to append the Shelby.NACustomN table.

In NACustomN if the record does not exist I need to append a new record:
NAIndex = '23'
,NameCounter = [dbo.hale].[IntegrationID]
,Number = [dbo.hale].[MembershipID]

Then I also need to append a second record:
NAIndex = '24'
,NameCounter = [dbo.hale].[IntegrationID]
,Number = [dbo.hale].[MemberID]

Please see the attached images for the table layouts.
hale.jpg
CustomN.jpg
0
Comment
Question by:garysamuels
  • 2
4 Comments
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 250 total points
ID: 38779133
Where are you getting the "NAIndex" number from? It doesn't look like it's coming from your source table, so I'm a bit confused. Unless you have some kind of filter set up in your query to only pull a certain row from hale, how do you know what data to insert? What if hale has 500 rows in it - which one do you want?

If you want to do an upsert, you can use SQL Server's MERGE function, which allows you to compare two tables and take one action if a match is found (update) and a different action if it's not found (insert or delete) - this might be along the lines of what you're looking for. However, since you're doing single row inserts, why not just check the destination table for the relevant value and make the decision in your T-SQL?
0
 

Author Comment

by:garysamuels
ID: 38780350
Sorry but your talking over my head. I barely know a few simple SQL functions such as SELECT, FROM and WHEN.

The NAIndex numbers were auto generated when the custom MembershipID and MemberID fields were added to a form. So the NAIndex value for the field which holds the MembershipID will always be 23, and the NAIndex value for MemberID will always be 24.

The table hale will end up with thousands of rows. I though it would run the SQL statement once for each row in the hale table. The MERGE function sounds like what I need because it might be possible that the NACustomN table may already contain a duplicate record.

In the hale table the IntegrationID value could be set as a PK, it is unique. It will also match the NameCounter PK in the NACustomN table.

Any help would be greatly appreciated.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 38781992
NAIndex is Identity and TStamp is Timestamp datatype?
I don't know what you really want but the T-SQL statement you should use is something like:
INSERT INTO Shelby.NACustomN (NameCounter, Number)
SELECT IntegrationID, MemberID
FROM dbo.hale
WHERE [YourSearchCriteriaHere]

Open in new window

0
 

Author Comment

by:garysamuels
ID: 38782566
After looking over ryanmccauley's and your comment I realize I need to break the hale table into 2 tables and then use the Insert or Merge.

Thanks for the help
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now