CODEWITHSUNDEEP
sql-server
daniel caceres
daniel caceres

Reputation: 17

MSSQL server select into

I have a table with 20 millions rows and I need to create another table off of it. but select into new table only inserts 100K records.

I have tried select into it and select table as

select * INTO #test
from dbo.log

New table should have 20 millions rows

Upvotes: 0

Views: 96

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

In addition to @gordy's ROWCOUNT suggestion, you could be looking at the wrong #temp table.

Consider:

select top 10 * into #foo from sys.objects

exec ('select * into #foo from sys.objects')

select count(*) from #foo

The nested batch creates a transient temp table called #foo that is destroyed at the end of the batch. This would happen calling a stored procedure too.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Tables in temp db are very similar to the one available on regular databases. The table may be restricted with disk space available.

SELECT *
INTO #temp
FROM dbo.log

-- checking actual number of rows affected by query above
SELECT @@ROWCOUNT;

Upvotes: 1

Related Questions