Solved

Updating SQL Tables with store procedures

Posted on 2007-11-27
7
207 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

789 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