Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with update or append statement

Posted on 2013-01-14
4
Medium Priority
?
275 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 1000 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 53

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 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