srinivas_ganamur
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
srinivas_ganamur,
Any commments on my post above..
Any commments on my post above..
ASKER
Hi Rregan17,
Your solution is looking good..i am testing in my local server. After that i will let you know the result.
Your solution is looking good..i am testing in my local server. After that i will let you know the result.
Sure..
ASKER
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_Co
'SELECT * FROM INSTA_HISTORY.DBO.[dbo].[T
PLEASE LET ME KNOW WHERE I AM WRONG..
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_Co
'SELECT * FROM INSTA_HISTORY.DBO.[dbo].[T
PLEASE LET ME KNOW WHERE I AM WRONG..
ASKER
INSERT INTO DATABASE1.[dbo].[TABLE_1]
SELECT *
FROM OPENROWSET(BULK 'SQLNCLI', 'Server=(local);Trusted_Co
'SELECT * FROM DATABASE1.[dbo].[TABLE_2]'
SELECT *
FROM OPENROWSET(BULK 'SQLNCLI', 'Server=(local);Trusted_Co
'SELECT * FROM DATABASE1.[dbo].[TABLE_2]'
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.
And that's why you are obtaining errors.
ASKER
--
ASKER
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.