where clause on 'insert into select' not filtering records

Question

I am writing a query where I use an INSERT INTO tablename SELECT statement, and end the INSERT INTO statement with a where clause. However, the where is not filtering the results as intended (I know there are records where this criteria is not met and should be filtered out), nor is it throwing an error. So, I cannot figure out why it is not outputting the expected results.

Please see my code below:

N'INSERT INTO table
 SELECT DISTINCT
      [Identifier]
      ,[Variable1]
      ,[Variable2]
      ,[Variable3]
      ,[Variable4]
      ,[Variable5]
      ,[Variable6]
      ,[Variable7]
      ,[Variable8]
      ,NULL
      ,[Variable9]
      ,NULL
  FROM [DBname].[tablename].[tablename2]
WHERE [Variable10] = ''Successful'';'

Answer 1

A free beer says your variable is a VARCHAR(300) and this is 340 characters long, truncating it right before the WHERE clause.

Answer 2

Are you sure your table name is correct? ([DBname].[tablename].[tablename2])

Are you sure your table structure that insert is correct?

Declare @sql varchar(4000)
Set @sql =N'
    INSERT INTO [TITMS_Test].[dbo].[GROUP] (Name, Description)
    SELECT DISTINCT Name, Description
    FROM [TITMS].[dbo].[GROUP]
    WHERE [Name] = ''Imported'';'
Print @sql
Exec (@sql)