Partage via

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

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics 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.

Plan
Conserver les valeurs d’identité
Exemples de conditions de test
 ● Échantillon de table
 ● Exemple de fichier de données
 ● Échantillon de fichier de format non XML
Exemples
 ● Utilisation de la commande bcp et conservation des valeurs d’identité sans fichier de format
 ● Utilisation de la commande bcp et conservation des valeurs d’identité avec un fichier de format non XML
 ● Utilisation de la commande bcp et de valeurs d’identité générées sans fichier de format
 ● Utilisation de la commande bcp et de valeurs d’identité générées avec un fichier de format non XML
 ● Utilisation de BULK INSERT et conservation des valeurs d’identité sans fichier de format
 ● Utilisation de BULK INSERT et conservation des valeurs d’identité avec un fichier de format non XML
 ● Utilisation de BULK INSERT et de valeurs d’identité générées sans fichier de format
 ● Utilisation de BULK INSERT et de valeurs d’identité générées avec un fichier de format non XML
 ● Utilisation d’OPENROWSET et conservation des valeurs d’identité avec un fichier de format non XML
 ● Utilisation d’OPENROWSET et de valeurs d’identité générées avec un fichier de format non XML

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 Switch
BULK INSERT KEEPIDENTITY Argument
INSERT ... SELECT * FROM OPENROWSET(BULK...) KEEPIDENTITY Indicateur de table

Pour plus d’informations, consultez Utilitaire bcp, BULK INSERT (Transact-SQL), OPENROWSET (Transact-SQL), INSERT (Transact-SQL), SELECT (Transact-SQL) et 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
   );

Fichier de données d’exemple

À 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. Veuillez consulter Fichiers de format non XML (SQL Server) pour obtenir des informations détaillées. 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 à la place d’un chemin de fichier de données. L’option format nécessite également l’option -f . 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. À 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 ci-dessous utilisent la base de données, le fichier de données et les fichiers de format créés ci-dessus.

Utilisation de la commande bcp et conservation des 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;"

Utilisation de la commande bcp et conservation des valeurs d’identité avec un fichier de format non XML

Commutateurs-E et -f . À 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;"

Utilisation de la commande bcp et de 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;"

Utilisation de la commande bcp et de valeurs d’identité générées avec un fichier de format non XML

Utilisation des valeurs par défaut et du commutateur -f . À 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;"

Utilisation de BULK INSERT et conservation des valeurs d’identité sans fichier de format

ArgumentKEEPIDENTITY . 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;

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

ArgumentsKEEPIDENTITY et 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;

Utilisation de BULK INSERT et de 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;

Utilisation de BULK INSERT et de 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
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 XML

Indicateur de tableKEEPIDENTITY et 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
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 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

Pour utiliser un fichier de format

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

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

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

  2. Spécifier une longueur de préfixe dans des 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)

Voir aussi

BACKUP (Transact-SQL)
Utilitaire bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Indicateurs de table (Transact-SQL)
Fichiers de format pour l'importation ou l'exportation de données (SQL Server)