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:
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.
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.
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 🙂