作为CSV导入操作的一部分，如何在一个步骤中创建和填充表格? [英] How to create and populate a table in a single step as part of a CSV import operation?

问题描述

我正在寻找一种快捷方式将CSV文件导入SQL Server ，而无需事先创建表并定义其列.

I am looking for a quick-and-dirty way to import CSV files into SQL Server without having to create the table beforehand and define its columns.

每个导入的CSV都将导入到其自己的表中.

Each imported CSV would be imported into its own table.

我们不关心数据类型推断. CSV的结构和布局各不相同，它们都有很多列，但我们只关心其中的几个:街道地址和邮政编码.我们只想将CSV数据快速导入SQL数据库并提取相关列.

We are not concerned about data-type inferencing. The CSV vary in structure and layout, and all of them have many many columns, yet we are only concerned with a few of them: street addresses and zipcodes. We just want to get the CSV data into the SQL database quickly and extract the relevant columns.

我想提供FieldTerminator和RowTerminator，将其指向CSV，然后由实用程序完成其余的工作.是否有任何方法可以使用BULK INSERT和/或OpenRowset(BULK ...)一步创建表格并填充表格?

I'd like to supply the FieldTerminator and RowTerminator, point it at the CSV, and have the utility do the rest. Is there any way to create the table and populate it, all in one step, using BULK INSERT and/or OpenRowset(BULK ... ) ?

推荐答案

Referencing SQLServerPedia, I think this will work:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

select TerritoryID
      ,TotalSales
      ,TotalCost
INTO CSVImportTable
from openrowset('MSDASQL'
               ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
               ,'select * from C:\csvtest.CSV')

这篇关于作为CSV导入操作的一部分，如何在一个步骤中创建和填充表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！