INSERT (T-SQL)

Insert Statement in SQL SERVER adds one or more rows to a table or a view.

Syntax 1:
INSERT INTO TableName(Column1 , Column2 , Column3….)
VALUES (Column1Value , Column2Value , Column3Value);
Here Column1, Column2, Column3 etc… represents the column name in the table into which we want to insert the data.
Example: Let say I want to add Employee’s Id, Name, Date Of Birth and Salary into table called dbo.Employee.

INSERT dbo.Employee(Id , Name , DateOfBirth , Salary)
VALUES(1 , ‘John Smith’ , ‘1998-12-24’ , 50000);

Syntax 2:
INSERT INTO TableName(Column1 , Column2, Column3….)
SELECT SourceColumn1 , SourceColumn2, SourceColumn3
From SourceTableName
According to syntax 2, we can insert all or certain columns from SourceTableName.

Stored Procedure Silently truncate the Parameter Value – SQL Server.

Practically it doesn’t make sense that SQL Server throws an error if we insert a record with longer value string to the column but If that insert happens with stored procedure than stored procedure will silently truncate the parameter value to the specified length value. For example, if I have defined a parameter @Param Varchar (10) and if I pass my Parameter value as ‘SQL is trimmer’ it will truncate all the characters after the 10 character and stored first 10 character in a database table. Let run this as an example below.

Define the Stored Procedure and Database.

–Create Database
Create Database TSQLTest
GO
—Use TSQLTest
USE TSQLTest;
GO
—Create ParameterCheck
Create Table dbo.ParameterCheck
(
    Id        Int    Identity,
    Col1        VARCHAR (10) ,
    Col2        Varchar (5) ,
    ConstraintPrimary Key (Id)
);
GO
—Create Procedure with the parameter with same length of the table.
Create Procedure usp_ParameterCheck_Insert
    @Param_Col1        Varchar (10),
    @Param_Col2        Varchar (5)
AS
BEGIN
    SET NOCount ON;
    INSERT dbo.ParameterCheck(Col1, Col2)
    SELECT @Param_Col1, @Param_Col2;
    SET NOCOUNT OFF;
END;
GO
Now Let’s call the stored procedure and see the result.
—Stored Procedure call by passing parameter value.
EXEC usp_ParameterCheck_Insert
             @Param_Col1 = ‘SQL Is Trimmer.’
            , @Param_Col2 = ‘Trust Me.’
GO
—Get the Result From the table.
SELECT * From dbo.ParameterCheck

Result:

What are the Alternatives?

Alternative 1: I have read on other forums and people will say allow one character more than the actual length of the column and get exception which return to the application but the error doesn’t say which parameter value or on which column has longer string so this solution isn’t going to work. Please see below screen shot for it.

Alternative 2: The second approach I was trying with Table Valued Parameter as below but it has also the same problem the error doesn’t say which column has longer string as below.

Conclusion:

Max Character length should be at Application Level. It would be idle that client stop typing extract character than allowed length.

SQL SERVER Database Samples URLs

You can download SQL SERVER Product Samples through below URL.

You can download Latest SQL SERVER 2005 Samples from below URL.
http://msftdbprodsamples.codeplex.com/releases/view/4004

You can download Latest SQL SERVER 2008 Samples from below URL.
http://sqlserversamples.codeplex.com/releases/view/30803

Adventure Works Databases – 2012, 2008R2 and 2008
http://msftdbprodsamples.codeplex.com/releases/view/93587

Adventure Works 2014 Sample Databases
http://msftdbprodsamples.codeplex.com/releases/view/125550

Adventure Works and Samples for SQL SERVER 2016 CTP
https://www.microsoft.com/en-us/download/details.aspx?id=49502

Microsoft Contoso BI Demo Dataset for Retail Industry
http://www.microsoft.com/en-us/download/details.aspx?id=18279

Temporal Table in SQL SERVER 2016. Removing System Versioning.

Note: This topic applies to Microsoft SQL SERVER 2016.

So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement a temporal table on a new table or creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.

In this Blog Post, we will be learning on How to remove System Versioning? In our previous example on Changing the schema of a Temporal Table.

The following example shows how to completely remove System Versioning from dbo.SalesPerson Table and drop the dbo.SalesPersonHistory Table. We could also want to drop the PERIOD columns.

You can read the Previous Blog post Changing the schema of a Temporal Table
to continue with this series of the Blog Post or you can start from Temporal Table in SQL SERVER 2016.

Note: we cannot drop either the dbo.SalesPerson or the dbo.SalesPersonHistory tables while system versioning is enabled.

Step1: Below code Remove System Versioning from dbo.SalesPerson table, Drop the Period Column From dbo.SalesPerson table and Drop the dbo.SalesPersonHistory table.

USE SQL2016Demo;
GO
ALTER TABLE dbo.SalesPerson
SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.SalesPerson
DROP PERIOD FOR SYSTEM_TIME;
DROP TABLE dbo.SalesPersonHistory;

Temporal Table in SQL SERVER 2016. Migrate An Existing Solution to Use System Versioning.

Note: This topic applies to Microsoft SQL SERVER 2016.

In my previous Blog Post we have learned about how to implement Temporal Table on a new table? For more information on Temporal table please follow Temporal Table in SQL SERVER 2016.

Let’s assume that we have below solution managed via stored Procedure for System Versioning. In this existing solution we have a table called dbo.SalesPerson and if the Sales Person’s region changed then we maintain the history in the table called dbo.SalesPersonHistory.

Step1: First let’s create a Demo Database Called SQL2016Demo.

USE master
Go
— Drop database
IF DB_ID (‘SQL2016Demo’) IS NOT NULL
DROP DATABASE SQL2016Demo;
GO
CREATE DATABASE SQL2016Demo;

Output: Below Image show that SQL2016Demo Database is created.

Image1:

Step2 : In this step we will create a table called dbo.SalesPerson with an assumptions that each sales person has their sales region and their Sales Region sales over the time, when Business change their sales region we need to keep their Historical Sales Regions in a table called dbo.SalesPersonHistory. Here, in this example we have two columns called RowStartDate and RowEndDate to track the changes for the Sales Region for each SalesPerson over the period of time.

USE SQL2016Demo;
GO
CREATE TABLE dbo.SalesPerson
(
SalesPersonId     Int IDENTITY,
SalesPersonName   Varchar (50),
SalesRegion       Varchar (50),
RowStartDate      date NOT NULL Default GetDate (),
RowEndDate        date NOT NULL Default ‘2079-12-31’,
Constraint PK_SalesPerson_SalesPersonId Primary Key(SalesPersonId)
);
GO
CREATE TABLE dbo.SalesPersonHistory
(
SalesPersonId     Int NOT NULL,
SalesPersonName   Varchar (50),
SalesRegion       Varchar (50),
RowStartDate      date NOT NULL,
RowEndDate        date NOT NULL
);
GO
–Populate the dbo.SalesPerson Table with some initial data.
INSERT dbo.SalesPerson(SalesPersonName , SalesRegion , RowStartDate)
Values (‘John Smith’ , ‘Hertfordshire’ , ‘2015-05-01’) ,
       (‘Anthony Webster’ , ‘South West London’, ‘2015-05-01’),
       (‘Matt Williams’ , ‘Kingston upon Thames’, ‘2015-05-01’),
       (‘James Jenkinson’ , ‘Croydon’, ‘2015-05-01’),
       (‘Tom Bailey’ , ‘Slough’, ‘2015-05-01’)
GO

Step3: In this step we are creating a stored procedure to update the Sales Region for the Sales person and keep their historical Sales Region in SalesPersonHistory Table. Please find below T-SQL to create a stored procedure and update the Sales Region for the Sales Person Id 1 and 2.

USE SQL2016Demo;
GO
Create Procedure dbo.usp_UpdateSalesPersonRegion(@SalesPersonid INT, @SalesRegion Varchar(50))
As
Begin
SET NOCOUNT ON;
    UPDATE dbo.SalesPerson SET SalesRegion = @SalesRegion ,
                    RowStartDate = GetDate()
    OUTPUT    inserted.SalesPersonId,
inserted.SalesPersonName,
deleted.SalesRegion,
deleted.RowStartDate,
GetDate()-1 as RowEndDate
INTO dbo.SalesPersonHistory(
SalesPersonId,
SalesPersonName,
SalesRegion,
RowStartDate,
RowEndDate)
Where
SalesPerson.SalesPersonId = @SalesPersonid;SET NOCOUNT OFF;
END
GO
EXEC dbo.usp_UpdateSalesPersonRegion @SalesPersonid = 1,
@SalesRegion = ‘North West London’
GO
EXEC dbo.usp_UpdateSalesPersonRegion @SalesPersonid = 2,
@SalesRegion = ‘Hertfordshire’

Please find below Image2: which show how the records look like in dbo.SalesPerson and dbo.SalesPersonHistory table.

Image2:

So far, I have implemented a solution via stored procedure which maintains system versioning for the sales person’s historical sales regions in to SalesPersonHistory table. Now we are going to learn how we can migrate this existing solution to system versioning or Temporal Table.

How to Migrate Existing Solution to use System Versioning?

Step1:

The first change we need to do is that in our dbo.SalesPerson table maximum date value for RowEndDate is 2079-12-31 but System versioning required it to be the 9999-12-31 otherwise it will throw below error. So let’s update the RowEndDate to as 9999-12-31.

Error: ADD PERIOD FOR SYSTEM_TIME failed because table ” contains records where end of period is not equal to MAX datetime.

USE SQL2016Demo;
GO
UPDATE dbo.SalesPerson SET RowEndDate =
CONVERT (DATETIME2, ‘9999-12-31 23:59:59.9999999’)
GO

Step2: Follow below T-SQL code to migrate existing solution to use System Versioning.

USE SQL2016Demo;
GO
— Drop existing Stored Procedure, we also need to remove this stored procedure from the calling application.
DROP PROCEDURE usp_UpdateSalesPersonRegion;

— Adjust the schema for current and history table
–Drop the Default Constraint from column RowStartDate and RowEndDate On table dbo.SalesPerson;
ALTER TABLE dbo.SalesPerson
DROP CONSTRAINT DF_SalesPerson_RowStartDate;ALTER TABLE dbo.SalesPerson
DROP CONSTRAINT DF_SalesPerson_RowEndDate;— Change data types for existing period columns
ALTER TABLE dbo.SalesPerson
ALTER COLUMN [RowStartDate] datetime2 NOT NULL;ALTER TABLE dbo.SalesPerson
ALTER COLUMN [RowEndDate] datetime2 NOT NULL;

ALTER TABLE dbo.SalesPersonHistory
ALTER COLUMN [RowStartDate] datetime2 NOT NULL;
ALTER TABLE dbo.SalesPersonHistory
ALTER COLUMN [RowEndDate] datetime2 NOT NULL;

— Add SYSTEM_TIME period and set system versioning with linking two existing tables — (a certain set of data checks happen in the background)

ALTER TABLE dbo.SalesPerson
ADD PERIOD FOR SYSTEM_TIME ([RowStartDate], [RowEndDate])

ALTER TABLE dbo.SalesPerson
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SalesPersonHistory, DATA_CONSISTENCY_CHECK = ON))

Temporal Table in SQL SERVER 2016: How to Implement Temporal Table on a New table?

Note: This topic applies to Microsoft SQL SERVER 2016.

In my Previous Blog post Temporal Table in SQL SERVER 2016 we have learnt about Temporal Table which Microsoft has introduced in SQL SERVER 2016. In this Blog post we will be learning how to implement Temporal Table on a new table?

Step1: First let’s create Demo Database Called SQL2016 Demo.

Output: Observer Image1, Demo Database is created.

Image1:

Step2: In step we will implement Temporal Table or System Versioned table called Department without specifying History_Table option. In below transact SQL we haven’t use HISTORY_TABLE = [History_Table_Name] option, we have just use WITH (SYSTEM_VERSIONING = ON) option. In step 3 I will explain you how to use HISTORY_TABLE option.

Output: With the help of above transact SQL we have implemented a temporal table or System-Versioned table called Department and as we have not specified historical table name so system has created a table called dbo.MSSQL_TemporalHistoryFor_885578193 (History) highlighted in yellow in below Image2. Also History table is underneath the current table. In ideal world it is not easy to remember or workout which table is the history table for Department or any Temporal Table if the table name is like MSSQL_TemporalHistoryFor_%, so in next step we will be implementing Temporal Table with proper history table name?

The Department table has also period definition column called SysStartTime and SysEndTime of datetime2 data type. Please observe in blue circle in below image Image2.

Image2:

Step3: In step we will implement Temporal Table or System Versioned table called Department1 with specifying History_Table option. In below transact SQL we use HISTORY_TABLE = [Department1_History] option. Here in this step I will creating table called Department1 instead of Drop the existing table called Department. To drop table system versioned table, it requires to disable the system versioning on the table then we can drop the table. I will explain how to disable the System version on the table and Drop table or change the schema later on.

Output: So, in this step we implemented a Temporal Table or System-Versioned table called Department and we have specified historical table name called Department1_History so the system has created table called dbo.Department1_Histsory (History) highlighted in yellow in below Image3. Also History table is underneath the current table.

Image3:

So, in this Blog post we learnt how to implement Temporal Table or System versioned table on a new table, with specifying History_Table option and without specifying History_Table option.

Related Post

Temporal Table in SQL SERVER 2016

Migrate an Existing Solution to Use System Versioning.

Temporal Table in SQL SERVER 2016.

Note: This topic applies to Microsoft SQL SERVER 2016.

Microsoft have introduces a support for temporal table (Track historic changes) as a database feature in SQL SERVER 2016 which provides built in support for provide information about data stored in the table at any point in time rather than only the data that is current at the moment of time. Let’s understand what is temporal table? , Why do we need temporal table, how does this temporal table work?

What is Temporal Table?

Temporal table is table for which PERIOD definition exists and it contains system columns called as SysStartTime and SysEndTime with a datatype of datetime2 into which the period of validity recorded by the SQL SERVER, and temporal table has associated history table. This historical table holds all the prior versions of each record with their period of validity and this all has been managed by SQL SERVER internally. With temporal table we can determine value of the record at any point in time rather than just the current value of the each record. A temporal table is called as system-versioned table.

Why do we need temporal table?

Real data sources are dynamic and historical data analysis is often key to business success. We can use temporal table in below cases.

Time Trend: To understand the business trends over time or Tracking data changes over time.
Data Audit: Auditing all changes to data.
Slow Changing dimension, useful for data warehouse load for Type 2 Dimension.
Repair Record Level corruption: in the event of accidental update/delete you can easily recovered each record from history table.

How does temporal table work?

Implementations of a temporal table involves pair of tables, current table and history table. With each of these tables, two additional datetime (datetime2 datatype) columns are used to defined period of validity for each record – a system start time (SysStartTime) column and a system end time (SysEndTime) column. The current table contains the current value for each record. The historical table contains the each previous value for each record, if any, and the start time and end time for the period for which it was valid.

INSERTS: On an INSERT of a record the system set the value for the SysStartTime column to the UTC time of the current transaction based on the system clock and set the value for the SysEndTime column to the maximum value of 9999-12-31 – this marks the record as open.

UPDATES: On an UPDATE, the system stored the previous value of the record in the history table and sets the value for the SysEndTime column to the UTC time of the current transaction based on the system clock. This marks the record as closed, with a period recorded for which the record was valid. In the current table record is updated with its new value and system sets the value for the SysStartTime column to the UTC time of the current transaction based on the system clock. The value for the updated record in the current table for the SysEndTime column remains the maximum value of 9999-12-31.

DELETES: On a DELETE, the system stored the previous value of the current record in the history table and sets the value for the SysEndTime column to the UTC time of the current transaction based on the system clock. This marks the record as closed with a period recorded for which the previous record was valid. In the current table the record is removed and queries of the current table will not return this value. Only queries that deal with history data return data for which a record is closed.

MERGE: On a MERGE behaves as an INSERT, an UPDATE, or a DELETE based on the condition for each record.

For More information about Temporal Table please follow:

1) https://msdn.microsoft.com/en-us/library/dn935015%28v=sql.130%29.aspx

2) http://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

Migrate an Existing Solution to Use System Versioing.