Reputation: 15
I have two tables with students (using Microsoft SQL Server 2008 R2 Standard edition). I Need to write a procedure, so that if 1st table has new students, the second one gets updated as well
this doesnt work and I dont know why:
CREATE PROCEDURE [dbo].[CHECK_NEW]
AS
begin transaction
declare @tempId int
declare @tempName varchar
DECLARE c1 CURSOR FOR
SELECT kId, kName
FROM table1
OPEN c1
FETCH NEXT FROM c1
INTO @tempId, @tempName
WHILE @@FETCH_STATUS = 0
BEGIN
if (SELECT sId FROM table2) NOT IN(@tempId)
insert into table(sId, name) values(@tempId, @tempName)
END
commit
Thanks in advance
Upvotes: 0
Views: 2824
Reputation: 8129
Try like this....
CREATE PROCEDURE [dbo].[CHECK_NEW]
AS
Begin Try
begin transaction
Insert into Table2(ulid,Id,Name)
Select newid() as ulid, Id,name from ( Select Row_Number() over(Partition by a.id order by a.id) as row, a.Id,a.name from Table1 a where
not exists(Select * from Table2b where a.Id=b.Id)) t where row =1
Commit
End Try
Begin Catch
RollBack
End Catch
Upvotes: 1