SQL Server-插入默认值bcp [英] SQL Server - Inserting default values bcp

问题描述

在SQL Server中，如何在使用bcp命令时插入默认值?

In SQL Server, how to insert default values while using bcp command?

场景来自下表，运行bcp命令时，列"sno"是标识列，其中值应自动递增1，values列的数据应来自数据文件，而date列的值应该会自动更新为今天的日期，并且状态"列的值应更新为Flag1.

Scenario is from the below table, while running bcp command, column 'sno' is identity column, where values should increment automatically by 1, data for values column should come from datafile and values for date column should automatically updated to today's date and values for status column should updated as Flag1.

对于正常使用，我知道如何创建bcp格式文件.对于上述情况，如何创建格式文件并将数据插入到table1中?

For normal usage I know how to create bcp format file. For the above scenario, how can I create a format file and insert data to table1?

表格格式:

CREATE TABLE [dbo].[table1] 
(
    SNo int IDENTITY(1,1) NOT NULL,
    values  varchar(13) NOT NULL,
    date    datetime NOT NULL,
    status  varchar(50)
)

Table1:

sno  |  values  |    date    |  status
-----+----------+------------+--------
1    |  111111  | 2015-08-17 |  Flag1
2    |  222222  | 2015-08-17 |  Flag1

推荐答案

基本上，只需将0作为主机列号，以避免bcp插入列.

Basically, you just need to put 0 as the host column number to avoid a column from being inserted by bcp.

因此，假设您为[date]列设置了默认约束:

So assuming you have a default constraint for your [date] column:

ALTER TABLE dbo.table1
ADD CONSTRAINT DF_Table1_Date DEFAULT(SYSDATETIME()) FOR [Date]

，并且您还以某种方式设置了一种计算[status]的方法-那么您可以使用以下格式文件:

and somehow you have also set up some way to calculate the [status] - then you could use this format file:

12.0
4
1    SQLCHAR         0    12   ";"      0    SNo        ""
2    SQLCHAR         0    13   ";"      2    values     SQL_Latin1_General_CP1_CI_AS
3    SQLDATETIME     0    24   ";"      0    date       ""
4    SQLCHAR         0    50   "\r\n"   0    status     SQL_Latin1_General_CP1_CI_AS

，因此您实际上只导入了[values]列-SNo由SQL Server自动设置(身份列)，[date]列通过以下方式自动设置为当前日期和时间:默认约束-现在，您必须找到一种在插入时或插入后填充[status]列的方法！

and thus you would be really only importing the [values] column - the SNo is automatically set by SQL Server (identity column), the [date] column is automatically set to the current date&time by means of the default constraint - now you'll have to find a way to fill in the [status] column upon or after insert!

这篇关于SQL Server-插入默认值bcp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！