Thanks a lot for valuable ideas.. but what i am trying to do is

just like the following code...

from the select statement few values wil be inserted in one table and based on its result other values will be inserted in some other

if object_id('tempdb..#table1') IS NOT NULL DROP TABLE #table1;

if object_id('tempdb..#table2') IS NOT NULL DROP TABLE #table2;

if object_id('tempdb..#table3') IS NOT NULL DROP TABLE #table3;

create table #table1 (col1 int,col2 varchar(30));

insert into #table1 values (1,'martin');

insert into #table1 values (2,'tom');

insert into #table1 values (3,'schrof');

create table #table2(t_col1 int identity(1,1),t_col2 int);

create table #table3 (f_col1 int identity(1,1),f_col2 varchar(30),f_col3 int);

insert into #table2 (t_col2) output inserted.t_col1, t.col2 into #table3(f_col2,f_col3)

select col1,col2 from #table1 t;

quite difficult :sick: