inserting horizontal records from table1 as vertical records in table2

  • i have table with horizontal records , which i need to insert in another table as vertical records

    example

    FDT1 TODT1 FDT2 TODT2 FDT3 TODT3

    2009-12-14 2009-12-28 2009-12-29 2010-01-13 NULL NULL

    2010-01-14 2010-01-28 2010-01-29 2010-02-13 NULL NULL

    2010-02-14 2010-02-28 2010-03-01 2010-03-13 NULL NULL

    2010-03-14 2010-03-28 2010-03-29 2010-04-13 NULL NULL

    2010-04-14 2010-04-28 2010-04-29 2010-05-13 NULL NULL

    2010-05-14 2010-05-28 2010-05-29 2010-06-13 NULL NULL

    2010-06-14 2010-06-28 2010-06-29 2010-07-13 NULL NULL

    2010-07-14 2010-07-28 2010-07-29 2010-08-13 NULL NULL

    2010-08-14 2010-08-28 2010-08-29 2010-09-13 NULL NULL

    2010-09-14 2010-09-28 2010-09-29 2010-10-13 NULL NULL

    2010-10-14 2010-10-28 2010-10-29 2010-11-13 NULL NULL

    2010-11-14 2010-11-28 2010-11-29 2010-12-13 NULL NULL

    2010-12-14 2010-12-28 2010-12-29 2011-01-13 NULL NULL

    2011-01-14 2011-01-28 2011-01-29 2011-02-13 NULL NULL

    2011-02-14 2011-02-28 2011-03-01 2011-03-13 NULL NULL

    2011-03-14 2011-03-28 2011-03-29 2011-04-13 NULL NULL

    2011-04-14 2011-04-28 2011-04-29 2011-05-13 NULL NULL

    2011-05-14 2011-05-28 2011-05-29 2011-06-13 NULL NULL

    2011-06-14 2011-06-28 2011-06-29 2011-07-13 NULL NULL

    2011-07-14 2011-07-28 2011-07-29 2011-08-13 NULL NULL

    2011-08-14 2011-08-28 2011-08-29 2011-09-13 NULL NULL

    2011-09-14 2011-09-28 2011-09-29 2011-10-13 NULL NULL

    2011-10-14 2011-10-28 2011-10-29 2011-11-13 NULL NULL

    2011-11-14 2011-11-28 2011-11-29 2011-12-13 NULL NULL

    2011-12-14 2011-12-28 2011-12-29 2012-01-13 NULL NULL

    2012-01-14 2012-01-28 2012-01-29 2012-02-13 NULL NULL

    2012-02-14 2012-02-28 2012-03-01 2012-03-13 NULL NULL

    2012-03-14 2012-03-28 2012-03-29 2012-04-13 NULL NULL

    2012-04-14 2012-04-28 2012-04-29 2012-05-13 NULL NULL

    the above records i need to insert in another table ie table2

    as horizontal rows

    table2 has fields

    frmdt todt

    i need the records frm table1 to be inserted as

    frmdt todt

    FDT1 TODT1

    FDT2 TODT2

    FDT1 TODT1

    FDT2 TODT2

    FDT1 TODT1

    FDT2 TODT2

  • INSERT INTO table2

    SELECT FDT1, TODT1 FROM table1

    UNION ALL

    SELECT FDT2, TODT2 FROM table1

    Edit: corrected the code with comma's between the columns

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • INSERT INTO table2(frmdt,todt)

    SELECT ca.frmdt,ca.todt

    FROM table1 t

    CROSS APPLY(SELECT FDT1, TODT1

    UNION ALL

    SELECT FDT2, TODT2

    UNION ALL

    SELECT FDT3, TODT3) ca(frmdt,todt)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • HanShi (7/15/2013)


    INSERT INTO table2

    SELECT FDT1 TODT1 FROM table1

    UNION ALL

    SELECT FDT2 TODT2 FROM table1

    I think you might need to add a comma between the column names ...

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (7/15/2013)


    I think you might need to add a comma between the column names ...

    You're absolutely right!! I have to look better if I copy/paste from a post.:hehe:

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • currently the data is inserted as

    frmdate todate

    2009-12-14 2009-12-28

    2010-01-14 2010-01-28

    2010-02-14 2010-02-28

    instead of

    frmdate todate

    2009-12-14 2009-12-28

    2009-12-29 2010-01-13

    2010-01-14 2010-01-28

    2010-01-29 2010-02-13

    2010-02-14 2010-02-28

    2010-03-01 2010-03-13

    so used cross apply

    its working fine

    but with a issue in other fields which were taken i query

  • Remember, the output is only quaranteed in a specific order if you specify the ORDER BY in the query. How the data is phisically structured is not importand.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • used INSERT INTO table2(frmdt,todt)

    SELECT ca.frmdt,ca.todt

    FROM table1 t

    CROSS APPLY(SELECT FDT1, TODT1

    UNION ALL

    SELECT FDT2, TODT2

    UNION ALL

    SELECT FDT3, TODT3) ca(frmdt,todt)

    the records are inserted as needed

    thanks very for the same

    but a slight issue with other fields that is being inserted

    insert into table2(year,frmdt,todt,No,Consno)

    SELECT t.YEAR,ca.frmdt,ca.todt @No,@Consno

    FROM table1 t

    CROSS APPLY(SELECT FDT1, TODT1

    UNION ALL

    SELECT FDT2, TODT2

    UNION ALL

    SELECT FDT3, TODT3) ca(frmdt,todt)

    @No is a count taken from table3

    if @no is 2

    then the No column from the table2 will have data as

    1

    2

    1

    2

    1

    2

    @Consno is a consecutive no

    i am taking max(Consno)+1

    so each record inserted will have

    1

    2

    3

    4....... consecutive nos

    due to union all same no gets inserted in all records

Viewing 8 posts - 1 through 7 (of 7 total)

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