Solved

Help with update or append statement

Posted on 2013-01-14
4
265 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 47

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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