Extracting rows from a big table

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

SQL Server Development (2000)

Extracting rows from a big table

Author

Topic

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-05-05 : 15:53:00

I have "Table a" with 2 million rows. I want to insert 200,000 from 'Table A' to "Table B" and delete the 200,000 rows that I extracted from "Table A"

Can someone please tell me how I can do this?

Thanks

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-05 : 16:36:46

Insert into tableB
( , , , , , )
Select
, , , , ,
from tableA
Where ItMeetsYourCcondition

delete
from tableA
Where ItMeetsYourCcondition

Jim
Users <> Logic

Sully
Starting Member

30 Posts

Posted - 2005-05-05 : 16:41:18

Question, which 200,000 rows, is there a method to the extraction, can you put it in a where clause...such as

insert into TableB
select * from TableA
where some_condition

if @@error = 0
delete TableA
where some_condition --same as above

Stuck in neutral in a pedal to the metal world,
Sully

Sully
Starting Member

30 Posts

Posted - 2005-05-05 : 16:42:31

Looks like Jim beat me to it...lol

Stuck in neutral in a pedal to the metal world,
Sully

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-06 : 03:13:58

And you may want to wrap these two statements in a Transaction so that if anything goes wrong you can rollback the partial changes.

HTH

=================================================================
Some mistakes are too much fun to only make once.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-05-06 : 09:22:11

Thanks all! The only problem is my where clause will return more than 200,000 rows and I want to only do the first 200,000 and then on in 200,000 increments due to network reasons.. Can I do a TOP in my select, but the problem will be I dont know which 200,000 though

Any ideas?

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-05-06 : 10:03:37

Here is my problem. This is what I am trying to do, thought will give you the whole picture.

My where clause is going to return more thatn 200,000 rows. But I want to get the first 200,000 rows only. The reason is I want to get some data for the first 200,000 from an Oracle DB and populate those columns into Table B.

Along those lines can you tell me what is the best approach to do that and here is what I am trying to do. I ahve an idea but am not sure how to go about

1. There is 1 table in Oracle (ORA) A with 15 columns
2. There are 2 tables (SQL) A with 10 columns,(SQL) B which is a identical table as the one in Oracle with 15 columns on a SQL box.

3. Retrieve the first 200,000 from (SQL) table A with a where clause into SQL table B
4. I need to get the values for those remaning 5 columns from Oracle and populate into table B on SQL box.
5. Delete that 200,000 rows from table A in the SQL box.

Thank you very much!

Sully
Starting Member

30 Posts

Posted - 2005-05-06 : 11:06:57

I don't know Oracle, but you could use a temp table, insert the first 200,000 rows into it from Table A, then you'd have to populate from Oracle ??? the other 5 columns. Is there anything unique about the rows from Table A? Once the temp table is complete, you could then insert into Table B. Then use the temp table to delete the appropriate rows from Table A. Such as:

CREATE TABLE #tmptable
(
tt_col1 CHAR(8),
tt_col2 char(8),
tt_col3 char(8)
)

INSERT INTO #tmptable
select top 50000 tabA_col1, tabA_col2, ' ' from tableA

???Oracle stuff here???

Insert tableB
select * from #tmptable

delete tableA
where ta_col1 in (select tt_col1 from #tmptable)

drop table #tmptable

And as mentioned above I would wrap this in a transaction. I hope this is of some help.

Stuck in neutral in a pedal to the metal world,
Sully

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-05-06 : 11:41:56

Exactly! Thanks much! thats kind of the approch I am taking.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources