how to insert 4 million rows quickly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

how to insert 4 million rows quickly

Author

Topic

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 13:27:42

I am not a DBA, but I have to deal with all the database problems after our DBA person left. So here is my question#65306;

How to quickly insert 4 million rows from table A to table B? Both table A and table B are in the same database. Should I use export and then import data?

I really need your suggestions. Thanks a million!

jason
Posting Yak Master

164 Posts

Posted - 2005-03-23 : 15:28:12

Don't know how quick it will be, but I suspect a select statement would be your best bet.

SELECT * FROM Table_A INTO New_Table WHERE <your criteria>

or entire table just leave out the WHERE clause

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-03-23 : 15:56:20

The key points in large DML operations are indexes and log activity.
Indexes are a case by case basis, but you should be performing a bulk log operation. ie BCP etc.. look it up in BOL.

DavidM

A front-end is something that tries to violate a back-end.

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 16:04:29

Thanks for the response!

This Table_A has several foreign keys and indexes. Right now it has no data. All the data I need is in table_B which has exactly the same structure, but no any contraints or indexes. Should I drop table A and create constraints and indexes for table B, instead of copy data from table_B to table_A? Would it save a lot of time?

I tried bcp and successfully export the data from table_B to temp.bcp file. But when I tried to import temp.bcp to table_A, it stopped after sending 181000 rows. I don't know what the problem is.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 16:06:48

If you want to do this quickly, then the table to receive the data needs to have it's indexes dropped so that SQL Server doesn't have to keep updating them. Then once you are done, add them back in.

quote:

I tried bcp and successfully export the data from table_B to temp.bcp file. But when I tried to import temp.bcp to table_A, it stopped after sending 181000 rows. I don't know what the problem is.

Bcp would have generated an error. Please post it. Also post your bcp in and bcp out commands.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 16:29:01

Do you know how to script the table DDL?

And how did you bcp the data out?

Native format would be the way to go

I'm guessing there are some difference in the structures.

Post the DDL to prove they're the same....

Brett

8-)

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 16:29:48

Tara, bcp didn't generated an error. It just paused there, so I thought it stopped.

I decided to use table_B as the production table, so I am building primary key and foreign key on this table. But it seems taking a long time. Is it because the table has > 4 million rows? So when I build indexes on it, it will take a long time, right?

Sorry for my inexperience!

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 16:33:46

Bcp commands I used:

bcp mydb.dbo.mytable_old out mytable_old.bcp -U username -P password -n
bcp mydb.dbo.mytable_new in mytable_old.bcp -U username -P password
(then i entered many times without specifying datatype for each column)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 16:37:56

Yes, indexes will take a long time to create when there are a large number of rows in the table.

quote:

(then i entered many times without specifying datatype for each column)

What does that mean?

Show us the DDL of both tables.

Tara

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 16:53:43

quote:
(then i entered many times without specifying datatype for each column)

means it prompted me to enter column type, length and etc.(I can't clearly remember all of them). Then I press enter each time it prompted me.

The reason I said table_A and table_B are in the same structure is that originally there were 36 million rows in table_A, I wanted to delete 32 million rows from it, but it took so much time, so I copied 4 million rows (which I want to keep) to table_B, then truncate table_A. Now I want to move data back from table_B to table_A. That's why I said table_A and table_B are in the same structure. I didn't write DDL for either of them.

Do you think I can use table_B as a production table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 16:57:25

Just script the DDL for us. Right click on both tables, all tasks, generate sql script...post the info here.

It shouldn't have prompted you for column type information if the structures are the same.

Tara

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 17:03:24

Here are the codes:
The first one is actually table_B, second one is table_A as I memtioned.

Table_B:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MessagesReceived_MessagesReceived]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MessagesReceived] DROP CONSTRAINT FK_MessagesReceived_MessagesReceived
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessagesReceived]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MessagesReceived]
GO

CREATE TABLE [dbo].[MessagesReceived] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[MessageParentID] [int] NULL ,
	[MessageProcessedID] [int] NULL ,
	[MessageSourceID] [int] NOT NULL ,
	[StationID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ErrorCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ArrivalTime] [datetime] NOT NULL ,
	[InsertionTime] [datetime] NOT NULL ,
	[SignalStrength] [int] NOT NULL ,
	[FrequencyOffset] [int] NOT NULL ,
	[PhaseShift] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SignalToNoise] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Length] [int] NOT NULL ,
	[Channel] [int] NOT NULL ,
	[HeaderText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MessageText] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DeleteReady] [int] NULL 
) ON [PRIMARY]
GO

table_A:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessagesReceived_old]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MessagesReceived_old]
GO

CREATE TABLE [dbo].[MessagesReceived_old] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[MessageParentID] [int] NULL ,
	[MessageProcessedID] [int] NULL ,
	[MessageSourceID] [int] NOT NULL ,
	[StationID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ErrorCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ArrivalTime] [datetime] NOT NULL ,
	[InsertionTime] [datetime] NOT NULL ,
	[SignalStrength] [int] NOT NULL ,
	[FrequencyOffset] [int] NOT NULL ,
	[PhaseShift] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SignalToNoise] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Length] [int] NOT NULL ,
	[Channel] [int] NOT NULL ,
	[HeaderText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MessageText] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DeleteReady] [int] NULL 
) ON [PRIMARY]
GO

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-23 : 17:18:33

quote:
Originally posted by sqlservernew

quote:
(then i entered many times without specifying datatype for each column)

means it prompted me to enter column type, length and etc.(I can't clearly remember all of them). Then I press enter each time it prompted me.

The reason I said table_A and table_B are in the same structure is that originally there were 36 million rows in table_A, I wanted to delete 32 million rows from it, but it took so much time, so I copied 4 million rows (which I want to keep) to table_B, then truncate table_A. Now I want to move data back from table_B to table_A. That's why I said table_A and table_B are in the same structure. I didn't write DDL for either of them.

Do you think I can use table_B as a production table?

Yeah, that being said, why not fire up enterprise manager, rename table b to table a, and create your indexes??

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

sqlservernew
Starting Member

7 Posts

Posted - 2005-03-23 : 17:52:07

quote:

Yeah, that being said, why not fire up enterprise manager, rename table b to table a, and create your indexes??

That's what I am doing - rename table and create index. I didn't do that because I was afraid of messing up things and that all the procedures related with table a will not work after renaming.

Kristen
Test

22859 Posts

Posted - 2005-03-24 : 01:01:20

Create a VIEW for the other table name you need?

CREATE VIEW dbo.Table_B
AS
SELECT * -- put all column names here, do NOT use "*" !!
FROM dbo.Table_A

Kristen

Kristen
Test

22859 Posts

Posted - 2005-03-24 : 01:02:20

OH ... and if you are using BCP for ths job you need to use Native format - that will avoid all thse pesky ENTER issues! Target table needs ot have identical structure though AFAIK

... And order by any Clustered index (or PK) that you will be using

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources