CODEWITHSUNDEEP
sqlsql-serverwhere-clauseinsert-into
mafro
mafro

Reputation: 1

where clause on 'insert into select' not filtering records

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'';'

Upvotes: 0

Views: 1218

Answers (2)

Thao Phan
Thao Phan

Reputation: 11

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)

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5425

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

Upvotes: 3

Related Questions