SQL Server : convert historical data to Temporal Tables

Question

We have SQL Server 2016. For many years we have our own history. Now I want to convert historical data to a new format, and use the CustomAudit tables as a history table with already available historical data.

For the beginning there will be a small example, then the question

CREATE TABLE [dbo].[client] ( idclient int identity(1,1) primary key, clientData nvarchar (400) ) ON [PRIMARY] INSERT [dbo].[client] ( clientData ) values ('some-12221') INSERT [dbo].[client] ( clientData ) values ('some-22111') alter table [client] add StartTime datetime2 GENERATED ALWAYS AS ROW START DEFAULT GETUTCDATE(), EndTime datetime2 GENERATED ALWAYS AS ROW END DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (StartTime,EndTime) CREATE TABLE [dbo].[CustomAydit_client] ( idclient int, dEditDate datetime NOT NULL DEFAULT (getdate()), clientData nvarchar (400) ) ON [PRIMARY] alter table [CustomAydit_client] add StartTime datetime2 GENERATED ALWAYS AS ROW START DEFAULT GETUTCDATE(), EndTime datetime2 GENERATED ALWAYS AS ROW END DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (StartTime,EndTime) INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2016-02-11 10:08:11.923' AS DateTime)), 'some-211') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (2, (CAST(N'2016-12-06 10:08:11.923' AS DateTime)), 'some-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2015-05-19 10:08:11.923' AS DateTime)), 'some-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (2, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some-211') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2016-05-26 10:08:11.923' AS DateTime)), 'some-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (2, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some33-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2016-05-06 10:08:11.923' AS DateTime)), 'some3-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate,clientData) VALUES (2, (CAST(N'2016-11-16 10:08:11.923' AS DateTime)), 'some-1') INSERT [dbo].[CustomAydit_client] (idclient, dEditDate, clientData) VALUES (1, (CAST(N'2016-02-17 10:08:11.923' AS DateTime)), 'some-1')

I need StartTime for row AND for row.

For customer 1, I want to take row 2 data of field DeditDate

Update this data row 1 field EndTime

And the last line, the client should not have EndTime, this ok 9999-12-31 23:59:59.9999999

SELECT ROW_NUMBER() OVER( PARTITION BY idclient ORDER BY dEditDate) AS tempid, idclient, dEditDate, StartTime, EndTime FROM [dbo].[CustomAydit_client] ORDER BY idclient, dEditDate

You did a great job posting the table definition and sample data. But it is not very clear to me what you want as output. — Sean Lange
– Sean Lange, Commented May 2, 2017 at 20:23
In Temporal Tables for the row your need StartTime and EndTime. I don't how to do EndTime correct. Use my data — GoAnatol
– GoAnatol, Commented May 2, 2017 at 20:29
I need somesing like that 1drv.ms/i/s!ApEvwFL7i3nmkvlgTivat9If6J_vdQ — GoAnatol
– GoAnatol, Commented May 2, 2017 at 20:36
Not sure what that image is supposed to show me. Your output looks like the same thing as the data entered. Or are you trying to make the end date be start date value of the "next" row?? — Sean Lange
– Sean Lange, Commented May 2, 2017 at 20:40

Vamsi Prabhala · Accepted Answer · 2017-05-02 20:40:09Z

1

You can use the lead function to get a value from the next row as endtime.

select ROW_NUMBER() OVER(PARTITION BY idclient ORDER BY dEditDate) AS tempid, idclient, dEditDate, StartTime, lead(dEditDate) over(PARTITION BY idclient ORDER BY dEditDate) as EndTime from [dbo].[CustomAydit_client] order by idclient, dEditDate

answered May 2, 2017 at 20:40

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

R Yoda Over a year ago

Do you have any experience about the performance of lead in case of many many rows (beyond 100 mio)?

GoAnatol Over a year ago

Thanks you can help me a lot

Danilo Braga Over a year ago

@GoAnatol, did you get the answer for you question? I need exactly the same thing

Collectives™ on Stack Overflow

SQL Server : convert historical data to Temporal Tables

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related