哪种文件类型更适合将数据导入SQL Server:CSV或JSON? [英] Which file type is better for importing data into SQL Server: CSV or JSON?

问题描述

我正在参与一个项目，其中第三方公司将向我们提供客户数据的导出，以便我们可以将其导入到我们的内部系统中.

I am taking part in a project where a third-party company will provide us with an export of our customer data so that we can import them into our in-house system.

每个客户记录都有大约二十个字段.数据类型是字符串，布尔值，整数和日期(带有时间成分和UTC偏移量成分).所有字符串都不超过250个字符.整数，范围从0到100,000(含).

Each customer record has about twenty fields. The data-types are strings, booleans, integers and dates (with time components and UTC offset components). None of the strings are longer than 250 chars. The integer can range from 0 to 100,000 inclusive.

我必须将大约200万用户导入SQL Server数据库.我正在计划阶段，试图确定是否应在csv或json中请求导出文件.我打算要求两者(以防万一)，但我还不知道是否可以.

I have to import about 2 million users into a SQL Server database. I am in the planning phase and trying to determine if I should ask for the export file in csv or json. I am planning on asking for both (just in case), but I don't yet know if I can.

如果我只能选择一种文件类型(csv或json)，那么哪种类型的工作更好?有经验的人可以向SQL Server导入数据吗?

If I can only pick one file-type (csv or json), which is better for this kind of work? Can anyone with experience importing data into SQL Server provide any advice on which is better?

推荐答案

如果您使用批量方法，同样可以快速完成. 从SQL Server 2016开始，JSON受本机支持，您可以使用JSON函数轻松地对其进行操作. 您还可以使用OPENJSON通过T-SQL直接导入文件 和OPENROWSET批量导入.或者，您可以将上面的T-SQL放入SSIS包中.

The same are fast if you use the bulk method. From SQL Server 2016 the json is natively supported and you can manipulate it easily with JSON function. You can also import file directly via T-SQL with OPENJSON and OPENROWSET BULK IMPORT. Alternately you can put the T-SQL above into SSIS package.

有关更多详细信息，请参见本文: https://www.sqlshack.com/import-json-data -into-sql-server/

See this article for more details: https://www.sqlshack.com/import-json-data-into-sql-server/

这篇关于哪种文件类型更适合将数据导入SQL Server:CSV或JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！