Click here to Skip to main content
15,891,778 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'am doing a data migration from source database to target database using dblink. Can any one suggest how to write an Insert for the below scenario.

Source Table Name - "T_Dates" with the below columns

Id - 1, 2, 3,
Start_date - 10/1/2014, 10/7/2014, 10/17/2014
End_date - 10/6/2014, 10/4/2014, 10/14/2014
Set_date - 10/2/2014, 10/3/2014, 10/13/2014

target Table Name - "Dates" with the below columns

Id - 1 , 2 , 3
Date_Type - Start_date , End_date , Set_date
Date_Displayed - 10/1/2014, 10/6/2014, 10/2/2014



So the Columns in source table are the values in the target table.
I want to insert Source table values into target table with the below query.

INSERT INTO (Dates.date_type, Dates.date_displayed)
SELECT ( -----------------)
FROM T_Dates;


So, could any one suggest what could be the select phrase in this..

Thanks,
Suma
Posted

1 solution

If i understand you well, try this:
SQL
INSERT INTO (Id, Dates.date_type, Dates.date_displayed)
SELECT (
    SELECT Id, 'Start_date' AS date_Type, Start_date AS date_displayed
    FROM T_Dates
    UNION ALL
    SELECT Id, 'End_date' AS date_Type, End_date AS date_displayed
    FROM T_Dates
    UNION ALL
    SELECT Id, 'Set_date' AS date_Type, Set_date AS date_displayed
    FROM T_Dates
) AS T


Another way is to use UNPIVOT[^] operator.

Have a look at example:
SQL
SET DATEFORMAT mdy;

CREATE TABLE #T_Dates (ID INT IDENTITY(1,1), [Start_date] DATETIME, [End_date] DATETIME, [Set_date] DATETIME, [Another_date] DATETIME)

INSERT INTO #T_Dates ([Start_date], [End_date], [Set_date], [Another_date] )
VALUES('10/1/2014', '10/6/2014', '10/2/2014', '10/8/2014'),
('10/7/2014','10/4/2014','10/3/2014', '10/6/2014'),('10/17/2014','10/14/2014','10/13/2014', '10/9/2014')


CREATE TABLE #Dates(ID INT IDENTITY(1,1), Old_ID INT, Date_Type VARCHAR(30), Date_Displayed DATETIME)

INSERT INTO #Dates (Old_ID, [Date_Type], [Date_Displayed])
SELECT ID AS Old_ID, [Date_Type], [Date_Displayed]
FROM (
    SELECT *
    FROM #T_Dates
    ) AS pvt
UNPIVOT( [Date_Displayed] FOR [Date_Type]  IN([Start_date], [End_date], [Set_date], [Another_date])) as unpvt

SELECT *
FROM #Dates

DROP TABLE #T_Dates
DROP TABLE #Dates


Result:
ID  Old_ID  Date_Type   Date_Displayed
1   1   Start_date  2014-10-01 00:00:00.000
2   1   End_date    2014-10-06 00:00:00.000
3   1   Set_date    2014-10-02 00:00:00.000
4   1   Another_date    2014-10-08 00:00:00.000
5   2   Start_date  2014-10-07 00:00:00.000
6   2   End_date    2014-10-04 00:00:00.000
7   2   Set_date    2014-10-03 00:00:00.000
8   2   Another_date    2014-10-06 00:00:00.000
9   3   Start_date  2014-10-17 00:00:00.000
10  3   End_date    2014-10-14 00:00:00.000
11  3   Set_date    2014-10-13 00:00:00.000
12  3   Another_date    2014-10-09 00:00:00.000
 
Share this answer
 
v2
Comments
Suma0101 3-Nov-14 12:16pm    
Thank you so much Maciej, I have one more question.. What if there are multiple columns in Dates.Date_type (like start_date, end_date, ......say some 50 dofferent types).
Can you give me a solution to insert all the columns(all 50 date_types) in a loop using pl/sql..

Thanks,
Suma
Maciej Los 3-Nov-14 12:42pm    
You're very welcome ;)
Please, see updated answer.
Suma0101 5-Nov-14 18:09pm    
Great, Thank you
Maciej Los 5-Nov-14 18:11pm    
You're very welcome ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900