I need to insert 3 Laks rows in a temporary table. Which is the best approach to insert in less time? Guide me

Thanks.

Ram

SELECT Col1, Col2..
INTO #Temp
FROM YourTable

Data is not in another table. it is in a spreadsheet.

I tried in this way.

insert into #temp

 select 1,'A' UNION ALL

 select 2,'B' UNION ALL

select 3 ,'C'

etc.............

Hi,

What is your source of data?

Is it within the same database then you can just use 

SELECT Col1, Cols2, Col3...
INTO #table
From SourceTable

But if your source is something different then need different approach to insert.

Data is not in another table. it is in a spreadsheet.

I tried in this way.

insert into #temp

 select 1,'A' UNION ALL

 select 2,'B' UNION ALL

select 3 ,'C'

etc.............

Excel spreadsheet or CSV ?

You may try using BULK INSERT or OPENROWSET.

See various options here:

https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-serve

Hi,

Additionally, you can also make use of SSMS import wizard to import data.

Simple way to import data into SQL Server

You can use SSIS Bulk Insert Task to insert your data from text file into SQL Server Table. Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.

https://msdn.microsoft.com/en-us/library/ms141239.aspx

Or 

If you have data in CSV or Excel you can use Import and Export Data Wizard, and that a very simple way to do such thing and plus point is you can save it like SSIS package as well to make changes later if you want.

http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

in both above cases, destination is SQL Server Table, Once Data is in SQL Table you can play with it easily.

In addition, you can also check the feasibility of SSIS Package:

Refer the below link:

https://www.simple-talk.com/sql/reporting-services/using-sql-server-integration-services-to-bulk-load-data/