SQL Server insert using JSON


During this post, I explained how to get information from a database in JSON format. In this one, I will show you how to store information sent to the database in JSON format.

I will use a very common scenario, storing a master-detail combination. For this, I use the Order-Order Details tables in Northwind database, which you can get here.

The goal is to store a new Order, with several Order Details in the same procedure, by using T-SQL OPENJSON.

Like a cooking recipe, I will explain this step by step.

Define the JSON schema.

We want to store the information received in the Order Details table, so its schema will be the schema received in the JSON information.

Simply get the Order Details schema and remove the NOT NULL modifiers. This will be used in the WITH modifier of the OPENJSON statement this way:

OPENJSON(@Details) WITH([OrderID] [INT], [ProductID] [INT],
[UnitPrice] [MONEY], [Quantity] [SMALLINT], [Discount]
[REAL]);

Prepare the Stored Procedure.

Parameters.

It must have parameters to receive all the data for the Orders Table’s columns, and one more containing the entire JSON information for the Order Details table. Notice the OrderID parameter is declared as OUTPUT, so the calling code could retrieve the new Order ID for the inserted row.

   @OrderID        INT OUTPUT
, @CustomerID     NCHAR(5)
, @EmployeeID     INT
, @OrderDate      DATETIME
, @RequiredDate   DATETIME      = NULL
, @ShippedDate    DATETIME      = NULL
, @ShipVia        INT
, @Freight        MONEY
, @ShipName       NVARCHAR(40)
, @ShipAddress    NVARCHAR(60)
, @ShipCity       NVARCHAR(15)
, @ShipRegion     NVARCHAR(15)  = NULL
, @ShipPostalCode NVARCHAR(10)
, @ShipCountry    NVARCHAR(15)
, @Details        NVARCHAR(MAX)

Insert the Orders new row values.

It is a simple insert – values sentence, as follows:

INSERT INTO [Orders]
      (
   [CustomerID]
 , [EmployeeID]
 , [OrderDate]
 , [RequiredDate]
 , [ShippedDate]
 , [ShipVia]
 , [Freight]
 , [ShipName]
 , [ShipAddress]
 , [ShipCity]
 , [ShipRegion]
 , [ShipPostalCode]
 , [ShipCountry]
  )
VALUES
    (
  @CustomerID
, @EmployeeID
, @OrderDate
, @RequiredDate
, @ShippedDate
, @ShipVia
, @Freight
, @ShipName
, @ShipAddress
, @ShipCity
, @ShipRegion
, @ShipPostalCode
, @ShipCountry
);

Get the new inserted OrderId.

For this, the procedure must use the IDENT_CURRENT function.

SET @OrderID = IDENT_CURRENT('[Orders]');

Insert the Order Details using OPENJSON.

In this case, using Insert – select statement, and OPENJSON from the Details parameter as source, declaring it with the previously obtained schema. Notice the utilization of the @OrderID parameter for the Order Id value in each row.

INSERT INTO [Order Details]
      (
   [OrderID]
 , [ProductID]
 , [UnitPrice]
 , [Quantity]
 , [Discount]
  )
       SELECT 
          @OrderID /* Using the new Order ID*/
 
        , [Productid]
        , [UnitPrice]
        , [Quantity]
        , [Discount]
       FROM 
          OPENJSON(@Details) WITH([OrderID] [INT], [ProductID] [INT],
          [UnitPrice] [MONEY], [Quantity] [SMALLINT], [Discount]
          [REAL]);

The C# code.

Define the Order and Order Details entities in your Application.

I created a simple C# Console Application Sample. In it, the Order and Order_Details has been defined by using the Paste Special Paste JSON as Classes feature in Visual Studio. You can see the step by step here.

The Insert routine in the main program.

The code creates a new instance of the Order class, with values,

Order order = new()
{
   CustomerID = "ALFKI",
   EmployeeID = 1,
   OrderDate = DateTime.UtcNow,
   RequiredDate = DateTime.UtcNow.AddDays(5),
   ShipAddress = "Obere Str. 57",
   ShipCity = "Berlin",
   Freight = 12.05F,
   ShipCountry = "Germany",
   ShipName = "Alfreds Futterkiste",
   ShipPostalCode = "12209",
   ShipRegion = null,
   ShipVia = 1
};

Then get information from a previously defined set, (as JSON), to have an array of Order Details.

// Create the details. To Avoid a long code, just get it from a JSON sample
var details = System.Text.Json.JsonSerializer.Deserialize<OrderDetail[]>
   (InsertUsingJSONSample.Properties.Resources.Details);

Create the Connection and Command objects.

A connection object to the database is assigned to a Command object, with the name of the stored procedure as text, which is defined as a Stored Procedure command type.

SqlConnection con = new SqlConnection(InsertUsingJSONSample.Settings1.Default.ConString);
SqlCommand com = new SqlCommand("InsertWithJSONSP", con)
{
   CommandType = System.Data.CommandType.StoredProcedure
};

Add the parameters

Then, all the Order properties are added as parameters, plus one more, Details, containing the Order_Detail array expressed as JSON.

To do so, the code use Reflection to get all the properties in the Order instance and their values.

Note the parameter @OrderID is defined as InputOutput, so the code could retrieve the new Order Id once the procedure ends execution.

foreach (PropertyInfo item in order.GetType().GetProperties())
{
   com.Parameters.AddWithValue("@" + item.Name, item.GetValue(order));
 
}
com.Parameters["@OrderId"].Direction = System.Data.ParameterDirection.InputOutput;

Finally, the command is executed to insert the new Order with the Details and retrieve the new Id.

using (con)
{
   con.Open();
   int retValue = await com.ExecuteNonQueryAsync();
   int NewOrderID = (int)com.Parameters["@OrderId"].Value;
}

As usual, you will find the code sample here.

By using this method, you reduce the calls between your app and the database server, optimizing the response, and including the entire store process in a unique implicit transaction.

HTH

PowerShell: backup Docker containers


I have to reinstall my computer several times in a year, and I need to keep backup of my different stuffs so I was looking for some kind of a procedure to backup Docker containers and I found this link Docker backup – Easy steps to backup and restore your containers (bobcares.com) where Reeshma Mathews explained how to do it.

However, sometimes I need to perform the backup procedure over several containers at the same time, so I tried to enhance this routine to make this happen using PowerShell.

I adapted Reeshma’s steps and include them in a PowerShell script as follows:

Reeshma uses docker ps command to get the containers in the Docker environment.

However, the command do not get those containers not running at the time of command execution.

So, I use the command adding the -a parameter, which list all the containers.

The command returns the information in several columns, as space separated values.

The last column is the name of the container, so I get the information by looking of the last space (Docker do not admit spaces in the containers name, so the last space is the previous char of the container name).

I store the result in a PowerShell variable, which becomes a string array, including the titles row.

$Result=docker ps -a

So, starting at the row number one  of the array, (starting in zero), there are the values from the containers. Each row is used as an argument of a function, which performs the actions documented by Reeshma.

for($i=1;$i -lt $result.count;$i++)
{
 
    Backup-Container $result[$i]
}

However, we need to use variables instead of hardcoding the names.

Since docker commands do not interpret the parameters well when call them from PowerShell, it is needed to prepare them in a string variable and call them by Invoke-Expression PowerShell command.

	 $command="docker save -o $namebak $Name"
	 Invoke-Expression $command

This is the function where I adapted the original code:

function Backup-Container
{
[CmdletBinding()]
param
(
	[Parameter(Mandatory=$true,Position=0,HelpMessage="Must define the docker container name" )]
	[string]$Line
 
)
 
	 $SpacePos=$Line.IndexOf(' ') # Find the first space to retrieve the ContainerID
	 $ContainerID=$Line.Substring(0,$SpacePos)
	 $SpacePos=$Line.LastIndexOf(' ') # Find the last space, where starts the container's name
	 $name=$Line.Substring($SpacePos)
	 Write-host "Backing up $name"
	 $Name=$Name.ToLower()+"bkp" # Add 'bkp' the the container's name as file name for the backup
	 $command="docker commit  $ContainerID $name" # First, create a commited version of the container
	 Invoke-Expression $command
	 $namebak="$name.tar"
	 $command="docker save -o $namebak $Name" # Then, save the commited one to disk
	 Invoke-Expression $command
}

Just a final couple of comments:

  • The function is in a PowerShell module, because the idea is progressively building a library of common Docker actions. That’s why the first line is for include the library.
Import-Module -Name D:\Desarrollos\PowerShell\Docker\DockerLib.psm1
  • The script change drive and folder to establish the place where I use to store the backups. Please, adapt it to your needs.
d: cd D:\Desarrollos\Docker\Backup
  • By using the Get-Process command, the script ensures Docker Desktop is running until start the process. If it is not the case, the script starts Docker Desktop.
# Look for Docker Desktop instance
$DockerProcesses=Get-Process|Where-Object {$_.ProcessName -eq 'Docker Desktop'}
if($DockerProcesses.count -eq 0) #If there is no instance, try to start it
{
    $command="C:\Program Files\Docker\Docker\Docker Desktop.exe"
    Invoke-Expression "& '$command'"
    while($DockerProcesses.count -eq 0) #Whait for an instance
    {
        Start-Sleep -Seconds 120
        $DockerProcesses=Get-Process|Where-Object {$_.ProcessName -eq 'Docker Desktop'}
    }
}

As usual, here you have the Docker Full backup script, and here the Docker library.

HTH

Languages and countries in your Apps


In our current globalized world, at this time, any web site must be multilingual, enabling the user to select the language to use.

Moreover, a global company needs to know from which country the user connected.

A lot of sites, more of the streaming services as examples, identifies the country based on the IP address. Which could be a mistake, since a lot of people are in a different country of which belongs to.  

Anyway, your database must need a language list and a countries list.

This post shows you a sample application which, using the standardized languages and countries from .Net Framework, and adding some information from external sources, set up some tables to manage this in your databases.

System.Globalization Namespace

The tool uses information from the System.Globalization namespace, which appears with .Net Framework from it very beginning.

The CultureInfo type exposes each of the languages in use around the world thru the GetCultures member, based in the ISO 639-1: Codes for the Representation of Names of Languages  and in the ISO – ISO 3166 — Country Codes.

Each CultureInfo class instance exposes these properties among others:

Name The identifier of the culture in the culturecode2-country/regioncode2 format
EnglishName The culture name in Enlgish
DisplayName The culture name in the culture of your current configuration
NativeName The culture name in it own culture
IetfcultureTag The culture part of the name
LCID The Windows Language code
ThreeLetterISOcultureName ISO 639-2 code
TextInfo Information about text management
Calendar Calendars used by the culture
DateTimeFormat How the dates and times are managed
NumberFormat How the numbers, currencies etc. Are used
IsNeutralCulture Indicates if it is just a language or a language and country specification

Finally, I get information from two external sources. I got the GPS coordinates of each country from here meanwhile the flag’s pictures are from here. You can found the urls inside the code as well.

Storage’s schema.

Languages/Countries database schema

The tool create 3 tables as you can see in the Diagram.

It is necessary this way, because some countries use more than one language, and the relationship must be preserved.

The tables have a InUse column, to enable/disable each row for your application. So, you can query the Languages table for all the rows with the InUse value in 1, to display only those languages you desire use, or have enabled.

Note: It is important using nvarchar/nchar data types, since several Native names are in UTF-8 chars.

Using the tool.

The tool expects at least the connection string to your database. It accepts a second parameter for the schema name under the tables will be created. If this value is not provided, the tool assumes “Masters” as schema name.

In any case, the DDL scripts manage the creation of the schema if it does not exist.

The source code of the DataGen solution is in my GitHub.

If you prefer just use a T-SQL script to add the tables, here is the script.

In future posts, I will show some faqncy methods for site AND CONTENT localization.

Auditing Data Changes


Sometimes, it is not only important to know when and who perform the last change but trace all the changes to the data in certain important tables.

Big applications, require security behaviors and traceability. And better more important, they must be maintained independently of the applications, since more than one application could modify the same database.

And here is where triggers come to rescue.

Of course, I am not saying you must implement business rules in triggers (or even in Stored Procedures).

Business rules are rules, not data manipulation, even when in some SPECIAL cases, you must implement some in database objects, but this is not the generic case. (We will discuss this in several other posts in the future).

How it works

To store any change, we need a COPY of the data each time it is about changed. So, we need another table with the same definition than the original. Moreover, it is good to store who and when the change was performed.

The stored procedure creates a new table, based in the source table definition, adding the following columns.

ColumnData TypeUsage
DateChangeddatetime2(7)When
UserChangednvarchar(150)Who
Actionnvarchar(15)How

The stored procedure receives an optional parameter for the destination schema. If it is provided, the table denomination will be the destination schema and the same table name than the original. If not, then the history table will be named like the source one, plus the “_Hist” suffix.

Once the table exists, the procedure must create Triggers for Insert, update and delete changes over the source table.

How the triggers work?

A trigger is a T-SQL code which is executed automatically when something happens with one or more rows in a table.
The code could be linked to one or more of the three major events: Insertion, update or deletion.
Meanwhile the trigger is running, a special table, called inserted, contains the new values in change, and another table, called delete, contains the old values. Notice that this tables could contains more than one row, in case the action that start the process, manipulates one or more rows, like in one massive update.
From the trigger point of view, there is no update, but a delete and insert combined.
So, inside the trigger, depending on the action, you can have one or both “virtual” tables, as follows.

Actioninserteddeleted
InsertX
UpdateXX
DeleteX

In this example, the trigger will store in the historical table a new entry with the row (or rows) are inserted. And entries with one or more deleted rows in updates or deletes, to persist the older version of the rows.

CREATE TRIGGER [dbo].[EmployeeTerritories_TInsert] 
	ON [dbo].[EmployeeTerritories]
	AFTER INSERT
AS
  BEGIN
    SET NOCOUNT ON;
	--Insert a new ow in historical table
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()-- The exact moment of the insert
            , USER_NAME()-- The user performing the Insert
            , 'Insert'
           FROM 
              [inserted] [O];
  END;
CREATE TRIGGER [dbo].[EmployeeTerritories_TUD] 
	ON [dbo].[EmployeeTerritories]
	AFTER UPDATE, DELETE
AS
  BEGIN
    DECLARE 
       @Action NVARCHAR(15) = 'Update';
	   /*If there is no rows in inserted table,
	   then it is not an update*/
 
    IF NOT EXISTS
     (
       SELECT 
          *
       FROM 
          [inserted]
     )
      BEGIN
        SET @Action = 'Delete';
      END;
    SET NOCOUNT ON;
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()
            , USER_NAME()
            , @Action
           FROM 
              [deleted] [O];
  END;

So, using the same methodology than in the SP Builder post, with a Table Type variable to collect the columns of the original table, the procedure builds the triggers’ scripts to create them with sp_executesql .

You can found the script fot the SP Create Triggers here.

HTH

Tracking data changes


Sooner or later, it happens. Someone asks “When this data has been modified? Who changed this information?”

A reliable system must ensure this information could be obtained any time.

In this post I offer you a simple way to add columns to any table you want to preserve the metadata about the last changes.

In another post, I’ll talk about tracking all the changes.

Like in the last posts, I prepare it as stored procedure, which receives two parameters:

  • The Table Name
  • The Schema Name (optional) in which case, “dbo” is assumed

The procedure will add the following columns:

Column  NameInformation StoredDefault Value
Created byusername in the insert actionCURRENT_USER
Modified Byusername in the update action 
Creation Datedate & time of the creationSYSUTCDATETIME
Modification Datedate & time of the update 
Note: An «Active» column is added as well, to manage «soft deletes» in case you need it.

Notice there is no default value for update actions, since they are not supported by the database. The values must be provided by application procedures. In fact, they could be provided for the creation process as well, if desired.

Some code highlights

Using a select from INFORMATION_SCHEMA.COLUMNS the procedure detects if the column to add already exists.

DECLARE 
   @Isthere INT;
DECLARE 
   @CheckForColumn NVARCHAR(300) =
   'select @Isthere =count(*) from INFORMATION_SCHEMA.COLUMNS T where TABLE_SCHEMA=@schema and TABLE_NAME=@TableName and T.COLUMN_NAME=@ColumnName'

Like in other examples I already post, using sp_executesql stored procedure, can execute the T-SQL sentence passing parameters.

DECLARE 
   @ColumnName SYSNAME = 'Created by';
EXEC [sp_executesql] 
       @CheckForColumn
 ,
 N'@Schema sysname,@TableName sysname,@ColumnName sysname,@Isthere int OUTPUT'
 , @schema
 , @TableName
 , @ColumnName
 , @Isthere OUTPUT;

If the column doesn’t exist, two other dynamic T-SQL sentences are executed, adding the column and later configuring the default value.

DECLARE 
   @SqlToExecute NVARCHAR(MAX);
IF @Isthere = 0
  BEGIN
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD ['
                        + @columnName
                        + '] nvarchar(150) NULL';
    EXEC [sp_executesql] 
           @SqlToExecute;
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD CONSTRAINT [DF_'
                        + @TableName
                        + '_'
                        + @columnName
                        + '] DEFAULT CURRENT_USER FOR ['
                        + @columnName
                        + ']';
    EXEC [sp_executesql] 
           @SqlToExecute;
  END;

The same steps are repeated for the other columns.

In similar way with other previous posts, this stored procedure could be executed over all the tables in a database, using a select from INFORMATION_SCHEMA.TABLES.

You can find the procedure in my repository here.

HTH

T-sql SP Builder


A Developer tool

When things are complex, and it is preferable to not use any ORM like EF, or the database needs to be secured, probably you prefer to use SQL Server Stored procedures.

However, it happens that in those cases, the database uses to have hundreds of tables, and it takes a lot of time to write the standard procedures for CRUD operations for them. This code helps you to build the Insert, Update and Delete procedure for each table. In all cases, the task is performed in just one row, using the Primary key to identify it for update and delete.

How it works.

By getting information from the INFORMATION_SCHEMA view, and using some special system tables like sys.columns and sys.computed_columns, the code retrieves the information for the required table.

The information is stored in a table variable which contains columns to make easier to build the different parts of the sentence, like adding parenthesis, default values, etc. which contains data as you can see in the next image

Table variable columns

Then, several queries against the table variable constructs the parameters, sentence, and where segments of the different procedures.

In this case, I will not explain each part.  Most of it use STRING_AGG to concatenate the different parts.

However, some comments about the different “issues” I found making this:

  • The parameters for the Insert procedures, include an OUTPUT statement, in case a column has identity specification to get the value for the inserted row. To do so, the procedure uses IDENT_CURRENT function. But the parameters for Update and Delete do not need the OUTPUT.
  • The computed columns must not be inserted or updated, so must be excluded of the procedure. This happens in case a column is of timestamp datatype as well.
  • Just to be sure what the script generates, the results are printed so you can review them.

You can download this script here.

The script as Stored Procedure.

If you want to use this several times during your development, you can use this second script, which add this as a Stored Procedure, so you can call it just passing the table name, and, when needed, the schema name.

Moreover, it accepts a third parameter, to perform the procedures’ creation directly.

Finally, if you want to use the procedure for all the tables of a database at once, just use this sentence for creating a complete script.

SELECT     
   'EXECUTE @RC = [Development].[SPBuilder]    @TableName'
   + QUOTENAME([table_schema])
   + ',@SchemaName ='
   + QUOTENAME([table_name])
   + ' ,@Create=1'
FROM     
   [INFORMATION_SCHEMA].[TABLES]
WHERE
       [table_type]
       = 'BASE TABLE'
       AND [table_name] NOT LIKE 'sys%';

enhanced truncate table


During the develop and testing of any applications, probably you need to clean up test data, in order to test from scratch.

In SQL Server, you have the TRUNCATE TABLE statement to perform this.

However, the statement execution will fail if the table has references to other tables.

This code snippet allows you to truncate a table, avoiding the problem, by removing the relations, truncating the table and reestablishing the relations.

sp_fkeys

In this code, I use the sp_fkeys system stored procedure, which retrieve the definition of any relationship between tables.

You can find the procedure documentation here.

The procedure is an old one, I mean, it exists in several SQL server versions.

That’s the reason some of the parameters are about owners. In older versions, the objects belong to users, and that is the reason for name them “owners”.

However, consider that when speaking about owners in objects, in fact we are talking about schemas.

The snippet

These are the steps the snippet performs.

  • The first step is declaring the schema and the name of the table to be truncated. (We will define how to manage this in a Stored procedure later).
DECLARE 
       @TableName    SYSNAME = 'WebCustomers'
     , @TableSchema  SYSNAME = 'OnLineSales';
  • Then, an in-memory table is declared, for the different columns the sp_fkeys stored procedure returns.
DECLARE 
       @FKSTableVarOri TABLE
 (
   [PKTABLE_QUALIFIER]  SYSNAME
 , [PKTABLE_OWNER]      SYSNAME
 , [PKTABLE_NAME]       SYSNAME
 , [PKCOLUMN_NAME]      SYSNAME
 , [FKTABLE_QUALIFIER]  SYSNAME
 , [FKTABLE_OWNER]      SYSNAME
 , [FKTABLE_NAME]       SYSNAME
 , [FKCOLUMN_NAME]      SYSNAME
 , [KEY_SEQ]            SMALLINT
 , [UPDATE_RULE]        SMALLINT
 , [DELETE_RULE]        SMALLINT
 , [FK_NAME]            SYSNAME
 , [PK_NAME]            SYSNAME
 , [DEFERRABILITY]      SMALLINT
);

When retrieving the information of the relationships, the return value uses one row for each column implied ion a relation. So, if a relation is using a composite key, there will be more than one row for the same relation.

It is needed to consolidate this, to define just one entry by relation, with the columns defines as a comma separated list, for the re-creation of them later.

  • a second in memory table is defined to process the consolidation.
DECLARE 
       @FKSTableVar TABLE
 (
   [PKTABLE_OWNER]  SYSNAME
 , [PKTABLE_NAME]   SYSNAME
 , [PKCOLUMN_NAME]  SYSNAME
 , [FKTABLE_OWNER]  SYSNAME
 , [FKTABLE_NAME]   SYSNAME
 , [FKCOLUMN_NAME]  SYSNAME
 , [UPDATE_RULE]    SMALLINT
 , [DELETE_RULE]    SMALLINT
 , [FK_NAME]        SYSNAME
)
  • Using the sp_fkeys procedure, the snippet get the child relationships of the table to be truncated, and store them in the fist in-memory table.
INSERT INTO @FKSTableVarOri
EXEC [sp_fkeys] 
     @pktable_name = @TableName
   , @pktable_owner = @TableSchema;

Then, by using the STRING_AGG function, the second in-memory table is filled with one row by each relationship.

  • The STRING_AGG function, allows you to concatenate values from different rows in one string, using a delimiter between values.
INSERT INTO @FKSTableVar
       SELECT 
           [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [PKCOLUMN_NAME] + 
                      ']', ',') AS [PKCOLUMN_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [FKCOLUMN_NAME] + 
                      ']', ',') AS [FKCOLUMN_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE]
         , [FK_NAME]
       FROM 
          @FKSTableVarOri
       GROUP BY 
           [FK_NAME]
         , [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE];
  • There is a chance no relationships exist. The snippet check if this happen, to proceed directly to the truncation of the table.
DECLARE 
       @References  SMALLINT;
 
SELECT 
    @References = COUNT(*)
FROM 
   @FKSTableVar;
 
IF @References > 0 --there are relationships
  BEGIN
  • It is possible that the tables using our candidate table as reference, have rows with referenced values. In that case, it is impossible to truncate the table, or the relationships will be broken. To avoid this, the snippet dynamically creates a script to sum de row-count of all the child tables. In case there are rows, the process aborts with an error.
DECLARE 
       @Counters  NVARCHAR(MAX);
SELECT 
    @Counters = STRING_AGG(
                           '(select count(*) from [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '])', '+')
FROM 
   @FKSTableVar;
SET @Counters =
                'Select @ForeignRows=' + 
                @Counters;
PRINT @Counters;
DECLARE 
       @ForeignRows  INT;
EXEC [sp_executesql] 
         @Counters
   , N'@ForeignRows int OUTPUT'
   , @ForeignRows OUTPUT;
SELECT 
    @ForeignRows;
IF @ForeignRows > 0
  BEGIN
    RAISERROR(
    'There are dependent rows in other tables', 10, 1);
  END;
   ELSE
  • If it is ok to proceed, two dynamically generated scripts is built, using the STRING_AGG function again, one for drop the relationships of the table, and other for recreate them after the truncate.
DECLARE 
       @DropRefs    NVARCHAR(MAX)
     , @CreateRefs  NVARCHAR(MAX);
SELECT 
    @DropRefs = STRING_AGG(
                           'ALTER TABLE [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '] DROP CONSTRAINT [' + 
                           [FK_NAME] + 
                           ']', ';')
FROM 
   @FKSTableVar;
SELECT 
    @CreateRefs = STRING_AGG(
                             'ALTER TABLE [' + 
                             [FKTABLE_OWNER] + 
                             '].[' + 
                             [FKTABLE_NAME] + 
                             ']  WITH CHECK ADD  CONSTRAINT ['
                             + 
                             [FK_NAME] + 
                             '] FOREIGN KEY(' + 
                             [FKCOLUMN_NAME] + 
                             ') REFERENCES [' + 
                             [PKTABLE_OWNER] + 
                             '].[' + 
                             [PKTABLE_NAME] + 
                             ']  (' + 
                             [PKCOLUMN_NAME] + 
                             ') ' + 
                                     (CASE [DELETE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON DELETE CASCADE '
                                 ELSE ''
                              END) + 
                                     (CASE [UPDATE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON UPDATE CASCADE '
                                 ELSE ''
                              END), ';')
FROM 
   @FKSTableVar;
  • The script for drop is executed.
        EXEC [sp_executesql] 
             @DropRefs;
END;
  • The script for truncate the table is built and executed.
DECLARE 
       @Truncate  NVARCHAR(MAX) =
                  N'TRUNCATE TABLE [' + 
                  @TableSchema + 
                  '].[' + 
                  @TableName + 
                  ']';
 
EXEC [sp_executesql] 
     @Truncate;
 
  • Finally, in case there were relationships, the script for recreating is executed, finishing the process.
IF @References > 0
  BEGIN --there are relationships
    EXEC [sp_executesql] 
         @CreateRefs;
  END;

Using the sp_executesql procedure

A special note about the use of sp_executesql system store procedure.
It can just execute a string containing a T-SQL script, but it can use parameters.
For that purpose, the parameters must be declared in a second string parameter and passed one by one as the next parameters.
Notice the string containing the script, as well as the parameters declaration MUST BE nvarchars.

The snippet as store procedure.

Of course, you can define this snippet as a stored procedure in your database during the devlopment process, to facilitate the execution.

I propose you to have it in a separate schema, as well as any other «tool» for your development work.

This can be defined by using the two first variables declared in the snippet as parameters of the procedure.

CREATE PROCEDURE [Development].[TruncateX] 
   @TableName    SYSNAME
 , @TableSchema  SYSNAME = NULL
AS
  BEGIN
    SET NOCOUNT ON;
    SET @TableSchema = ISNULL(@TableSchema, 'dbo'); -- assume the dbo schema by default

You can found the snippet and the Stored Procedure at my GitHub repository, SqlSamples.

Custom Identity splitted by other column


Last week, I saw an entry in a social network in Spanish, asking for this specific case:

I need to use a sequential numbering for an Orders table, but it must start in one for each customer. How can I accomplish this?

Of course, it can be done by selecting the max value of the column, filtered by customer, and add 1 to that value.

However, it is a good example to implement a SEQUENCE in T-SQL, as follows:

Consider these two tables:

You must create a new sequence for each new customer added to the database, as follows:

EGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) = replace(
           'CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;

Then, when a new order is inserted, you can get the next value from the sequence, using this code:

BEGIN
  DECLARE 
         @GetSeq  NVARCHAR(MAX) = 
         replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo', 
         '@@', @IdCustomer);
  DECLARE 
         @OrderNo  INT;
  EXEC [sp_executeSQL] 
         @GetSeq
     , N'@OrderNo INT OUTPUT'
     , @OrderNo OUTPUT;
  INSERT INTO [OnLineSales].[Orders]
     (
      [IdCustomer]
    , [OrderNo]
    , [Date]
    , [Total]
   )
  VALUES
     (
      @IdCustomer
    , @OrderNo
    , @Date
    , @Total
   );
END;

This is a simple way to keep separated numbering by other column value.

This is a simple way to keep separated numbering by other column value.

Note
As you can see, both statements samples use variables. In fact, they are parameters, since are implemented as stored procedures.
This is important, since a normal user, not and admin, dbo, or server admin, must not have DDL permissions, and the CREATE SEQUENCE statement is a DDL one.
However, been the Stored Procedure created by an admin, when it is used by another user with EXECUTE permissions, the DDL statement works without raise any error.
It is like the normal user “is impersonated” by the stored procedure’s creator, and the SP runs under the security permissions environment of the SP creator.

Below I include both procedures

/****** Object:  StoredProcedure [dbo].[WebCustomers_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [dbo].[WebCustomers_Add]
 (
   @LastName    NVARCHAR(40)
 , @FirstName   NVARCHAR(30)
 , @Address     NVARCHAR(60)  = NULL
 , @City        NVARCHAR(15)  = NULL
 , @Region      NVARCHAR(15)  = NULL
 , @PostalCode  NVARCHAR(10)  = NULL
 , @CountryId   INT           = 1
 , @Phone       NVARCHAR(24)  = NULL
 , @Fax         NVARCHAR(24)  = NULL
 , @email       NVARCHAR(100) = NULL
 , @LanguageId  INT           = 1
)
AS
  BEGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) =
           replace('CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;
GO
 
/****** Object:  StoredProcedure [OnLineSales].[Orders_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [OnLineSales].[Orders_Add]
 (
   @IdCustomer  INT
 , @Date        SMALLDATETIME
 , @Total       SMALLMONEY
)
AS
  BEGIN
    DECLARE 
           @GetSeq  NVARCHAR(MAX) =
           replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo',
           '@@', 
            @IdCustomer);
    DECLARE 
           @OrderNo  INT;
    EXEC [sp_executeSQL] 
         @GetSeq
       , N'@OrderNo INT OUTPUT'
       , @OrderNo OUTPUT;
    INSERT INTO [OnLineSales].[Orders]
     (
        [IdCustomer]
      , [OrderNo]
      , [Date]
      , [Total]
     )
    VALUES
     (
        @IdCustomer
      , @OrderNo
      , @Date
      , @Total
     );
  END;
GO

Hope this help.

El blog de Dani Seara

Microsoft Azure Blog

El blog de Dani Seara

VIVIENDO RODANDO

de rodar con cámara a rodar en una silla

Matías Iacono

Coding stuff

Leandro Tuttini Blog

El blog de Dani Seara

WindowServer

El blog de los paso a paso

campusMVP.es

El blog de Dani Seara

Angel \"Java\" Lopez on Blog

Software Development, in the Third Millenium

Angel "Java" Lopez

El blog de Dani Seara

Atascado en los 70 II (El regreso)

Segunda época del rockblog "Atascado en los 70". VIEJAS canciones y artistas PASADOS DE MODA. Tratamos al lector de usted y escribimos "rocanrol" y "roquero" con ortografía castellana.

Geeks.ms

El blog de Dani Seara

El Bruno

Innovation Dude

Cajon desastre

Just another WordPress.com site

Pasión por la tecnología...

Todo sobre tecnología Microsoft en general, y Office 365 y SharePoint en partícular...

return(GiS);

Mi sitio geek