How to use bcp in on a table with an indexed view in SQL Server

I'm using SQL Server 2017 and want to use "bcp in" in a script to populate tables in several databases. I am unable to import data into a table that has an indexed view. The following is an MCVE that reproduces my problem:

-
Run the script at the end of this post to populate a test database with two tables, an indexed view and some data.

-
Run bcp out to export the test data from the table Table1 to a file:

bcp [dbo].[Table1] out .\Table1.bcp -S "localhost" -d TestDB -T -k -N

• Delete the test data from Table1:

DELETE FROM [dbo].[Table1]

• Attempt to import data into Table1 using bcp in:

bcp [dbo].[Table1] in .\Table1.bcp -S "localhost" -d TestDB -T -k -N

Result: fails with an error message INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...

Note: if I drop the index [ix_v1] on the view, this will succeed: the problem only occurs if the table is referenced by an indexed view.

• Attempt to import data into Table1 using bcp in with the -q switch:

bcp [dbo].[Table1] in .\Table1.bcp -S "localhost" -d TestDB -T -k -N -q

Result: fails with an error message Invalid object name '[dbo].[Table1]'

• Attempt to import data into Table1 by specifying the table name without [] delimiters, and with the -q switch:

bcp dbo.Table1 in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q

Result: the data is successfully imported. However this does not meet my requirements, because I want a generic script that will also work with table names that require delimiters (e.g. [dbo].[My Table]).

Question: Is there a way to use bcp to import data into a table with an indexed view, while specifying a delimited, schema-qualified table name on the bcp command line?

Script to populate an empty database TestDB

```
USE [TestDB]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].Table1 NOT NULL,
CONSTRAIN

The documentation for the bcp -q option states:

Enclose the entire three-part table or view name in quotation marks
("")

Instead of enclosing the individual object name parts in square brackets, enclose the entire qualified object name argument in double-quotes. 2-part names are allowed here since you specified the -d option for the database context:

bcp "dbo.Table1" in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q

This will allow object names that don't conform to regular identifier naming rules. The -q option will additionally SET QUOTED_IDENTIFIER ON to allow inserting into a tables with indexed views, filtered indexes, etc.

However, you'll still get an error if the table name contains a period. An undocumented work-around is to enclose both the schema and object name in double-quotes:

bcp "dbo"."Table.NameWithDot" in .\Table1.bcp -S ".\SqlExpress17" -d TestDB2 -T -k -N -q

StackExchange Database Administrators Q#310571, answer score: 6