My articles about MSSQL Databases

Use the HAVING keyword to count duplicates in rows of a SQL table

There are times when you want to find out duplicated values in rows of a SQL table. We are going to use the HAVING keyword to list the values that are present more than one time. Let us consider the following table with three columns and three rows:

Read the complete article

Use the ISNULL function in MSSQL to define default values in your queries

When I work with SQL-Views and create complex SELECT statements that return data from multiple tables, I like to define default values in case a row contains columns with NULL as value. To do this I use the ISNULL function.

Read the complete article

The INSERT statement conflicted with the FOREIGN KEY constraint "XXX". The conflict occurred in database "XXX", table "XXX", column "XXX" error and how to fix it

It is very common to do migrations of data from a table of one database to another or inserting backed-up data into a table. For these tasks you will have to use long .sql scripts with INSERT statements.

When doing such migrations one possible problem might be that the Foreign Keys referenced in the primary-table will throw the INSERT statement conflicted with the FOREIGN KEY constraint “XXX. The conflict occurred in database “”, table “”, column “” error.

Read the complete article

Edit multiple rows in MSSQL after filtering the table

The past days I had to change multiple rows of an MSSQL table after filtering its content with a WHERE clause.

For this task you get great support from MSSQL Management Studio by using the criteria view of the tables data.

Read the complete article

How to export the schema and the data of an MSSQL-DB into an .sql script

One way to make a back-up file of your MSSQL Database is by exporting its schema, and optionally its data, into a new .sql script. The script can be automatically generated via the MSSQL Management Studio tool. Here is how:

Read the complete article

How to rename a column in an MSSQL temporal table

Temporal tables is a neat feature that allows us to version the data of our MSSQL-tables so that we keep a history of changes made. The renaming of columns of a table which is backed up by a temporal table is not 100% straightforward since you do not get the “Design” option when you right click on the table in SQL Management Studio. To perform the renaming, do the following steps:

Read the complete article

Copy all rows from one table to another new table in MSSQL

When you want to create a new table in a Database B and fill it with the rows of another table in Database A, then you can use the SELECT … INTO format like in the following query:

Read the complete article