CODEWITHSUNDEEP
sql-serveridentitybcp
Green
Green

Reputation: 111

SQL Server - Inserting default values bcp

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

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.

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?

Table format:

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

Upvotes: 0

Views: 1691

Answers (1)

marc_s
marc_s

Reputation: 754488

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

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

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

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

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!

Upvotes: 1

Related Questions