SQL: Insert new records from 1 table to another

Question

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

Answer 1

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