Search code examples
sql-serverpowershellbcp

using BCP to fill a column of a table with a single value

Let's say I have a table with 5 columns, some data with 4 columns, and a format file that specifies where bcp should write that 4-column data (obviously one column will be unused).

Is there any way to use bcp to supply a single value for that 5th column at the time bcp is executed? That is, when I run BCP, I want 4 of the columns in the table to be filled with my data, and I want the 5th column to be filled with a single integer. I'm guessing no, but it would be insanely helpful if it could be done.

EDIT: The problem with using a separate query to fill this column is that there are millions of records. It times out once the table gets big enough. I know that there is a default value concept in bcp, but here's the rub: the default value will vary depending on the Excel file I'm currently blasting in. Perhaps I can change the default value each time?

Solution

  • The two ways I can think of doing is are

    • Don't BCP that one column in and just set a default value for it in SQL. You'd have to change it for each file but you can do that programmatically.
    • Use a fake the value in the file you are using to BCP in like "XXXX". After you BCP the data in simply run an update on that column

    Good luck!