Regarding rownum

Hi, For functionality purpose, i am splitting a table into two. eg: for table1, i am splitting it as table1_v1 and table2_v2. i have taken the count of table1 and i am splitting the rows as count(1)/2. Now, i have to insert the splitted rows into table1_v1 and table2_v2. Any idea, how to do this, without using rownum ? I am asking this question, because, i did as follows : select rownum=identity(10), t1.* into #TMP_TABLE from table1 t1 select @count1 = @@rowcount select @tmp_cnt = (@count1/2) + 1 insert into table1_V1 select * from #TMP_TABLE where rownum <@tmp_cnt insert into table_V2 select * from #TMP_TABLE where rownum >=@tmp_cnt But the problem here is, while compiling, i got the below errors : Server Message: Number 213, Severity 16 Insert error: column name or number of supplied values does not match table definition. Server Message: Number 213, Severity 16 Insert error: column name or number of supplied values does not match table definition. Because the table1_V1, table1_v2 are similar to structure of table1, which doesnt have rownum as column. so this mismatch in columns. So any idea, how to do this, without using rownum ? Please help, any suggestion/ideas. Thanks in Advance. With Regards, Raja.

On Sep 16, 7:15 am, raja <dextersu...@gmail.com> wrote: > Hi, > > For functionality purpose, i am splitting a table into two. > eg: for table1, i am splitting it as table1_v1 and table2_v2. > i have taken the count of table1 and i am splitting the rows as > count(1)/2. > Now, i have to insert the splitted rows into table1_v1 and table2_v2. > Any idea, how to do this, without using rownum ? > > I am asking this question, because, i did as follows : > > select rownum=identity(10), t1.* into #TMP_TABLE from table1 t1 > >         select @count1 = @@rowcount > >         select @tmp_cnt  = (@count1/2) + 1 > >         insert into table1_V1 select * from #TMP_TABLE where rownum <> @tmp_cnt >         insert into table_V2 select * from  #TMP_TABLE where rownum >=> @tmp_cnt > > But the problem here is, while compiling, i got the below errors : > > Server Message:  Number  213, Severity  16 > Insert error: column name or number of supplied values does not match > table definition. > Server Message:  Number  213, Severity  16 > Insert error: column name or number of supplied values does not match > table definition. > > Because the table1_V1, table1_v2 are similar to structure of table1, > which doesnt have rownum as column. so this mismatch in columns. > > So any idea, how to do this, without using rownum ? > > Please help, any suggestion/ideas. > > Thanks in Advance. > > With Regards, > Raja. Why use row num? It sounds like you're taking table A and are splitting the table in to two tables on a random basis where every other row is put in either B or C. The end results is that tables B&C contain the same number of rows +-1 row. You don't say what language you're using, but the idea would be to use a cursor and a counter or a boolean to insert in to either table. (A boolean could be a small int set to 0 to indicate row to be inserted is inserted in to table B and 1 to insert in to table C. And then reset the counter to the other value after the insert.) You can do this in Python, Java, ESQL/C, 4GL without any problems. If you want to use SQL only I guess you could insert the data in to a temp table creating a rownum column and then select the rows based on odd or even row num. HTH -G

raja wrote: > Hi, > > For functionality purpose, i am splitting a table into two. > eg: for table1, i am splitting it as table1_v1 and table2_v2. > i have taken the count of table1 and i am splitting the rows as > count(1)/2. > Now, i have to insert the splitted rows into table1_v1 and table2_v2. > Any idea, how to do this, without using rownum ? > > I am asking this question, because, i did as follows : > > select rownum=identity(10), t1.* into #TMP_TABLE from table1 t1 > > select @count1 = @@rowcount > > select @tmp_cnt = (@count1/2) + 1 > > insert into table1_V1 select * from #TMP_TABLE where rownum <> @tmp_cnt > insert into table_V2 select * from #TMP_TABLE where rownum >=> @tmp_cnt > > But the problem here is, while compiling, i got the below errors : > > Server Message: Number 213, Severity 16 > Insert error: column name or number of supplied values does not match > table definition. > Server Message: Number 213, Severity 16 > Insert error: column name or number of supplied values does not match > table definition. > > Because the table1_V1, table1_v2 are similar to structure of table1, > which doesnt have rownum as column. so this mismatch in columns. > > So any idea, how to do this, without using rownum ? > > Please help, any suggestion/ideas. > > Thanks in Advance. > > With Regards, > Raja. You appear to just splitting a table. Just fragment the table by Round Robin, and detach one of the fragments, the end result is two tables, same structure half the rows in each Cheers Paul