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

All we need is an easy explanation of the problem, so here it is.

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:

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

  2. 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
  1. Delete the test data from Table1:
DELETE FROM [dbo].[Table1]
  1. 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.

  1. 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]'

  1. 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](
    [Table1Id] [int] NOT NULL,
    [Table1Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Table1Id] ASC)
 )
GO
CREATE TABLE [dbo].[Table2](
    [Table2Id] [int] NOT NULL,
    [Table2Name] [nvarchar](50) NOT NULL,
    [Table1Id] [int] NULL,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED (  [Table2Id] ASC)
)
GO
CREATE VIEW [dbo].[v1] WITH SCHEMABINDING
AS
    SELECT 
    T1.Table1Id, T1.Table1Name,
    T2.Table2Id, T2.Table2Name
    FROM [dbo].[Table1] T1 INNER JOIN [dbo].[Table2] T2
    ON T1.Table1Id = T2.Table1Id
GO
CREATE UNIQUE CLUSTERED INDEX [ix_v1] ON [dbo].[v1] (Table1Name, Table2Name)
GO
INSERT INTO Table1
VALUES 
 (1, 'One')
,(2,'Two')

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply