webentwicklung-frage-antwort-db.com.de

BULK INSERT mit Identitätsspalte (Autoinkrement)

Ich versuche, Massendaten in der Datenbank aus der CSV-Datei hinzuzufügen.

Die Employee-Tabelle hat eine Spalte ID (PK), die automatisch inkrementiert wird.

CREATE TABLE [dbo].[Employee](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [Address] [varchar](50) NULL
) ON [PRIMARY]

Ich verwende diese Abfrage:

BULK INSERT Employee  FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,KEEPIDENTITY,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

.CSV-Datei -

Name,Address
name1,addr test 1
name2,addr test 2

aber es führt zu dieser Fehlermeldung:

Konvertierungsfehler für Massenladedaten (Typkonflikt oder ungültiges Zeichen für die angegebene Codepage) für Zeile 2, Spalte 1 (ID).

58
Abhi

BULK INSERT nicht direkt in Ihre real Tabellen.

Ich würde es immer tun 

  1. einfügen in eine staging table dbo.Employee_Staging (ohne die IDENTITY-Spalte) aus der CSV-Datei
  2. möglicherweise bearbeiten/bereinigen/bearbeiten Sie Ihre importierten Daten
  3. und kopieren Sie dann die Daten mit einer T-SQL-Anweisung in die reale Tabelle:

    INSERT INTO dbo.Employee(Name, Address) 
       SELECT Name, Address
       FROM dbo.Employee_Staging
    
39
marc_s

Fügen Sie der CSV-Datei eine ID-Spalte hinzu und lassen Sie sie leer.

id,Name,Address
,name1,addr test 1
,name2,addr test 2

KEEPIDENTITY-Schlüsselwort aus der Abfrage entfernen:

BULK INSERT Employee  FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');

Das ID-Identifizierungsfeld wird automatisch inkrementiert.

Wenn Sie dem Feld id im csv Werte zuweisen, werden diese ignoriert, es sei denn, Sie verwenden das Schlüsselwort KEEPIDENTITY. Dann werden sie anstelle von auto-increment verwendet.

89
Josh Werts

Ich hatte ein ähnliches Problem, aber ich musste sicherstellen, dass die Reihenfolge der ID mit der Reihenfolge in der Quelldatei übereinstimmt .. Meine Lösung verwendet einen VIEW für die BULK INSERT

Behalten Sie Ihre Tabelle bei und erstellen Sie diese VIEW (wählen Sie alles außer der ID-Spalte aus)

CREATE VIEW [dbo].[VW_Employee]
AS
SELECT [Name], [Address]
FROM [dbo].[Employee];

Ihr BULK INSERT sollte dann so aussehen:

BULK INSERT [dbo].[VW_Employee] FROM 'path\tempFile.csv ' 
WITH (FIRSTROW = 2,FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n');
23
Paul_S

Sie müssen Bulk Insert mit Formatdatei machen:

   BULK INSERT Employee FROM 'path\tempFile.csv ' 
   WITH (FORMATFILE = 'path\tempFile.fmt');

die Formatdatei (tempFile.fmt) sieht folgendermaßen aus:

11,0
2
1 SQLCHAR 0 50 "\ t" 2 Name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\ r\n" 3 Adresse SQL_Latin1_General_CP1_CI_AS

weitere Details hier - http://msdn.Microsoft.com/de-de/library/ms179250.aspx

6
hotfusion

Meine Lösung besteht darin, das ID-Feld als LAST-Feld in die Tabelle einzufügen. Daher wird das Bulk-Insert ignoriert und es werden automatische Werte angezeigt. Sauber und einfach ...

Wenn Sie zum Beispiel in eine temporäre Tabelle einfügen:

CREATE TABLE #TempTable 
(field1 varchar(max), field2 varchar(max), ... 
ROW_ID int IDENTITY(1,1) NOT NULL)

Beachten Sie, dass das Feld ROW_ID immer als letztes Feld angegeben werden muss!

2
Langbaba
  1. Erstellen Sie eine Tabelle mit Identitätsspalte + anderen Spalten.
  2. Erstellen Sie eine Ansicht darüber und machen Sie nur die Spalten sichtbar, die Sie massenweise einfügen.
  3. BCP in der Ansicht
0
user10513964

Ich hatte genau das gleiche Problem, aus dem Ausfallzeiten entstanden. Daher bin ich inspiriert, meine Erkenntnisse und Lösungen mitzuteilen, die für mich funktionierten.

1. Verwenden Sie eine Excel-Datei

Dies ist der Ansatz, den ich gewählt habe. Anstelle einer csv-Datei habe ich eine Excel-Datei (.xlsx) mit dem folgenden Inhalt verwendet.

id  username   email                token website

    johndoe   [email protected]        divostar.com
    bobstone  [email protected]        divosays.com

Beachten Sie, dass die ID-Spalte keinen Wert hat. 

Stellen Sie als Nächstes eine Verbindung zu Ihrer Datenbank mit Microsoft SQL Server Management Studio her, klicken Sie mit der rechten Maustaste auf Ihre Datenbank, und wählen Sie Daten importieren aus (Untermenü unter Aufgabe). Wählen Sie als Quelle Microsoft Excel aus. Wenn Sie in der Phase "Select Source Tables and Views" angekommen sind, klicken Sie auf Zuordnungen bearbeiten . Klicken Sie für die Spalte id unter destination darauf und wählen Sie ignore aus. Aktivieren Sie nicht Enable Identity insert, es sei denn, Sie möchten IDs-Vorfälle festlegen, in denen Sie Daten aus einer anderen Datenbank importieren und die Auto-Inkrement-ID der Quelldatenbank beibehalten möchten. Fahren Sie fort, um zu beenden und das ist es. Ihre Daten werden problemlos importiert.

2. CSV-Datei verwenden

Stellen Sie in Ihrer CSV-Datei sicher, dass Ihre Daten wie folgt sind.

id,username,email,token,website
,johndoe,[email protected],,divostar.com
,bobstone,[email protected],,divosays.com

Führen Sie die folgende Abfrage aus:

BULK INSERT Metrics FROM 'D:\Data Management\Data\CSV2\Production Data 2004 - 2016.csv '
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

Das Problem bei diesem Ansatz ist, dass sich der CSV im DB-Server oder in einem freigegebenen Ordner befinden muss, auf den die Datenbank zugreifen kann. Andernfalls erhalten Sie die Fehlermeldung "Datei kann nicht geöffnet werden. Das Betriebssystem hat den Fehlercode 21 (Das Gerät ist nicht bereit) angezeigt ) ".

Wenn Sie eine Verbindung zu einer entfernten Datenbank herstellen, können Sie Ihre CSV-Datei in ein Verzeichnis auf diesem Server hochladen und auf den Pfad in der Masseneinfügung verweisen.

3. CSV-Datei und Microsoft SQL Server Management Studio-Importoption verwenden

Starten Sie Ihre Importdaten wie beim ersten Ansatz. Wählen Sie als Quelle Flat File Source aus und suchen Sie nach Ihrer CSV-Datei. Stellen Sie sicher, dass das rechte Menü (Allgemein, Spalten, Erweitert, Vorschau) in Ordnung ist. Stellen Sie sicher, dass Sie das richtige Trennzeichen im Spaltenmenü (Spaltenbegrenzer) festlegen. Klicken Sie wie in Excel oben auf Mappings bearbeiten . Für die ID-Spalte unter destination klicken Sie darauf und wählen Sie ignore .

Fahren Sie fort, um zu beenden und das ist es. Ihre Daten werden problemlos importiert.

0
Fokwa Best

Wenn Sie temporäre Tabellen anstelle von Staging-Tabellen verwenden, können Sie die temporäre Tabelle wie von Ihrem Import erwartet erstellen und nach dem Import die Identitätsspalte hinzufügen.

Ihr SQL macht also so etwas: 

  1. Wenn eine temporäre Tabelle vorhanden ist, löschen Sie sie
  2. Erstellen Sie eine temporäre Tabelle
  3. Massenimport in temporäre Tabelle
  4. Ändern Sie die temporäre Tabelle, um die Identität hinzuzufügen
  5. <was auch immer Sie mit den Daten machen wollen>
  6. Lassen Sie die Temp-Tabelle fallen

Immer noch nicht sehr sauber, aber es ist eine andere Option ... möglicherweise müssen Schlösser auch sicher sein. 

0
Izzy

Dies ist ein sehr alter Beitrag, der zu beantworten ist, aber keine der gegebenen Antworten löst das Problem, ohne die gestellten Bedingungen zu ändern, was ich nicht tun kann.

Ich habe es mit der OPENROWSET-Variante von BULK INSERT gelöst. Dies verwendet dieselbe Formatdatei und funktioniert auf die gleiche Weise, jedoch kann die Datendatei mit einer SELECT-Anweisung gelesen werden.

Erstellen Sie Ihren Tisch:

CREATE TABLE target_table(
id bigint IDENTITY(1,1),
col1 varchar(256) NULL,
col2 varchar(256) NULL,
col3 varchar(256) NULL)

Öffnen Sie ein Befehlsfenster und führen Sie einen Lauf aus:

bcp dbname.dbo.target_table format nul -c -x -f C:\format_file.xml -t; -T

Dadurch wird die Formatdatei basierend auf dem Aussehen der Tabelle erstellt.

Bearbeiten Sie nun die Formatdatei und entfernen Sie die gesamten Zeilen, in denen FIELD ID = "1" und COLUMN SOURCE = "1" sind, da dies in unserer Datendatei nicht vorhanden ist.
Passen Sie auch die Abschlusszeichen an, die für Ihre Datendatei erforderlich sind:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.Microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="256" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="col1" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="col2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="col3" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Jetzt können wir die Datendatei mit einem select in unsere Tabelle laden und haben somit die vollständige Kontrolle über die Spalten. In diesem Fall fügen Sie keine Daten in die Identitätsspalte ein:

INSERT INTO target_table (col1,col2, col3)
SELECT * FROM  openrowset(
bulk 'C:\data_file.txt',
formatfile='C:\format_file.xml') as t;
0
whetstone