Post reply

cursor appropriate?

  • November 23, 2010 at 4:51 am

    #1254334

    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), col3 varchar(30));

    insert into #table1 values (10,'martin','teacher');

    insert into #table1 values (20,'tom','trainer');

    insert into #table1 values (30,'schrof','student');

    create table #table2(t_col1 int identity(1,1), t_col2 VARCHAR(30), [table1_col3] varchar(30));

    create table #table3 (f_col1 int identity(1,1), f_col2 INT, f_col3 VARCHAR(30), f_col4 VARCHAR(30), [table1_col3] varchar(30));

    --- here in table3.f_col4 i need to store the value of table1.col3

    --- will output clause work in this scenario?

    --- You cannot add random columns from the source table to the OUTPUT column list,

    --- but you could add a column for it to #Table2 as follows

    --- You could of course remove it later.

    insert into #table2 (t_col2, [table1_col3])

    output inserted.t_col1, inserted.t_col2, inserted.[table1_col3]

    into #table3 (f_col2, f_col3, [table1_col3])

    select col2, col3 from #table1 t;

    SELECT * FROM #table2

    SELECT * FROM #table3

    “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

  • November 23, 2010 at 5:07 am

    #1254345

    but i have 20 columns in table1...Should i add 20 columns in table2 too?

  • November 23, 2010 at 5:12 am

    #1254348

    MonsterRocks (11/23/2010)

    but i have 20 columns in table1...Should i add 20 columns in table2 too?

    Surely just the keys (primary keys) would be sufficient?

    “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

  • November 23, 2010 at 5:17 am

    #1254353

    ok thanks a lot chris....

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply