• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

sql server update table from multiple insert queries without duplication

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
esak2000
Asked:
esak2000
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How they query the data and where the data comes from?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
esak2000Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now