• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Help with update or append statement

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
Gary Samuels
Asked:
Gary Samuels
  • 2
2 Solutions
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
Gary SamuelsPlant ManagerAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Gary SamuelsPlant ManagerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now