inserting horizontal records from table1 as vertical records in table2

Question

Post reply

inserting horizontal records from table1 as vertical records in table2

ssurekha2000

SSCommitted

Points: 1860
More actions
July 15, 2013 at 1:26 am

#279909

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

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

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’! **

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

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’! **

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 5

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

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’! **

ssurekha2000 SSCommitted Points: 1860 More actions · Answer 7

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