Copying values from one table to another table if it does not already exists

Question

I have four tables named Task, ProjectTeamMemeber, Hierarchy and Assignment.
Each Task is related to a Project through a Heirarchy and each Project has Team members which can be seen through ProjectTeamMember.

select TH.TaskId As TaskID, P.PersonId As PersonID from Task T Inner join Hierarchy TH on T.Id = TH.TaskId Inner join ProjectTeamMemeber PTM on TH.ProjectId = P.TaskId where T.name = 'work'

The above query returns all the person allocated to a certain project where the Task name is 'work'

TaskId PersonId 1347 50 1350 50 3377 50 3403 598 3411 1450 3411 1454 3411 1472

And I have another table Assigment from which i can see the Id of persons which are assigned to a particular task.

select T.Id, A.PersonId from Task T inner join Assignment A ON T.Id = A.TaskId where T.name = 'work'

The above query return all the persons allocated to Task called 'work'.

TaskId PersonId 1347 50 1350 50 3411 1472

Now, what i have to achieve here is to allocate all the persons to a task with name 'work' which are allocated to projects which have a task name 'work'. Putting it simply i have to write some sql statements after which my second query above writtens the same result as my first query.

So far, I have tried to do this with cusrsors but it would not work. Below is the code for that

DECLARE @TaskId int DECLARE @PersonId int DECLARE @Id int DECLARE db_cursor CURSOR FOR select TH.TaskId As TaskID, P.PersonId As PersonID from Task T Inner join Hierarchy TH on T.Id = TH.TaskId Inner join ProjectTeamMemeber PTM on TH.ProjectId = P.TaskId where T.name = 'work' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @TaskId, @PersonId BEGIN If not exists ( select 1 from Assignment where TaskId=@TaskId and PersonId=@PersonId) BEGIN EXEC GetUniqueIdentifier @Id OUTPUT; Insert INTO Assignment (Id, TaskId, PersonId) values (@Id, @TaskId, @PersonId) END END CLOSE db_cursor DEALLOCATE db_cursor

(NOTE : I have to use the statement EXEC GetUniqueIdentifier @Id OUTPUT; to generate the Id. I know there maybe different and more reliable ways to generate Ids but due to some limitations i cannot change how the Id is being generated.)

EDIT :

Based on LAMAK answer i ran this

DECLARE @ids int INSERT INTO Assignment (Id, TaskId, PersonId) EXEC GetUniqueIdentifier @ids OUTPUT; SELECT @ids, TH.TaskId As TaskID, P.PersonId As PersonID, FROM Task T INNER JOIN Hierarchy TH on T.Id = TH.TaskId INNER JOIN ProjectTeamMember P on TH.ProjectId = P.TaskId WHERE T.name = 'Customer work' AND NOT EXISTS( SELECT 1 FROM Assignment WHERE TaskID = TH.TaskId AND PersonId = P.PersonId)

Lamak · Accepted Answer · 2013-06-04 19:22:03Z

You don't need a CURSOR for this, you can use a set based solution:

INSERT INTO Assignment (Id, TaskId, PersonId) SELECT NEWID(), TH.TaskId As TaskID, P.PersonId As PersonID FROM Task T INNER JOIN Hierarchy TH on T.Id = TH.TaskId INNER JOIN ProjectTeamMemeber PTM on TH.ProjectId = P.TaskId WHERE T.name = 'work' AND NOT EXISTS( SELECT 1 FROM Assignment WHERE TaskID = T.TaskId AND PersonId = P.PersonId)

About the NEWID(), do you really need to check for collitions?, I think that is safe to assume that NEWID() will be unique if it's run on the same machine. And even on different servers, the probability for duplicated values is abysmally small.

UPDATED

Ok, so, you don't need an UNIQUEIDENTIFIER and you have to use the GetUniqueIdentifier sp (badly name also, since UNIQUEIDENTIFIER is a datatype, wich can bring confusion). Then I'm affraid that you'll need to use a CURSOR. For the most part, your cursor seems fine, the only visible change being that you are not fetching for the next value of the cursor:

DECLARE @TaskId int DECLARE @PersonId int DECLARE @Id int DECLARE db_cursor CURSOR FOR select TH.TaskId As TaskID, P.PersonId As PersonID from Task T Inner join Hierarchy TH on T.Id = TH.TaskId Inner join ProjectTeamMemeber PTM on TH.ProjectId = P.TaskId where T.name = 'work' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @TaskId, @PersonId BEGIN If not exists (select 1 from Assignment where TaskId=@TaskId and PersonId=@PersonId) BEGIN EXEC GetUniqueIdentifier @Id OUTPUT; Insert INTO Assignment (Id, TaskId, PersonId) values (@Id, @TaskId, @PersonId) END FETCH NEXT FROM db_cursor INTO @TaskId, @PersonId END CLOSE db_cursor DEALLOCATE db_cursor

you migth be right about the newid but i have to do it the way as described above using the GetUniqueIdentifier procedure
@LivingThing sigh what does the GetUniqueIdentifier returns?, can you post the code for it?
@LivingThing I really thought that your stored procedure was meant to return an UNIQUEIDENTIFIER. This way it doesn't make much sense, why don't you use an IDENTITY column for this?
I have no control over over the procedure yet (i am a junior developer) and cannot avoid this procedure to generate unique identifiers. So, i have to use it the way it is and have to find a solution and live with that :/

Collectives™ on Stack Overflow

Copying values from one table to another table if it does not already exists

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related