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: Tuesday, December 23, 2014 6:08 AM
Points: 2,482, Visits: 3,028
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: Yesterday @ 5:56 AM
Points: 1,683, Visits: 19,617
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: Today @ 5:28 AM
Points: 5,317, Visits: 12,362
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1473532
Posted Monday, July 15, 2013 3:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 6:08 AM
Points: 2,482, Visits: 3,028
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: Tuesday, December 23, 2014 6:08 AM
Points: 2,482, Visits: 3,028
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