?
Solved

sql server update table from multiple insert queries without duplication

Posted on 2015-01-19
3
Medium Priority
?
154 Views
Last Modified: 2015-01-19
I have multiple computers that query data to be stored on a central sql server database table.

Each computer can query data that is the same as any of the other computers, but I only want unique data to be stored to the central table.

Each computer first uploads the data to a temp table. A stored procedure is then run to insert only the new data into the main table, it then truncates the temp table so that it doesn't get to be too large.

My problem is that since all the computers are running at the same time the stored procedure can be called at the same time and truncate data that hasn't been inserted yet. Is there a way to lock the stored procedure for each computer as it is called?
0
Comment
Question by:esak2000
3 Comments
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40557741
How they query the data and where the data comes from?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40557760
>Each computer first uploads the data to a temp table.
Why a temp table?  Do others have access to this temp table?

Perhaps instead of a straight Trucate the user's Network ID (workstation ID) should be inserted into this temp table as a column, then replace TRUNCATE with DELETE FROM YourTable WHERE NetworkID = 'their network id'.   The Network ID can be passed to the SP as a parameter to make this easier.
0
 

Author Closing Comment

by:esak2000
ID: 40557809
First a temp table because I want to make sure there is no duplicate data before inserting it into the main table.
Your answer is will work, thank you.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

589 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