Solved

sql server update table from multiple insert queries without duplication

Posted on 2015-01-19
3
114 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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

17 Experts available now in Live!

Get 1:1 Help Now