Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)Keep Identity Values When Bulk Importing Data (SQL Server)

Les fichiers de données contenant des valeurs d’identité peuvent être importés en bloc dans une instance de Microsoft SQL Server.Data files that contain identity values can be bulk imported into an instance of Microsoft SQL Server. Par défaut, les valeurs de la colonne d'identité du fichier de données importé sont ignorées et SQL ServerSQL Server affecte automatiquement des valeurs uniques.By default, the values for the identity column in the data file that is imported are ignored and SQL ServerSQL Server assigns unique values automatically. 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.The unique values are based on the seed and increment values that are specified during table creation.

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.If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. Consultez Utiliser un fichier de format pour ignorer une colonne de table (SQL Server) pour plus d’informations.See Use a Format File to Skip a Table Column (SQL Server) for additional information.

ContourOutline
Conserver les valeurs d’identitéKeep Identity Values
Exemples de conditions de testExample Test Conditions
 ● Exemple de table ● Sample Table
 ● Exemple de fichier de données ● Sample Data File
 ● Exemple de fichier de format non XML ● Sample Non-XML Format File
ExemplesExamples
 ● Utilisation de la commande bcp et conservation des valeurs d’identité sans fichier de format ● Using bcp and Keeping Identity Values without a Format File
 ● Utilisation de la commande bcp et conservation des valeurs d’identité avec un fichier de format non XML ● Using bcp and Keeping Identity Values with a Non-XML Format File
 ● Utilisation de la commande bcp et de valeurs d’identité générées sans fichier de format ● Using bcp and Generated Identity Values without a Format File
 ● Utilisation de la commande bcp et de valeurs d’identité générées avec un fichier de format non XML ● Using bcp and Generated Identity Values with a Non-XML Format File
 ● Utilisation de BULK INSERT et conservation des valeurs d’identité sans fichier de format ● Using BULK INSERT and Keeping Identity Values without a Format File
 ● Utilisation de BULK INSERT et conservation des valeurs d’identité avec un fichier de format non XML ● Using BULK INSERT and Keeping Identity Values with a Non-XML Format File
 ● Utilisation de BULK INSERT et de valeurs d’identité générées sans fichier de format ● Using BULK INSERT and Generated Identity Values without a Format File
 ● Utilisation de BULK INSERT et de valeurs d’identité générées avec un fichier de format non XML ● Using BULK INSERT and Generated Identity Values with a Non-XML Format File
 ● Utilisation d’OPENROWSET et conservation des valeurs d’identité avec un fichier de format non XML ● Using OPENROWSET and Keeping Identity Values with a Non-XML Format File
 ● Utilisation d’OPENROWSET et de valeurs d’identité générées avec un fichier de format non XML ● Using OPENROWSET and Generated Identity Values with a Non-XML Format File

Conserver les valeurs d’identité Keep Identity Values

Pour empêcher SQL ServerSQL 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é.To prevent SQL ServerSQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. Lorsque vous spécifiez un qualificateur de conservation d'identité, SQL ServerSQL Server utilise les valeurs d'identité du fichier de données.When you specify a keep-identity qualifier, SQL ServerSQL Server uses the identity values in the data file. Ces qualificateurs sont les suivants :These qualifiers are as follows:

CommandCommand Qualificateur de conservation d'identitéKeep-identity qualifier Type de qualificateurQualifier type
bcpbcp -E-E CommutateurSwitch
BULK INSERTBULK INSERT KEEPIDENTITYKEEPIDENTITY ArgumentArgument
INSERT ... SELECT * FROM OPENROWSET(BULK...)INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITYKEEPIDENTITY Indicateur de tableTable hint

Pour plus d’informations, consultez bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) et Indicateurs de table (Transact-SQL).For more information, see bcp Utility, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL), and Table Hints (Transact-SQL).

Note

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.To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Exemples de conditions de testExample Test Conditions

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.The examples in this topic are based on the table, data file, and format file defined below.

Exemple de tableSample Table

Le script ci-dessous crée une base de données de test et une table nommée myIdentity.The script below creates a test database and a table named myIdentity. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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éesSample Data File

À l’aide du Bloc-notes, créez un fichier vide D:\BCP\myIdentity.bcp et insérez les données ci-dessous.Using Notepad, create an empty file D:\BCP\myIdentity.bcp and insert the data below.

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 :Alternatively, you can execute the following PowerShell script to create and populate the data file:

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 XMLSample Non-XML Format File

SQL Server prend en charge deux types de fichier de format : format XML et format non XML.SQL Server support two types of format file: non-XML format and XML format. Le format non XML est le format d’origine pris en charge dans les versions précédentes de SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server. Veuillez consulter Fichiers de format non XML (SQL Server) pour obtenir des informations détaillées.Please review Non-XML Format Files (SQL Server) for detailed information. 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.The following command will use the bcp utility to generate a non-xml format file, myIdentity.fmt, based on the schema of myIdentity. Pour utiliser une commande bcp pour créer un fichier de format, spécifiez l’argument format et utilisez nul à la place d’un chemin de fichier de données.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L’option format nécessite également l’option -f .The format option also requires the -f option. Pour cet exemple, le qualificateur c est utilisé pour spécifier les données de caractère, t est utilisé pour spécifier une virgule comme délimiteur de champ, et T est utilisé pour spécifier une connexion approuvée à l’aide de la sécurité intégrée.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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.Ensure your non-XML format file ends with a carriage return\line feed. Sinon, vous recevez probablement le message d’erreur suivant :Otherwise you will likely receive the following error message:

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

ExemplesExamples

Les exemples ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.The examples below use the database, datafile, and format files created above.

Utilisation de la commande bcp et conservation des valeurs d’identité sans fichier de formatUsing bcp and Keeping Identity Values without a Format File

Commutateur-E .-E switch. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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;"

Utilisation de la commande bcp et conservation des valeurs d’identité avec un fichier de format non XMLUsing bcp and Keeping Identity Values with a Non-XML Format File

Commutateurs-E et -f .-E and -f switches. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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;"

Utilisation de la commande bcp et de valeurs d’identité générées sans fichier de formatUsing bcp and Generated Identity Values without a Format File

Utilisation des valeurs par défaut.Using defaults. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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;"

Utilisation de la commande bcp et de valeurs d’identité générées avec un fichier de format non XMLUsing bcp and Generated Identity Values with a Non-XML Format File

Utilisation des valeurs par défaut et du commutateur -f .Using defaults and -f switch. À partir d'une invite de commandes, entrez la commande suivante :At a command prompt, enter the following command:

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;"

Utilisation de BULK INSERT et conservation des valeurs d’identité sans fichier de formatUsing BULK INSERT and Keeping Identity Values without a Format File

ArgumentKEEPIDENTITY .KEEPIDENTITY argument. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Utilisation de BULK INSERT et conservation des valeurs d’identité avec un fichier de format non XMLUsing BULK INSERT and Keeping Identity Values with a Non-XML Format File

ArgumentsKEEPIDENTITY et FORMATFILE .KEEPIDENTITY and the FORMATFILE argument. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Utilisation de BULK INSERT et de valeurs d’identité générées sans fichier de formatUsing BULK INSERT and Generated Identity Values without a Format File

Utilisation des valeurs par défaut.Using defaults. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Utilisation de BULK INSERT et de valeurs d’identité générées avec un fichier de format non XMLUsing BULK INSERT and Generated Identity Values with a Non-XML Format File

Utilisation des valeurs par défaut et de l’argument FORMATFILE .Using defaults and FORMATFILE argument. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Utilisation d’ OPENROWSET(BULK...) et conservation des valeurs d’identité avec un fichier de format non XMLUsing OPENROWSET(BULK...) and Keeping Identity Values with a Non-XML Format File

Indicateur de tableKEEPIDENTITY et argument FORMATFILE .KEEPIDENTITY table hint and FORMATFILE argument. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;

Utilisation d’ OPENROWSET(BULK...) et de valeurs d’identité générées avec un fichier de format non XMLUsing OPENROWSET(BULK...) and Generated Identity Values with a Non-XML Format File

Utilisation des valeurs par défaut et de l’argument FORMATFILE .Using defaults and FORMATFILE argument. Exécutez l’instruction Transact-SQL suivante dans Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS) :Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL 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;
  1. Spécifier des indicateurs de fin de champ et de fin de ligne (SQL Server)Specify Field and Row Terminators (SQL Server)

  2. Spécifier une longueur de préfixe dans des fichiers de données à l’aide de bcp (SQL Server)Specify Prefix Length in Data Files by Using bcp (SQL Server)

  3. Spécifier le type de stockage de fichiers à l’aide de bcp (SQL Server)Specify File Storage Type by Using bcp (SQL Server)

Voir aussiSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Indicateurs de table (Transact-SQL) Table Hints (Transact-SQL)
Fichiers de format pour l'importation ou l'exportation de données (SQL Server)Format Files for Importing or Exporting Data (SQL Server)