Copy Data between SQL Databases-CodePudding

I have 2 Databases (Database1) and (Database2) DataBase1 has one table (table1) DataBase2 has one table (table2)

DataBase1.table1 (id as int, code as nvarchar(20), PunhTime as datetime) DataBase2.table2 (code2 as nvarchar(20), MyDate as datetime, Time1 as nvarchar(8) , Time2 as nvarchar(8))

could Achieve that through SQL ?

thanks in advance

here photo to show how data should copy .. enter image description here

CodePudding user response：

If they are running the databases on the same server, it is relatively easy to do:

INSERT INTO dbo.DataBase2.table2
(
  [code2],
  [Time1]
)
SELECT [code], [PunhTime]
FROM dbo.DataBase1.table1

You only have to change the column, since I it is unclear which column should go to which column.

CodePudding user response：

You may try this

WITH cte_PunchTime AS (
    SELECT Code, CONVERT(date, PunchTime) as MyDate, CONVERT(time, PunchTime) as [Time], ROW_NUMBER() OVER (PARTITION BY Code ORDER BY PunchTime) AS [Rank]
    FROM DataBase1.table1
)
INSERT INTO DataBase2.table2( Code2, MyDate, Time1, Time2)
SELECT Code, MyDate, MIN([Time]) AS Time1, MAX([Time]) AS Time2 
FROM cte_PunchTime 
GROUP BY Code, MyDate

You can use the Rank and PIVOT to use it for more than two columns. But number of columns need to be defined. For example, below code is for 4 columns.

WITH cte_Product AS (
    SELECT Code, CONVERT(date, PunchTime) as MyDate, CONVERT(time, PunchTime) as [Time]
    , ROW_NUMBER() OVER (PARTITION BY Code ORDER BY PunchTime) AS [Rank]
    FROM DataBase1.table1
)
INSERT INTO DataBase2.table2( Code, MyDate, Time1, Time2, Time3, Time4)
SELECT * FROM cte_Product 
PIVOT ( 
    Min([Time]) FOR [Rank] IN ([1], [2], [3], [4])
) Result;