Solved

sql server update table from multiple insert queries without duplication

Posted on 2015-01-19
3
115 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 47

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

Suggested Solutions

Title # Comments Views Activity
Attaching Database Failed ? 3 39
SQL Replication question 9 41
SSRS 2013 - Creating a summarized report 19 32
T-SQL:  Collapsing 9 22
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 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