How to add a date when a record is created

Estimated reading time: 2 minutes

You maybe working with a lot of data and inserting it into tables, and want to create a date stamp so you can see when the record was created.

Below we will take you through the steps of how to complete this in SQL server management studio.

In this table we have created a new column called “date_created”

We have altered this column to allow automatic date stamp creation as follows:

alter table dbo.CUSTOMER 
add constraint df_TABLE_DATE default getdate() for date_created

What the above code does is basically tell the database any time a new record is created, it should automatically add in a date to the column.

Note that the existing values will not change as I applied this update after the column creation.

Adding in a new record and checking that date stamp has been created

Using the below code:

Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES (777777,'web')

Yields the following result:

As can be seen now for the new record, the date has been added.

It is probably good, that when designing the database table, that this is factored in from the very start , otherwise you will have to go back and change lots of records, which will be very time consuming.

how to insert data into a table in SQL

Estimated reading time: 4 minutes

After building your data using a create statement, one of the next steps using SQL is to now get your data in, so it can be managed and updated where necessary.

There are many ways to import your data including , one you could use is import a CSV file with an SQL query.

Here we talk about some of the steps involved, and also how an understanding of the primary key is important.

Before all the following updates happen, the table looks like this:

--Inserting one row of data
Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES (456789,'web')
select * from dbo.CUSTOMER

Insert one row into a database table

In the below code we will use the INSERT statement that basically tells the SQL to add a new row to the table.

It is important to remember two things about the primary key:

  • It has to be unique.
  • There can be no null values.

As a result of this been enforced, it will allow tables to be joined together assuming they have the same primary key information.

The output gives us the following:

Inserting multiple rows of data into one table

Following on from the above , we may have a need to insert more than one row.

Again it would follow the same steps, except, we need to ensure all data been inserted follows the same structure and there are no duplicate values in the CUSTOMER_NO column.

Any duplicates in this column would cause it to fail as follows:

It would fail on 125872 and then 456789, both need to be removed from the insert. A sample error message is shown below.

Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES 
 (125872,'web'),
 (333333,'web'),
 (987981,'web'),
 (879784,'web'),
 (654654,'web'),
 (456789, 'Phone')
 select * from dbo.CUSTOMER

Removing the duplicate values from the insert statement, also allows the update to complete successfully:

Insert INTO dbo.CUSTOMER(CUSTOMER_NO,customer_type)
VALUES 
 (333333,'web'),
 (987981,'web'),
 (879784,'web'),
 (654654,'web')
 select * from dbo.CUSTOMER

Insert values into a particular column

There is going to be a scenario, from time to time where you only want to insert values into a particular column, but not all of them.

With this table, as it has a primary key, you can insert new values into CUSTOMER_NO, with no values required for customer_type, as shown below.

If there was no primary key, then you could use the below statement, and in the where clause identify those specific records that need updating.

--Insert values into a particular column
Insert INTO dbo.CUSTOMER(CUSTOMER_NO)
VALUES 
(25897458),
(25898633)
select * from dbo.CUSTOMER

This will yield in the output:

The next section will show you the error , when trying to insert into the customer_type, and leaving CUSTOMER_NO blank.

Inserting a value in without updating the primary key, will fail

Finally we will look at how trying to update a table with a primary key, without providing the primary key value will give you an error.

The code we will use is:

--Inserting a value in without updating the primary key, will fail
Insert INTO dbo.CUSTOMER(customer_type)
VALUES 
('phone'),
('web')
select * from dbo.CUSTOMER

But the table schema states that the CUSTOMER_NO cannot be null:

As a result the following error appears:

In summary we have taken you through a number of different steps , and some of the caveats that you need to understand before inserting data.

The most important thing is that if you have a primary key on a table, doing inserts needs a value supplied that is not a duplicate and also that no empty values for that column are supplied.