how can I use loop 'for' through a table in SQL to check if a column is 0?

Question

how can I use loop 'for' through a table in SQL to check if a column is 0?

I need to do like this in a stored procedure:

for  each record in  tablex
     if table.column1=0 then
         insert into table1;
     Else
         insert into table2;
 End for;

Answer 1

What you need is called cursor.

Sample for mysql:

DECLARE tmp INT;
DECLARE cur1 CURSOR FOR SELECT column1 FROM `table`;
OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO tmp;

    -- perform your IF here
END LOOP;

CLOSE cur1;

More details: http://dev.mysql.com/doc/refman/5.1/en/cursors.html

The sample for SQL Server is quite the same.

SQL Server details: http://msdn.microsoft.com/en-us/library/ms180169.aspx

Answer 2

It's not clear what language you'll be writing in (MySQL or SQLServer), but the normal way to do this is using DECLARE CURSOR, FETCH, and WHILE.

However, if the task is as simple as you state in your problem, a better, faster, and more SQL-ish solution is probably:

INSERT INTO Table1 (ColList) 
     SELECT ColList 
       FROM SourceTable 
      WHERE Column1 = 0;

INSERT INTO Table2 (ColList) 
     SELECT ColList 
       FROM SourceTable 
      WHERE (Column1 <> 0 OR Column1 IS NULL);

Answer 3

You may be able to do the same thing without a cursor:

INSERT INTO table1 (colA, colB) 
     SELECT valX, valy, 'const' 
       FROM Table 
      WHERE column1 = 0;

then repeat for table 2.

Answer 4

Is there a reason you need to use a loop? Could you not just do

insert into table1
select * from tablex 
where tablex.column1 = 0

insert into table2
select * from tablex 
where tablex.column1 != 0