如何从SQL bcp命令导入文件表中的数据？ [英] How to import the data from file table wth SQL bcp command?

问题描述

我有一个问题，请你帮我解决这个问题...



我正在使用sql server 2017，这就是我的格式文件的样子

I have one query could you please help me on this...

I'm using sql server 2017 and this is how my format file looks like

14.0
4
1 SQLCHAR 0 5 " " 1 cycle SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 2 "" 2 month SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 5 " " 3 year SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 5 "\r\n" 4 type SQL_Latin1_General_CP1_CI_AS

表结构如下，

Table structure is as follows ,

CREATE TABLE TEST
(
CYCLE VARCHAR(5) NOT NULL,
MONTH VARCHAR(2) NOT NULL,
YEAR VARCHAR(4) NOT NULL,
TYPE VARCHAR(5) NOT NULL
)

我的SQL BCP命令是：

My SQL BCP command is:

bcp Sample..Test in D:\Test\Customer.TXT -f D:\Test\bcpfmt\CUST.FMT -S <srvr> -C -T -c -t " " -r "\n"

样本数据文件：

Sample Data FILE:

00005 051996 MOVE

我的问题是，我想将数据从文件导入表'Test'（表名）。我的样本数据如上所示。

' 00005 ' - >列'周期的值'

051996 - >列'月'和'年'的值。它的存储类似' 05 '的'月'和' 1996 'for'年'

MOVE - >列'类型'的值。



051996字段值存储在测试表中的两个列中。 />


我们如何在SQL Server BCP中执行此操作。我已经尝试过bcp格式文件，但格式文件没有得到应用。这有什么设置吗？如何制作



格式文件在sql bcp中工作？



我有什么试过：



我试过格式文件，但格式文件没有应用于bcp命令

My question is, I want to import the data from file to table 'Test'(table name). My sample data is as above.
'00005' -> value for the column 'cycle'
051996 -> value for the column 'month' and 'year'. It stores like '05' for 'month' and '1996' for 'year'
MOVE -> value for column 'Type'.

051996 field value storing in to two colums in Test table.

how can we do this in SQL server BCP. I have tried bcp format file, but the format file is not getting applied. Is there any setting for this? how to make

the format file to work in sql bcp?

What I have tried:

I have tried with format file but for format file is not applying in bcp command

推荐答案

谷歌是你的朋友：



bcp Utility | Microsoft Docs [ ^ ]



创建一个import ssis包并运行带有sql作业的包不是很简单吗？这样，您可以避免使用BCP实用程序的whacky-ass命令行。

Google is your friend:

bcp Utility | Microsoft Docs[^]

Wouldn't it be a lot simpler just to create an import ssis package and run the package with a sql job? That way, you could avoid that whacky-ass commandline for the BCP utility.

这篇关于如何从SQL bcp命令导入文件表中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！