Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Les fichiers de données contenant des valeurs d’identité peuvent être importés en bloc dans une instance de Microsoft SQL Server.

Par défaut, les valeurs de la colonne d'identité du fichier de données importé sont ignorées et SQL Server affecte automatiquement des valeurs uniques. Ces valeurs uniques reposent sur les valeurs de départ et d'incrément spécifiées lors de la création de la table.

Si les fichiers de données ne contiennent pas de valeurs pour la colonne d'identificateur de la table, vous devez utiliser un fichier de format pour préciser si la colonne d'identificateur de la table doit être ignorée lors de l'importation de données. Consultez Utiliser un fichier de format pour ignorer une colonne de table (SQL Server) pour plus d’informations.

Conserver les valeurs d’identité

Pour empêcher SQL Server d'assigner des valeurs d'identité lors de l'importation en bloc de lignes de données dans une table, utilisez le qualificateur de commande de conservation d'identité approprié. Lorsque vous spécifiez un qualificateur de conservation d'identité, SQL Server utilise les valeurs d'identité du fichier de données.

Ces qualificateurs sont les suivants :

Commande	Qualificateur de conservation d'identité	Type de qualificateur
bcp	-E	Commutateur
BULK INSERT	KEEPIDENTITY	Raisonnement
INSERT ... SELECT * FROM OPENROWSET(BULK...)	KEEPIDENTITY	Indicateur de table

Pour plus d’informations, consultez l’utilitaire bcp, BULK INSERT (Transact-SQL), OPENROWSET BULK (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) et les indicateurs de table (Transact-SQL).

Remarque

Pour créer un numéro à incrémentation automatique qui peut être utilisé dans plusieurs tables ou être appelé par des applications sans faire référence à une table, consultez Numéros de séquence.

Exemples de conditions de test

Les exemples de cette rubrique sont fondés sur la table, le fichier de données et le fichier de format définis ci-dessous.

Exemple de table

Le script ci-dessous crée une base de données de test et une table nommée myIdentity. Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.myIdentity ( 
   PersonID smallint IDENTITY(1,1) NOT NULL,
   FirstName varchar(25) NOT NULL,
   LastName varchar(30) NOT NULL,
   BirthDate date
   );

Exemple de fichier de données

À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myIdentity.bcp et insérez les données ci-dessous.

3,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
1,Stella,Rosenhain,1992-03-02
4,Miller,Dylan,1954-01-05

Vous pouvez aussi exécuter le script PowerShell suivant pour créer et remplir le fichier de données :

cls
# revise directory as desired
$dir = 'D:\BCP\';

$bcpFile = $dir + 'myIdentity.bcp';

# Confirm directory exists
IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# clear content, will error if file does not exist, can be ignored
Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data
Add-Content -Path $bcpFile -Value '3,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '1,Stella,Rosenhain,1992-03-02';
Add-Content -Path $bcpFile -Value '4,Miller,Dylan,1954-01-05';

#Review content
Get-Content -Path $bcpFile;
Invoke-Item $bcpFile;

Exemple de fichier de format non XML

SQL Server prend en charge deux types de fichier de format : format XML et format non XML. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server. Pour plus d’informations, consultez Utiliser des fichiers de format non XML (SQL Server).

La commande suivante utilise l’utilitaire bcp pour générer un fichier de format non xml myIdentity.fmtbasé sur le schéma de myIdentity.

• Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul au lieu d’un chemin d’accès au fichier de données.
• L’option de format nécessite également l’option -f.
• c est utilisé pour spécifier des données de caractère
• t, est utilisé pour spécifier une virgule comme marque de fin de champ
• T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée.

À partir d'une invite de commandes, entrez la commande suivante :

bcp TestDatabase.dbo.myIdentity format nul -c -f D:\BCP\myIdentity.fmt -t, -T

REM Review file
Notepad D:\BCP\myIdentity.fmt

Important

Vérifiez que votre fichier de format non XML se termine par un retour charriot\saut de ligne. Sinon, vous recevez probablement le message d’erreur suivant :

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

Exemples

Les exemples utilisent la base de données, le fichier de données et les fichiers de format créés dans cet article.

Utiliser bcp et conserver les valeurs d’identité sans fichier de format

Commutateur -E .

À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t, -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Utiliser bcp et conserver des valeurs d’identité avec un fichier de format non XML

Commutateurs -E et -f commutateurs.

À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T -E

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Utiliser bcp et les valeurs d’identité générées sans fichier de format

Utilisation des valeurs par défaut.

À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -T -c -t,

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Utiliser bcp et les valeurs d’identité générées avec un fichier de format non XML

Utilisez les valeurs par défaut et -f le commutateur.

À partir d'une invite de commandes, entrez la commande suivante :

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.myIdentity;"

REM Import data
bcp TestDatabase.dbo.myIdentity IN D:\BCP\myIdentity.bcp -f D:\BCP\myIdentity.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.myIdentity;"

Utiliser BULK INSERT et conserver les valeurs d’identité sans fichier de format

Argument KEEPIDENTITY.

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
    FROM 'D:\BCP\myIdentity.bcp'
    WITH (
        DATAFILETYPE = 'char',  
        FIELDTERMINATOR = ',',  
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Utiliser BULK INSERT et conserver des valeurs d’identité avec un fichier de format non XML

Arguments KEEPIDENTITY et arguments FORMATFILE .

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity; -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt',
        KEEPIDENTITY
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Utiliser BULK INSERT et les valeurs d’identité générées sans fichier de format

Utilisation des valeurs par défaut.

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
      DATAFILETYPE = 'char',  
      FIELDTERMINATOR = ','
      );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Utiliser BULK INSERT et les valeurs d’identité générées avec un fichier de format non XML

Utilisation des valeurs par défaut et FORMATFILE de l’argument.

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
BULK INSERT dbo.myIdentity
   FROM 'D:\BCP\myIdentity.bcp'
   WITH (
        FORMATFILE = 'D:\BCP\myIdentity.fmt'
        );

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Utiliser OPENROWSET BULK et conserver les valeurs d’identité avec un fichier de format non XML

Indicateur KEEPIDENTITY de table et FORMATFILE argument.

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
WITH (KEEPIDENTITY) 
(PersonID, FirstName, LastName, BirthDate)
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Utiliser OPENROWSET BULK et les valeurs d’identité générées avec un fichier de format non XML

Utilisation des valeurs par défaut et de l’argument FORMATFILE .

Exécutez l’instruction Transact-SQL suivant dans Microsoft SQL Server Management Studio (SSMS) :

USE TestDatabase;
GO

TRUNCATE TABLE dbo.myIdentity;  -- for testing
INSERT INTO dbo.myIdentity
(FirstName, LastName, BirthDate)
    SELECT FirstName, LastName, BirthDate
    FROM OPENROWSET (
        BULK 'D:\BCP\myIdentity.bcp', 
        FORMATFILE = 'D:\BCP\myIdentity.fmt'  
        ) AS t1;

-- review results
SELECT * FROM TestDatabase.dbo.myIdentity;

Tâches associées

• Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)

• Préparer des données à une exportation ou une importation en bloc

Pour utiliser un fichier de format

• Créer un fichier de format avec bcp (SQL Server)

• Utiliser un fichier de format pour importer des données en bloc (SQL Server)

• Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)

• Utiliser un fichier de format pour ignorer un champ de données (SQL Server)

• Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)

Pour utiliser des formats de données pour l'importation ou l'exportation en bloc

• Importer des données de format natif et de caractère à partir de versions antérieures de SQL Server

• Utiliser le format caractère pour importer ou exporter des données (SQL Server)

• Utiliser le format natif pour importer ou exporter des données (SQL Server)

• Utiliser le format caractère Unicode pour importer ou exporter des données (SQL Server)

• Utiliser le format natif Unicode pour importer ou exporter des données (SQL Server)

Pour spécifier des formats de données pour la compatibilité lors de l'utilisation de bcp

1. Spécifier les indicateurs de fin de champ et de ligne (SQL Server)

2. Spécifier la longueur du préfixe dans les fichiers de données à l’aide de bcp (SQL Server)

3. Spécifier le type de stockage de fichiers à l’aide de bcp (SQL Server)

Contenu connexe

• BACKUP (Transact-SQL)
• Utilitaire bcp
• BULK INSERT (Transact-SQL)
• OPENROWSET (Transact-SQL)
• Indicateurs de table (Transact-SQL)
• Fichiers de format pour importer ou exporter des données (SQL Server)