Solved

sql server update table from multiple insert queries without duplication

Posted on 2015-01-19
3
124 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 49

Expert Comment

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

Accepted Solution

by:
Jim Horn earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

762 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