search

microsoft / sql-server-samples

Azure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge
Other
9.98k stars 8.86k forks source link

Error @ Bulk Insert #743

Open AsimKhan2019 opened 4 years ago

AsimKhan2019 commented 4 years ago

Hi, While I was trying to run the script to install Adventure Works DB, I came across an error when Bulk Insert script tries to run, as it had an issue with the columns and rows not being properly identified or terminated.

So the work around that I used was a very lame one, but it worked. I just replaced the following line for some tables but not all.

ROWTERMINATOR = '\n', (replaced '\n' with '0x0a') This workaround worked for me.

For other tables where

ROWTERMINATOR='&|\n', (this line worked)

So just in case, anyone came across with bulk error, they can use this workaround.

martindanielsalkeiz commented 4 years ago

Here is which ones for me: `BULK INSERT [Person].[Address] FROM '$(SqlSamplesSourceDataPath)Address.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE = 'char', FIELDTERMINATOR= '\t', ROWTERMINATOR = '0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[AddressType]';

BULK INSERT [Person].[AddressType] FROM '$(SqlSamplesSourceDataPath)AddressType.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE = 'char', FIELDTERMINATOR= '\t', ROWTERMINATOR = '0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [dbo].[AWBuildVersion]';

INSERT INTO [dbo].[AWBuildVersion] VALUES ( CONVERT(nvarchar(25), SERVERPROPERTY('ProductVersion')), CONVERT(datetime, SERVERPROPERTY('ResourceLastUpdateDateTime')), CONVERT(datetime, GETDATE()) );

PRINT 'Loading [Production].[BillOfMaterials]';

BULK INSERT [Production].[BillOfMaterials] FROM '$(SqlSamplesSourceDataPath)BillOfMaterials.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE = 'char', FIELDTERMINATOR= '\t', ROWTERMINATOR = '0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[BusinessEntity]';

BULK INSERT [Person].[BusinessEntity] FROM '$(SqlSamplesSourceDataPath)BusinessEntity.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[BusinessEntityAddress]';

BULK INSERT [Person].[BusinessEntityAddress] FROM '$(SqlSamplesSourceDataPath)BusinessEntityAddress.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[BusinessEntityContact]';

BULK INSERT [Person].[BusinessEntityContact] FROM '$(SqlSamplesSourceDataPath)BusinessEntityContact.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[ContactType]';

BULK INSERT [Person].[ContactType] FROM '$(SqlSamplesSourceDataPath)ContactType.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[CountryRegion]';

BULK INSERT [Person].[CountryRegion] FROM '$(SqlSamplesSourceDataPath)CountryRegion.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[CountryRegionCurrency]';

BULK INSERT [Sales].[CountryRegionCurrency] FROM '$(SqlSamplesSourceDataPath)CountryRegionCurrency.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[CreditCard]';

BULK INSERT [Sales].[CreditCard] FROM '$(SqlSamplesSourceDataPath)CreditCard.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[Culture]';

BULK INSERT [Production].[Culture] FROM '$(SqlSamplesSourceDataPath)Culture.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[Currency]';

BULK INSERT [Sales].[Currency] FROM '$(SqlSamplesSourceDataPath)Currency.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[CurrencyRate]';

BULK INSERT [Sales].[CurrencyRate] FROM '$(SqlSamplesSourceDataPath)CurrencyRate.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[Customer]';

BULK INSERT [Sales].[Customer] FROM '$(SqlSamplesSourceDataPath)Customer.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[Department]';

BULK INSERT [HumanResources].[Department] FROM '$(SqlSamplesSourceDataPath)Department.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[Document]';

BULK INSERT [Production].[Document] FROM '$(SqlSamplesSourceDataPath)Document.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK
);

PRINT 'Loading [Person].[EmailAddress]';

BULK INSERT [Person].[EmailAddress] FROM '$(SqlSamplesSourceDataPath)EmailAddress.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[Employee]';

BULK INSERT [HumanResources].[Employee] FROM '$(SqlSamplesSourceDataPath)Employee.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[EmployeeDepartmentHistory]';

BULK INSERT [HumanResources].[EmployeeDepartmentHistory] FROM '$(SqlSamplesSourceDataPath)EmployeeDepartmentHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[EmployeePayHistory]';

BULK INSERT [HumanResources].[EmployeePayHistory] FROM '$(SqlSamplesSourceDataPath)EmployeePayHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[Illustration]';

BULK INSERT [Production].[Illustration] FROM '$(SqlSamplesSourceDataPath)Illustration.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[JobCandidate]';

BULK INSERT [HumanResources].[JobCandidate] FROM '$(SqlSamplesSourceDataPath)JobCandidate.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[Location]';

BULK INSERT [Production].[Location] FROM '$(SqlSamplesSourceDataPath)Location.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[Password]';

BULK INSERT [Person].[Password] FROM '$(SqlSamplesSourceDataPath)Password.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[Person]';

BULK INSERT [Person].[Person] FROM '$(SqlSamplesSourceDataPath)Person.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[PersonCreditCard]';

BULK INSERT [Sales].[PersonCreditCard] FROM '$(SqlSamplesSourceDataPath)PersonCreditCard.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[PersonPhone]';

BULK INSERT [Person].[PersonPhone] FROM '$(SqlSamplesSourceDataPath)PersonPhone.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[PhoneNumberType]';

BULK INSERT [Person].[PhoneNumberType] FROM '$(SqlSamplesSourceDataPath)PhoneNumberType.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[Product]';

BULK INSERT [Production].[Product] FROM '$(SqlSamplesSourceDataPath)Product.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductCategory]';

BULK INSERT [Production].[ProductCategory] FROM '$(SqlSamplesSourceDataPath)ProductCategory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductCostHistory]';

BULK INSERT [Production].[ProductCostHistory] FROM '$(SqlSamplesSourceDataPath)ProductCostHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductDescription]';

BULK INSERT [Production].[ProductDescription] FROM '$(SqlSamplesSourceDataPath)ProductDescription.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductDocument]';

BULK INSERT [Production].[ProductDocument] FROM '$(SqlSamplesSourceDataPath)ProductDocument.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK
);

PRINT 'Loading [Production].[ProductInventory]';

BULK INSERT [Production].[ProductInventory] FROM '$(SqlSamplesSourceDataPath)ProductInventory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductListPriceHistory]';

BULK INSERT [Production].[ProductListPriceHistory] FROM '$(SqlSamplesSourceDataPath)ProductListPriceHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductModel]';

BULK INSERT [Production].[ProductModel] FROM '$(SqlSamplesSourceDataPath)ProductModel.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductModelIllustration]';

BULK INSERT [Production].[ProductModelIllustration] FROM '$(SqlSamplesSourceDataPath)ProductModelIllustration.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductModelProductDescriptionCulture]';

BULK INSERT [Production].[ProductModelProductDescriptionCulture] FROM '$(SqlSamplesSourceDataPath)ProductModelProductDescriptionCulture.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductPhoto]';

BULK INSERT [Production].[ProductPhoto] FROM '$(SqlSamplesSourceDataPath)ProductPhoto.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK
);

PRINT 'Loading [Production].[ProductProductPhoto]';

BULK INSERT [Production].[ProductProductPhoto] FROM '$(SqlSamplesSourceDataPath)ProductProductPhoto.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductReview]';

BULK INSERT [Production].[ProductReview] FROM '$(SqlSamplesSourceDataPath)ProductReview.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ProductSubcategory]';

BULK INSERT [Production].[ProductSubcategory] FROM '$(SqlSamplesSourceDataPath)ProductSubcategory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Purchasing].[ProductVendor]';

BULK INSERT [Purchasing].[ProductVendor] FROM '$(SqlSamplesSourceDataPath)ProductVendor.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Purchasing].[PurchaseOrderDetail]';

BULK INSERT [Purchasing].[PurchaseOrderDetail] FROM '$(SqlSamplesSourceDataPath)PurchaseOrderDetail.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Purchasing].[PurchaseOrderHeader]';

BULK INSERT [Purchasing].[PurchaseOrderHeader] FROM '$(SqlSamplesSourceDataPath)PurchaseOrderHeader.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesOrderDetail]';

BULK INSERT [Sales].[SalesOrderDetail] FROM '$(SqlSamplesSourceDataPath)SalesOrderDetail.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesOrderHeader]';

BULK INSERT [Sales].[SalesOrderHeader] FROM '$(SqlSamplesSourceDataPath)SalesOrderHeader.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesOrderHeaderSalesReason]';

BULK INSERT [Sales].[SalesOrderHeaderSalesReason] FROM '$(SqlSamplesSourceDataPath)SalesOrderHeaderSalesReason.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesPerson]';

BULK INSERT [Sales].[SalesPerson] FROM '$(SqlSamplesSourceDataPath)SalesPerson.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesPersonQuotaHistory]';

BULK INSERT [Sales].[SalesPersonQuotaHistory] FROM '$(SqlSamplesSourceDataPath)SalesPersonQuotaHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesReason]';

BULK INSERT [Sales].[SalesReason] FROM '$(SqlSamplesSourceDataPath)SalesReason.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesTaxRate]';

BULK INSERT [Sales].[SalesTaxRate] FROM '$(SqlSamplesSourceDataPath)SalesTaxRate.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesTerritory]';

BULK INSERT [Sales].[SalesTerritory] FROM '$(SqlSamplesSourceDataPath)SalesTerritory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SalesTerritoryHistory]';

BULK INSERT [Sales].[SalesTerritoryHistory] FROM '$(SqlSamplesSourceDataPath)SalesTerritoryHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[ScrapReason]';

BULK INSERT [Production].[ScrapReason] FROM '$(SqlSamplesSourceDataPath)ScrapReason.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [HumanResources].[Shift]';

BULK INSERT [HumanResources].[Shift] FROM '$(SqlSamplesSourceDataPath)Shift.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Purchasing].[ShipMethod]';

BULK INSERT [Purchasing].[ShipMethod] FROM '$(SqlSamplesSourceDataPath)ShipMethod.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[ShoppingCartItem]';

BULK INSERT [Sales].[ShoppingCartItem] FROM '$(SqlSamplesSourceDataPath)ShoppingCartItem.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SpecialOffer]';

BULK INSERT [Sales].[SpecialOffer] FROM '$(SqlSamplesSourceDataPath)SpecialOffer.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[SpecialOfferProduct]';

BULK INSERT [Sales].[SpecialOfferProduct] FROM '$(SqlSamplesSourceDataPath)SpecialOfferProduct.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Person].[StateProvince]';

BULK INSERT [Person].[StateProvince] FROM '$(SqlSamplesSourceDataPath)StateProvince.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='\t', ROWTERMINATOR='\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Sales].[Store]';

BULK INSERT [Sales].[Store] FROM '$(SqlSamplesSourceDataPath)Store.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[TransactionHistory]';

BULK INSERT [Production].[TransactionHistory] FROM '$(SqlSamplesSourceDataPath)TransactionHistory.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', TABLOCK );

PRINT 'Loading [Production].[TransactionHistoryArchive]';

BULK INSERT [Production].[TransactionHistoryArchive] FROM '$(SqlSamplesSourceDataPath)TransactionHistoryArchive.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[UnitMeasure]';

BULK INSERT [Production].[UnitMeasure] FROM '$(SqlSamplesSourceDataPath)UnitMeasure.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Purchasing].[Vendor]';

BULK INSERT [Purchasing].[Vendor] FROM '$(SqlSamplesSourceDataPath)Vendor.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[WorkOrder]';

BULK INSERT [Production].[WorkOrder] FROM '$(SqlSamplesSourceDataPath)WorkOrder.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

PRINT 'Loading [Production].[WorkOrderRouting]';

BULK INSERT [Production].[WorkOrderRouting] FROM '$(SqlSamplesSourceDataPath)WorkOrderRouting.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='char', FIELDTERMINATOR='\t', ROWTERMINATOR='0x0a', KEEPIDENTITY, TABLOCK );

GO`

SvenAelterman commented 1 year ago

I suspect \n fails because some CSV source files are encoded as UTF-16 LE instead of UTF-8.

Thank you for finding these fixes. I still had to make some changes to Document.csv and to ProductDocument.csv's BULK INSERT options.

Thank you for finding these fixes. I submitted PR #1195 for this. Hopefully, it will get reviewed and approved.

PS: The PR also includes a fix for the last line where a few characters were missing.