• MonsterRocks (11/23/2010)


    any idea ?... cursor inevitable?....

    Don't think so. Try this, it's the same as yours with the little errors removed, mostly column types:

    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 VARCHAR(30));

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

    insert into #table2 (t_col2)

    output inserted.t_col1, inserted.t_col2

    into #table3(f_col2,f_col3)

    select col2 from #table1 t;

    SELECT * FROM #table2 -- three rows

    SELECT * FROM #table3 -- three rows. Column1 = identity column of #Table3, Column2 = identity column of #Table2

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden