select * into without max rows limit T-SQL

Question

I'm duplicating a series of tables using a loop in SQL. I've inferred from SQL Server Maximum rows that can be inserted in a single insert statment that the maximum rows which can be inserted using a select * is 10,000 for my SQL SERVER. However, the source tables are bigger than 10,000 rows. Is there a way to bypass this limitation without explicitly stating the columns in the select statement, since these will be different for each table?

DECLARE @iTable TABLE (TableName VARCHAR(50),Id int identity(1,1))
INSERT INTO @iTable
Select distinct table_name From INFORMATION_SCHEMA.COLUMNS 
Where table_name like 'D0%' OR table_name like 'D1%'
DECLARE @imax int
DECLARE @iSQL VARCHAR(MAX) 
DECLARE @iTableName VARCHAR(max)
DECLARE @iid int = 1
select @imax = MAX(Id) from @iTable
WHILE (@iid <= @imax) 
BEGIN
    SELECT @iTableName = TableName FROM @iTable WHERE Id = @iid
    SET @iSQL = 'select * into st_'+ @iTableName +' from '+ @iTableName +';'
    EXEC(@iSQL)
    SET @iid = @iid +1
END

"I've discovered that the maximum rows which can be inserted using a select * is 10,000" - care to provide a link to that effect? It's not a limitation I've heard of....It's simply not true! — Mitch Wheat, Oct 31 '17 at 14:15
`I've discovered that the maximum rows which can be inserted using a select * is 10,000`, how did you discovered this?, what version of SQL Server are you using, since I've never encountered this situation before and I've just tested inserting 10,000,000 from one table to another with no issue — Lamak, Oct 31 '17 at 14:15
edited above - 'inferred' not discovered! when i tested the code, the tables had a count (*) of 10k rows — joshi123, Oct 31 '17 at 14:24

score 1 · Answer 1 · answered Oct 31 '17 at 14:22

As already mentioned there is no limitation of the number of rows when using select into. However, you could certainly simplify your code and get rid of that loop.

DECLARE @iSQL NVARCHAR(MAX) = ''

SELECT @iSQL = @iSQL + 'select * into st_'+ TABLE_NAME +' from '+ TABLE_NAME +';'
from INFORMATION_SCHEMA.COLUMNS 
Where TABLE_NAME like 'D0%' 
    OR TABLE_NAME like 'D1%'
group by TABLE_NAME

select @iSQL
--uncomment below when you are satisfied the dynamic sql is correct.
--exec sp_executesql @iSQL

select * into without max rows limit T-SQL

1 Answers1