Split table - SQL Server Forums

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

Transact-SQL (2000)

Split table

Author

Topic

Gili
Starting Member

42 Posts

Posted - 2005-11-10 : 03:10:30

Hi to all.
i want to split table that have 100,0000 records to several tables with 10,000 records each and then export those tables to text files.
i'll be happy to some help.
thanks.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-10 : 03:12:45

why not direct export instead of splitting?

Karunakaran

Gili
Starting Member

42 Posts

Posted - 2005-11-10 : 03:19:03

i need text files that will be max 10000 records.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 03:30:01

Refer these pagination techniques
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx
http://www.aspfaq.com/show.asp?id=2120

Madhivanan

Failing to plan is Planning to fail

Gili
Starting Member

42 Posts

Posted - 2005-11-10 : 03:50:36

hi madhivanan this is not what i look for .
i look for split table in access or SQL SERVER to 10,000 records each.
thanks anyway.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 03:59:38

If you have identity column then you can write queries

Select columns from yourTable where id between 1 and 10000
Select columns from yourTable where id between 10001 and 20000
.
.

Madhivanan

Failing to plan is Planning to fail

Gili
Starting Member

42 Posts

Posted - 2005-11-10 : 04:04:11

i don't have idnetity column.
there is no system index column??
or some other way?

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-10 : 04:16:07

Probably you might need to dump the existing records to a temp table with an identity column as rowid and can try madhivanan's solution

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 04:18:15

Post the structure of the table
If you have primary key
Take this an example


Declare @t table(i int)
insert into @t select 1 union all select 2 union all select 3 union all select 4 union all 
select 5 union all select 6 union all select 7 union all select 8 

select top 2 i from @t

Select i from(
		select top 2 i from (
					select top 4 i from @t order by i asc) T order by i desc
				     ) T 
		order by i asc

Select i from(
		select top 2 i from (
					select top 6 i from @t order by i asc) T order by i desc
				     ) T 
		order by i asc

Select i from(
		select top 2 i from (
					select top 8 i from @t order by i asc) T order by i desc
				     ) T 
		order by i asc

which split records by 2 rows

Madhivanan

Failing to plan is Planning to fail

Gili
Starting Member

42 Posts

Posted - 2005-11-10 : 04:29:47

the structure of the table was import from .csv file.
and now i have split the table to 10000 records each and i have to export it back to text files.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 05:43:05

>>now i have split the table to 10000 records each and i have to export it back to text files.

Well. Try this

Exec Master..xp_cmdShell 'bcp "Select columns from DBName..tablename" queryout "C:\file.txt" -c'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources