Q: SQL Query - INSERT INTO with SELECT WHERE does nothing I have 2 tables with the same schema already created. I want to insert rows from table1 -> table2 with the constraint of Age(column) not being duplicates. The query executes but nothing gets inserted.
CREATE TABLE #Global (dbName varchar(100) NULL)
INSERT INTO #Global VALUES ('db1')

DECLARE @temp nvarchar(1000)
SELECT @temp = dbName from #Global

DECLARE @sql nvarchar(max)
SELECT @sql = 'INSERT INTO [dbo].[Person] ([age], [name])
              SELECT [age], [name]
              FROM [' + @temp + ']..[Person] 
              WHERE [Person].[age] <> [' + @temp + ']..[Person].[age]'
exec sp_executesql @sql

Any help would be much appreciated!

A: Your where clause would exclude all rows.
You should try something like this:
DECLARE @sql nvarchar(max)
SELECT @sql = 'INSERT INTO [dbo].[Person] ([id], [age], [name])
              SELECT min([id]) as id, [age], min([name]) as name
              FROM [' + @temp + ']..[Person] 
              group by age'
exec sp_executesql @sql


A: Write as:
CREATE TABLE #Global (dbName varchar(100) NULL)
INSERT INTO #Global VALUES ('db1')

DECLARE @temp nvarchar(1000)
SELECT @temp = dbName from #Global

DECLARE @sql nvarchar(max)
SELECT @sql = 'INSERT INTO [dbo].[Person] ([age], [name])
              SELECT [age], [name]
              FROM [' + @temp + '].[Person] 
              WHERE [' + @temp + '].[Person].[age] NOT IN (SELECT [age] from [dbo].[Person])'

exec sp_executesql @sql

Why your where clause is not working?
Schema scope resolution always starts at the user's default schema and revert to the
dbo schema if the referenced object is not scope-qualified. 
In the where clause you have not specified schema name for first table. So it's taken as DB1 the source table itself. Just try replacing it with [dbo] schema and whole statement will give you syntax error. Hope this helps!!!

A: try this as the select statement:    
INSERT INTO [dbo].[Person] ([age], [name])
SELECT [age], [name]
FROM [' + @temp + ']..[Person] 
WHERE [' + @temp + ']..[Person].[age] 
not in (select distinct age from [dbo].[Person])

