An gewissen Punkten mssen Sie die Einschrnkungen in der gesamten Tabelle berprfen. At some point, you must examine the constraints on the whole table. BULK INSERT Transact SQL Microsoft Docs. DIESES THEMA GILT FR SQL Server ab 2. Azure SQL Datenbank. Azure SQL Data Warehouse Parallel Data Warehouse THIS TOPIC APPLIES TO SQL Server starting with 2. Azure SQL Database. Azure SQL Data Warehouse Parallel Data Warehouse Importiert eine Datendatei in eine Datenbanktabelle oder Sicht und verwendet dabei ein vom Benutzer angegebenes Format in SQL Server. SQL Server. Imports a data file into a database table or view in a user specified format in SQL Server. SQL Server. Transact SQL Syntax Conventions Transact SQL SyntaxkonventionenTransact SQL Syntax Conventions. Syntax. Syntax. databasename. Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />FROM datafile. BATCHSIZE batchsize. CHECKCONSTRAINTS. CODEPAGE ACP OEM RAW codepage. DATAFILETYPE. char native widechar widenative. DATASOURCE datasourcename. ERRORFILE filename. ERRORFILEDATASOURCE datasourcename. FIRSTROW firstrow. Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />FIRETRIGGERS., FORMATFILEDATASOURCE datasourcename. KEEPIDENTITY., KEEPNULLS. KILOBYTESPERBATCH kilobytesperbatch. LASTROW lastrow. MAXERRORS maxerrors. ORDER column ASC DESC. ROWSPERBATCH rowsperbatch. ROWTERMINATOR rowterminator. SR1lU9zLUT.png' alt='Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />TABLOCK. FORMAT CSV. FIELDQUOTE quotecharacters. FORMATFILE formatfilepath. OPENROWSET is a TSQL function that allows for reading data from many sources including using the SQL Servers BULK import capability. One of the useful features of. We provide database converters for MySQL, MSSQL, Oracle, MS Access, Foxpro, DBFs and CSV Fixed Width Text files. Importing and modifying Active Directory users with AD Bulk Users Importing users into Active Directory CSV, Excel The import file can be formatted using the CSV. Selects data from a table or multiple tables. GROUP BY groups the the result by the given expressions. HAVING filter rows after grouping. ORDER BY sorts the result. FIELDTERMINATOR fieldterminator. ROWTERMINATOR rowterminator. Argumente. Argumentsdatabasenamedatabasename. Der Name der Datenbank, in der sich die angegebene Tabelle oder Sicht befindet. Is the database name in which the specified table or view resides. Fehlt die Angabe, ist dies die aktuelle Datenbank. If not specified, this is the current database. Der Name der Tabelle oder des Sichtschemas. Is the name of the table or view schema. Schemaname ist optional, wenn das Standardschema des Benutzers den Massenimportvorgang ausfhrt Schema der angegebenen Tabelle oder Sicht ist. Wenn Schema nicht angegeben wird und das Standardschema des Benutzers, der den Massenimportvorgang durchfhren unterscheidet sich von der angegebenen Tabelle oder Sicht, SQL Server. SQL Server gibt eine Fehlermeldung angezeigt, und der Massenimportvorgang wird abgebrochen. If schema is not specified and the default schema of the user performing the bulk import operation is different from the specified table or view, SQL Server. SQL Server returns an error message, and the bulk import operation is canceled. Der Name der Tabelle oder Sicht, in die der Massenimport von Daten erfolgen sollen. Is the name of the table or view to bulk import data into. Es knnen nur Sichten verwendet werden, deren Spalten alle auf dieselbe Basistabelle verweisen. Only views in which all columns refer to the same base table can be used. Weitere Informationen zu den Einschrnkungen beim Laden von Daten in Sichten, finden Sie unter INSERT Transact SQL. For more information about the restrictions for loading data into views, see INSERT Transact SQL. DatafiledatafileDer vollstndige Pfad der Datendatei mit den Daten, die in die angegebene Tabelle oder Sicht importiert werden sollen. Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT kann Daten von einem Datentrger importieren, einschlielich eines Netzwerks, einer Diskette, einer Festplatte usw. BULK INSERT can import data from a disk including network, floppy disk, hard disk, and so on. Datafile muss einen gltigen Pfad auf dem Server angeben, auf dem SQL Server. SQL Server ausgefhrt wird. SQL Server. SQL Server is running. Wenn Datafile ist eine Datei, geben Sie den Namen UNC Universal Naming Convention. If datafile is a remote file, specify the Universal Naming Convention UNC name. Ein UNC Name weist folgendes Format SystemnameShare. NamePfad File. Name. A UNC name has the form SystemnameShare. NamePathFile. Name. Beispiel System. XDisk. ZSalesupdate. For example, System. XDisk. ZSalesupdate. Gilt fr SQL Server 2. SQL Server 2. 01. CTP 1. 1. Applies to SQL Server 2. SQL Server 2. 01. CTP 1. 1. Beginnend mit SQL Server 2. SQL Server 2. 01. CTP1. 1, kann die Datafile im Azure Blob Speicher. Beginning with SQL Server 2. SQL Server 2. 01. CTP1. 1, the datafile can be in Azure blob storage. DatasourcenamedatasourcenameGilt fr SQL Server 2. SQL Server 2. 01. CTP 1. 1. Eine benannte externe Datenquelle auf den Azure Blob Speicherort der Datei verweist, die importiert werden sollen. Is a named external data source pointing to the Azure Blob storage location of the file that will be imported. Die externen Datenquelle muss erstellt werden, mithilfe der TYPE BLOBSTORAGE hinzugefgt, die Option SQL Server 2. SQL Server 2. 01. CTP Version 1. 1. The external data source must be created using the TYPE BLOBSTORAGE option added in SQL Server 2. SQL Server 2. 01. CTP 1. 1. Weitere Informationen finden Sie unter CREATE EXTERNAL DATA SOURCE. For more information, see CREATE EXTERNAL DATA SOURCE. BATCHSIZE Batchsize. BATCHSIZE batchsize. Gibt die Anzahl von Zeilen in einem Batch an. Specifies the number of rows in a batch. Jeder Batch wird als eine Transaktion auf den Server kopiert. Each batch is copied to the server as one transaction. Falls ein Fehler erzeugt wird, fhrt SQL Server. SQL Server fr die Transaktion jedes Batches ein Commit oder Rollback aus. If this fails, SQL Server. SQL Server commits or rolls back the transaction for every batch. In der Standardeinstellung werden alle Daten, die sich in der angegebenen Datendatei befinden, als ein Batch behandelt. By default, all data in the specified data file is one batch. Informationen zu Leistungsaspekten finden Sie unter Hinweise weiter unten in diesem Thema. For information about performance considerations, see Remarks, later in this topic. CHECKCONSTRAINTSCHECKCONSTRAINTSGibt an, dass alle Einschrnkungen, die fr die Zieltabelle oder sicht gelten, whrend des Massenimportvorgangs berprft werden mssen. Specifies that all constraints on the target table or view must be checked during the bulk import operation. Ohne die Option CHECKCONSTRAINTS werden alle CHECK und FOREIGN KEY Einschrnkungen ignoriert, und nach Abschluss des Vorgangs wird die Einschrnkung in der Tabelle als nicht vertrauenswrdig gekennzeichnet. Without the CHECKCONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not trusted. Hinweis. Unique und PRIMARY KEY Einschrnkungen werden immer erzwungen. UNIQUE, and PRIMARY KEY constraints are always enforced. Beim Importieren in eine Zeichenspalte, die mit einer NOT NULL Einschrnkung definiert ist, fgt BULK INSERT eine leere Zeichenfolge ein, wenn die Textdatei keinen Wert enthlt. When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts a blank string when there is no value in the text file. An gewissen Punkten mssen Sie die Einschrnkungen in der gesamten Tabelle berprfen. At some point, you must examine the constraints on the whole table. Wenn die Tabelle vor dem Massenimportvorgang nicht leer war, kann der Aufwand einer erneuten berprfung der Einschrnkung hher sein als das Anwenden von CHECK Einschrnkungen auf die inkrementellen Daten. Loading data files with SQL Servers BULK INSERT statement. In my previous tip, I discussed how to use the bcp utility in SQL Server to bulk copy data from a file into a table. By submitting your personal information, you agree that Tech. Target and its partners may contact you regarding relevant content, products and special offers. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy. In this tip, I discuss the BULK INSERT statement, which is a Transact SQL statement that lets you bulk load a data file into a SQL Server database. I also provide several examples of BULK INSERT statements to copy data into a SQL Server table. These examples have been tested in SQL Server 2. SQL Server 2. 00. I used the Adventure. Works sample database. To run the examples in this tip, I first used the following bcp command to create my source data file bcp SELECT Employee. ID, First. Name, Last. Name, Job. Title FROMAdventure. Works. Human. Resources. Employee WHERE Employee. ID 1 queryout. C DataEmployees. T t,The command retrieves data from the v. Employee view in the Adventure. Works database and saves it to the C DataEmployees. Next, I used the following T SQL script to create the Employees table in the Adventure. Works database USE Adventure. Works. GOIF OBJECTID NEmployees, NU IS NOT NULLDROP TABLE dbo. Employees. GOCREATE TABLE dbo. Employees Employee. ID INT IDENTITY PRIMARY KEY,First. Name NVARCHAR5. 0 NOT NULL,Last. Name NVARCHAR5. 0 NOT NULL,Job. Title NVARCHAR5. NOT NULL,CONSTRAINT ck. Job. Title CHECK Job. Title lt Marketing Technician Note that, in the table definition, the Employee. ID column is configured as an IDENTITY column and the Job. Title column is configured with a check constraint. Ive included both components to demonstrate the options supported by the BULK INSERT statement. The syntax for the BULK INSERT statement is straightforward BULK INSERT Table. View FROM Data. File WITH option ,. As you can see, the BULK INSERT keywords are followed by the name of the table or view. That name should be qualified with the database and schema names as necessary. Following the name is the FROM clause, which specifies the path and file name of the data file. After the FROM clause, you should include any of the appropriate options supported by the BULK INSERT statement. The following statement provides an example of the various elements included in the syntax BULK INSERT Adventure. Works. dbo. Employees. FROM C DataEmployees. WITHFIELDTERMINATOR ,This statement retrieves data from the C DataEmployees. Employees table in the Adventure. What Is Another Word For Program more. Game Pokemon Vs Digimon Ds. Works database. First, I defined the target table in the BULK INSERT clause. In this case, I used the tables fully qualified name. Next, I specified the file name in the FROM clause. Note that I enclosed the file name in single quotes, so it is treated as a string value. The next step is to add the WITH clause, which includes the FIELDTERMINATOR option enclosed in parentheses. Because the source file is a comma delimited file, I must specify that each field is terminated with a comma. I do so by including a comma enclosed in single quotes after the equal sign. Without specifying the FIELDTERMINATOR option for a. BULK INSERT statement would not know how to retrieve the data and the statement would fail. These are the general steps for using a SQL Server BULK INSERT statement. But the statement supports nearly 2. Ive shown here. For instance, the following BULK INSERT statement includes two options FIELDTERMINATOR and CHECKCONSTRAINTS BULK INSERT Adventure. Works. dbo. Employees. FROM C DataEmployees. WITHFIELDTERMINATOR ,CHECKCONSTRAINTSUnlike the FIELDTERMINATOR option, the CHECKCONSTRAINTS option takes no arguments. It simply tells the BULK INSERT statement to enforce all CHECK constraints on the table or view before inserting data. Otherwise, these constraints are not enforced. Note that PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL constraints are always enforced. If the CHECKCONSTRAINTS option is included in the BULK INSERT statement, the statement will fail when a CHECK constraint is violated. You may have noticed in the preceding example that, if you include multiple options in the WITH clause, you must separate these options with a comma. The following BULK INSERT statement, for example, builds on the previous example by adding the KEEPIDENTITY option BULK INSERT Adventure. Works. dbo. Employees. FROM C DataEmployees. WITHFIELDTERMINATOR ,CHECKCONSTRAINTS,KEEPIDENTITYThe KEEPIDENTITY option specifies that identity values should be preserved when inserted into the target table. If you were to run the previous examples, you might notice that new identity values were inserted for the Employee. ID column. By using KEEPIDENTITY, however, the Employee. ID values saved to the Employees. In addition to the data focused options shown in the examples above, the BULK INSERT statement also supports options that control the batch load process. The following statement, for example, includes the BATCHSIZE option, which specifies the number of rows in a batch BULK INSERT Adventure. Works. dbo. Employees. FROM C DataEmployees. WITHFIELDTERMINATOR ,CHECKCONSTRAINTS,KEEPIDENTITY,BATCHSIZE 5. When you run this statement, the rows are inserted into the target table in batches of 5. You can verify this in the messages returned by the query. The preceding statement returns the following messages 5. As you can see, the data is now loaded in 5. As mentioned previously, the BULK INSERT statement doe SQL Server 2. You can find details about each option by referring to the BULK INSERT Transact SQL topic in SQL Server Books Online. The topic also discusses how to call a format file when bulk loading your data. A format file is useful when the data file contains a different number of columns than does your target table the columns are in a different order, the column delimiters vary or there are anomalies in the data format. For a basic BULK INSERT operation, however, the examples above should provide many of the details you need to get started loading data from various types of data files. ABOUT THE AUTHORRobert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http www.