Solved

Updating SQL Tables with store procedures

Posted on 2007-11-27
7
208 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query error in former passthrough query 2 31
Find unused columns in a table 12 67
online  environment for testing sql queries 5 29
Error in sql query statment. 21 40
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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