Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

inserting horizontal records from table1 as vertical records in table2 Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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
Post #1473503
Posted Monday, July 15, 2013 1:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:06 AM
Points: 2,390, Visits: 2,925
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’! **
Post #1473508
Posted Monday, July 15, 2013 2:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:38 AM
Points: 1,678, Visits: 19,553
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)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1473521
Posted Monday, July 15, 2013 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 5,167, Visits: 12,017
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 ...



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1473532
Posted Monday, July 15, 2013 3:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:06 AM
Points: 2,390, Visits: 2,925
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1473534
Posted Monday, July 15, 2013 5:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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
Post #1473593
Posted Monday, July 15, 2013 5:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:06 AM
Points: 2,390, Visits: 2,925
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’! **
Post #1473598
Posted Monday, July 15, 2013 5:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
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
Post #1473599
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse