Solved

Updating SQL Tables with store procedures

Posted on 2007-11-27
7
204 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now