INSERT queries

SQL 4 CDS translates INSERT queries to a set of standard create requests that are processed by Dataverse / Dynamics 365. It does not perform any unsupported modifications directly to the underlying SQL database.

The supported syntax is based on SQL Server.

INSERT
    [ INTO ]
    { table_name }
    (
        { column_name  } [ ,...n ]
    )
    {
        VALUES ( { expression } [ ,...n ]) [ ,...n ]
        | { select_statement }
    }
	[ OPTION ( { query_hint } [ ,...n ] ) ]

Note

If you are inserting multiple records it is much more efficient to do so in a single query rather than have multiple queries each inserting a single record - this allows SQL 4 CDS to take advantage of the parallelism featres to insert your records quicker.

Capturing the ID of new records

Dataverse will assign each new record a guid identifier. This is available after executing the INSERT statement in the @@IDENTITY variable.

Unlike in standard SQL Server behavior where this is a numeric variable, in SQL 4 CDS this variable stores an EntityReference type value which contains all the required information to reference the new record in lookup fields on other tables.

You can use this variable directly in subsequent INSERT or UPDATE statements to create links to your new record. Remember though that this variable is overwritten by each INSERT statement, so if you need to use it multiple times you may need to store a copy of the value in your own local variable.

Examples

Simple record insert

INSERT INTO account (name)
VALUES ('Data8')

A new account will be created with the given name. The @@IDENTITY global variable will be set to the ID of the new account.

Multiple inserts

INSERT INTO account (name)
VALUES ('Data8'), ('Data9')

Two new accounts will be created.

Insert with SELECT query

INSERT INTO contact (firstname, lastname)
SELECT firstname, lastname FROM systemuser

New contact records will be created for each user, copying the names for each one.

Inserting into a many-to-many relationship

INSERT INTO listmember (listid, entityid)
SELECT '44C3EAE1-252B-4D20-AD12-74AF0C9981D5', contactid FROM contact WHERE firstname = 'Mark'

All contacts called Mark will be added to the marketing list with the specified ID.

Copy values between instances

INSERT INTO [UAT Instance].dbo.account (name)
SELECT name
FROM   [DEV Instance].dbo.account

Using a cross-instance query, this copies the names of accounts from a development instance to a UAT instance ready for testing.

Ignoring duplicate key errors

INSERT INTO systemuserroles (systemuserid, roleid)
SELECT @NewUser,
       roleid
FROM   systemuserroles
WHERE  systemuserid = @OldUser
OPTION (USE HINT('IGNORE_DUP_KEY'))

The server will normally produce an error if you try to insert a value that already exists, with unique constraints imposed on primary key and alternate key fields. Use the IGNORE_DUP_KEY query hint to ignore any such errors and continue inserting any other records in the same batch.

Creating a new parent record with multiple child records

-- Create the parent record
INSERT INTO account (name)
VALUES ('Data8')

-- Store the ID of the new record
DECLARE @NewAccountId ENTITYREFERENCE = @@IDENTITY

-- Create the first contact in the account
INSERT INTO contact (firstname, parentcustomerid)
VALUES ('Mark', @NewAccountId)

-- Store the ID of the first contact
DECLARE @PrimaryContactId ENTITYREFERENCE = @@IDENTITY

-- Create a second contact
INSERT INTO contact (firstname, parentcustomerid)
VALUES ('Matt', @NewAccountId)

-- Update the account to use the first contact as the primary contact
UPDATE account
SET    primarycontactid = @PrimaryContactId
WHERE  accontid = @NewAccountId