[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Best way to write an update procedure in Sql Server

Posted on 2012-08-22
6
Medium Priority
?
535 Views
Last Modified: 2012-08-29
I'm an Oracle developer learning Sql Server / SSIS.

Typically, I write update procedures in Oracle, read the d.b. with a cursor, loop through the cursor, then write an update statement. Then send an e-mail with totals, etc.

I'm also in a Sql Server shop that does a lot of SSIS work. I'm familiar with the tool but haven't actually written anything yet.

In my first discussion with an SSIS developer, his first reaction was SSIS might not be the right tool for something like this.

What's the best approach for this (cursor, loop through data, update, e-mail with totals) ?
0
Comment
Question by:Alaska Cowboy
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 38323306
Another example is
- read a flat file
- use info in the flat file to do a lookup, returning another value based on lookup info
- update a d.b. record with the returned lookup value, based on fields in the flat file
- send e-mail with totals, etc.

So it's not really moving data from point A to point B with transformation in the classic sense, but it's reading data and then making an update to a d.b.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38326500
Hi,

My first thought was to consume the flatfile and take into a table that matches it, then do one update rather than use a cursor. Maybe I'm wrong, but on the face of it a cursor doesn't appear necessary.

Regards
  David
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 38326564
David,

ok, that makes sense. But I'm more asking is SSIS the right tool ? If not, what is ?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 500 total points
ID: 38326588
Hi,

SSIS is a good choice to import a flat file to a SQL database table.

Subsequent to that I'd be using a SQL task and write the rest in a stored procedure called by the SQL task.

HTH
  David
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 38326805
David,

ok, makes sense. I can separate out the file reading from the stored procedure, just trying to visualize the pieces before diving in.

thanks.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 38345934
David, sorry for the delay, closing now.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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