?
Solved

Updating SQL Tables with store procedures

Posted on 2007-11-27
7
Medium Priority
?
212 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 1500 total points
ID: 20360944
do you have access to the SQL Query analyser?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

777 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