Iterating over table rows in MSSQL

Hello all,

I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.

Currently, I would like to do the following (in abstract terms):

declare tmp record
select column1 from tableA into tmp
for each entry from above selection do
insert into tableB values (tmp[column1], 0, 0, 0)

I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:

CREATE FUNCTION dbo.newDay (@mDate datetime)
RETURNS int
AS
BEGIN
DECLARE @id int
DECLARE item_cursor CURSOR FOR
SELECT id FROM tblKitchenCat
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate)
FETCH NEXT FROM item_cursor INTO @id
END
CLOSE item_cursor
DEALLOCATE item_cursor
RETURN 0
END
GO

I get a syntax error next to AS... what is it?

Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.

In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?

Thanks in advance.
Cheers,
Michael

Thank you for your reply. It looks very simple and easy to understand, but when I'm entering this as a user defined function in Enterprise Manager, I get the following error - User Defined Function definition must include name and text. Am I missing something?

Why do you try to create a function?