You need to use dynamic sql in order to insert the correct group name into the table name(s).
E.g (untested)
DECLARE @i int
DECLARE @numrows int
DECLARE @indexcount int
DECLARE @Group varchar(200)
DECLARE @temp_table TABLE (
idx smallint Primary Key IDENTITY(1,1), [Group] varchar(200)
)
INSERT @temp_table
SELECT distinct Column_3 FROM #test
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @temp_table)
SET @indexcount = (SELECT MAX(idx) FROM @temp_table)
IF @numrows > 0
WHILE (@i <= @indexcount)
BEGIN
SET @Group = (SELECT [Group] FROM @temp_table WHERE idx = @i);
declare @sql nvarchar(max)
declare @drop nvarchar(max)
set @drop = 'IF OBJECT_ID(''dbo.New_Table_' + @Group + ''', ''U'') IS NOT NULL DROP TABLE dbo.New_Table_' + @Group
EXECUTE sp_executesql @drop
set @sql = 'SELECT Column_1, Column_2 INTO New_Table_' + @Group + ' FROM #test WHERE Column_3 = ''' + @Group + ''''
EXECUTE sp_executesql @sql
SET @i = @i + 1
END
Also note the use of square brackets around the column name
Group
in table @temp_table (and everywhere it is used). This is because Group is a reserved word and should not be used as a column name. It can be used as a column name only if surrounded by square brackets -
[Group]
[EDIT] For the benefit of other readers, please review my comments to the OP. A better approach would be to create
Views to be used by the other application. are describing can be achieved using Views without duplicating the data. To the calling application there should be no apparent distinction between querying a view and querying a table.
The solution provided above could be altered to create views rather than tables, the key is that the sql statements must be dynamic.