Insert data from one table to another

Hi,

I need to insert data from one table to another table. Currently i am using below statement:

INSERT INTO TABLE_2
SELECT * FROM TABLE_1with (nolock)
GO

The table_1 contains heavy records (in corers) and log file size is growing very high and also it was taking 9-10 hours (1107052852 rows) to insert.

Is there any other way to speed up my insert statement & to minimize log file size growth.

NOTE: 1. table_2 already exist in database with same structure of table_1.
           2. one clustered index and one nonclustered indexes are there in both the tables.
           3. I have tried with Heap table method (no indexes) but no use.

srinivas_ganamurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
Change your Database recovery model to simple or bulk-logging

Then remove index in both table.

Insert using bcp or import_export wizard.

Then re-create index.

Now change the recover model to full.

This should help you.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
You can use BCP utility to copy records in a faster manner compared to the approach you are doing..
Using BCP, export table contents to a flat file and then import it back into your destination table. And you can refer "A. Copying table rows into a data file (with a trusted connection)" and "C. Copying data from a file to a table" in the link below;

http://msdn.microsoft.com/en-us/library/ms162802.aspx

Or you can also use SSIS to do Bulk Insert operations which would be faster too..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srinivas_ganamurAuthor Commented:
Hi Sachiek,

The destination table is (table_2) is partitioned table that is table data contains 10 NDF file. I have created indxes on this table. I think can't drop / disable indexes right?

Currently my database in simple recovery model.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

srinivas_ganamurAuthor Commented:
Hi Sachiek,

The destination table is (table_2) is partitioned table that is table data contains 10 NDF file. I have created indxes on this table. I think can't drop / disable indexes right?

Currently my database in simple recovery model.
0
srinivas_ganamurAuthor Commented:
Hi Sachiek,

The destination table is (table_2) is partitioned table that is table data contains 10 NDF file. I have created indxes on this table. I think can't drop / disable indexes right?

Currently my database in simple recovery model.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
srinivas_ganamur,

Any commments on my post above..
0
srinivas_ganamurAuthor Commented:
Hi Rregan17,

Your solution is looking good..i am testing in my local server. After that i will let you know the result.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Sure..
0
srinivas_ganamurAuthor Commented:
HI RREGAN17

i AM INSERTING DATA USING BULK INSERT (I THINK IT IS FAST AS COMPARED MY INSERT METHOD..RIGHT?)...BUT IT WAS GIVING SYNTAX ERROR

INSERT INTO [dbo].[TABLE_1]

SELECT *

FROM OPENROWSET(BULK 'SQLNCLI', 'Server=(local);Trusted_Connection=yes;',

'SELECT * FROM INSTA_HISTORY.DBO.[dbo].[TABLE_2]')

PLEASE LET ME KNOW WHERE I AM WRONG..
0
srinivas_ganamurAuthor Commented:
INSERT INTO DATABASE1.[dbo].[TABLE_1]

SELECT *

FROM OPENROWSET(BULK 'SQLNCLI', 'Server=(local);Trusted_Connection=yes;',

'SELECT * FROM DATABASE1.[dbo].[TABLE_2]')
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
BULK option can be provided only for loading from files and it wont work with another SQL Server table..
And that's why you are obtaining errors.
0
srinivas_ganamurAuthor Commented:
--
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.