Solved

Updating SQL Tables with store procedures

Posted on 2007-11-27
7
210 Views
Last Modified: 2010-04-21
I have another question on EE that is related to this one, but this is more specific.

I am working on a method of updating a live data table on my SQL server with outside data.
It has been suggested that I import my data into a temporary SQL Table and then use a stored procedure to update the "live" table with data from the "temporary" table.  

This will make it faster and I will have less issues than if I just deleted the contents of the "live" table and upated it with the "temporary". (thats what I do now)

I am not very familiar with SQL Server.
How can I create such a stored procedure?

The two tables are identical.
I want a procedure that will update the live table with data from the temp table.
I would imagine it would be good if only new data was added and only records that had something different were updated, but I am not sure if that is possible. (there are 35 'columns')

A sample or pointer to a complete sample woulkd be most helpful.
Assume I am a SQL dummy.

0
Comment
Question by:EGormly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:RDWaibel
ID: 20360433
There are many ways to do this.  I do something very simular with mine when it comes to the Federal DNC databases.  
When you say "The two tables are identical." do you mean the inital state of the data as well OR just the columns (basically the table definition)
0
 

Author Comment

by:EGormly
ID: 20360844
Sorry.. the tables are identical in state and definition.

I will be moving data from one to another to avoid using a webserver asp script to do it. from actual databases.


The following works in my SQL Server Management Studio Script window:

insert into INVENTORY
select * from tempINVENTORY


But I do not know how to make it a procedure that I can fire automatically.


 



0
 
LVL 14

Accepted Solution

by:
RDWaibel earned 500 total points
ID: 20360944
do you have access to the SQL Query analyser?
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:EGormly
ID: 20365734
RDWaibel, I am not sure where you are going with that, but I found what I needed online with just a few more hours of looking.

something so simple should be readily available.
Maybe I'll compile a list of supremely simple things to do in SQL.

For anyone who needs the answer to this
The answer to my question is:


In SQL Management Studio
Double click "Databases"
Click the [+] next to the Database you want to have a stored procedure in.
Click the [+] next to the Programmability
Right click the Stored Procedures folder and choose > New Stored Procedure
Click the Query Menu (Top of Application)
Choose Specify Values for Template Parameters
Assign a "ProcedureName, this will be the name use use to execute the stored procedure.
The rest doesn't really matter, you can fill it all out of not.

Click in the newly created procedure code window and scroll down to the " -- Insert statements for procedure here" section.  Replace any select statement you see with whatever you want thr stored procedure to be.

Click Qery > Parse
if it is successful (lower statud window under procedure code)
Click Query > Execute
if it is successful
Right click the Stored procedure Folder and choose Refresh.
If all is well the new stored procedure will be in the list.


code that I needed for a procedure:

insert into Inventory
  select * from tempInventory
0
 

Author Comment

by:EGormly
ID: 20365768
Here is some code to fire a stored procedure from an asp page:
UpdateTablesInentory is the name I gave to the procedure.


set conn = CreateObject("ADODB.Connection")
conn.open "Provider=SQLNCLI;Server=YOUSERVER\YOURSERVER;Database=test;Uid=User;Pwd=password;"
    set rs = conn.execute("EXEC dbo.UpdateTablesInentory")
conn.close
 set conn = nothing


NOTE: If you run this outside of SQL Server Studio you will need to give permission to the Stored Procedure just like you do to any other accessible database.
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 20366035
Very well.  Great Job!
0
 

Author Closing Comment

by:EGormly
ID: 31411268
Although this is not the actual solution,  I screwed up on EE and asked for a different question to be closed by mistake.


I will not be doing that again...

You may not have answered the question but in some way you pointed me in the correct direction.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

707 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