Solved

Help with update or append statement

Posted on 2013-01-14
4
262 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:Gary Samuels
  • 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:Gary Samuels
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 46

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:Gary Samuels
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

18 Experts available now in Live!

Get 1:1 Help Now