SQL Tips-Bulk insert records into table ,Select into ,Insert into,delete record in chunks,Remove unused space

Bulk insert records into table in sql 

This is the way to Bulk insert records into table in sql or oracle

insert into 

If you want to insert of table data to existing table then you need to select insert into query.
E.g.

INSERT INTO Table2

SELECT * FROM Table1

insert into dTable select * from sTable

It mean table2 and table1 both exists before execution of query and its simply insert data line by line. It takes more time on execution.



Select into:

If you want to copy of table structure with data to new table then you need to select select into query.
E.g.

Select * into tab1 from tab2.

It means tab2 exists and tab1 does not exist in database before execution of query. After execution it will copy data of tab2 to tab1 and it will create table tab1 also.


if dTable is blank and doesn't have any record.

Create this as follows: definately reduce the time.

CREATE TABLE dtable NOLOGGING AS SELECT * FROM stable


Example of delete records from sql table delete records  in chunks

If your query for deleting large number of records and its taking long time to delete records then you can try deletion by chunks. This is an simple Example of delete records from sql table in chunks

WHILE (select count(1) from Some_Table where table_sr_key !=-99 and sector_id = @some_code) >0
BEGIN
Delete top (50) from Some_Table where table_sr_key !=-99 and sector_id = @some_code
END

Remove unused space from Table in SQL

1) sp_spaceused "Tablename", @updateusage = 'TRUE'
2) DBCC UPDATEUSAGE('DBName', 'TableName')
3) DBCC CLEANTABLE ('DBName', 'TableName')
4) UPDATE STATISTICS TableName