Re: Regarding rownum

Hi raja, do you like my idea? fill a temp table with rowids of your table1, insert into V1 records from table1 that have been linked with even rowids in temp table, delete even rowid record from temp table, insert into V2 records from table1 linked with remaining records intemp table drop temp table its all pure sql, no host variables :D and that's all folks ! select rowid as nrec from table1 into temp temptable; insert into table1_V1 select * from table1 where rowid in (select nrec from temptable where mod(rowid,2)=0 ); delete from temptable where mod(rowid,2)=0; insert into table1_V1 select * from table1 where rowid in (select nrec from temptable ); drop table temptable; On Sep 16, 9: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.