Breaking up a 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 2005 Forums

Other SQL Server Topics (2005)

Breaking up a Table

Author

Topic

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 16:10:57

I've got a Table that has over 500,000 row in it. Now I need to convert the whole thing into Excel to import into another application. So I need to break the table into 10 different tables. How can I do that?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-17 : 16:14:41

SELECT *
INTO Table1
FROM ...
WHERE ...

SELECT *
INTO Table2
FROM ...
WHERE ...

...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 16:26:25

like this?

SELECT *
INTO Table1
FROM dbo.MSC Structure
WHERE category is 'PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 16:29:14

quote:
Originally posted by bduncs2001

like this?

SELECT *
INTO Table1
FROM dbo.MSC Structure
WHERE category is 'PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING'


SELECT *
INTO Table1
FROM dbo.MSC Structure
WHERE category IN  ('PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING')

Its better to include some primary key in the WHERE condition sucn as ID Between 1 and 10000 into Table1, ID Between 10001 and 20000 into table2 etc so you dont get duplicate data.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 16:46:05

Getting this error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'category'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'category'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'category'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'category'.

from the previous script

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 16:47:38

Do I need to creat the tables first?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 16:48:15

As the message says, the column name "category" used in the SELECT script seems to be incorrect. Check the name from your table and correct the script appropriately.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 17:02:17

Still getting the same errors and "Category" is spelled correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-17 : 17:05:04

Run the query without the INTO:

SELECT *
FROM dbo.MSC Structure
WHERE category IN ('PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING')

Do you still get an error?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-17 : 17:21:31

quote:
Originally posted by bduncs2001

like this?

SELECT *
INTO Table1
FROM dbo.MSC Structure
WHERE category is 'PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING'

You need to put the table name in brackets if has spaces.


SELECT *
INTO Table1
FROM dbo.[MSC Structure]
WHERE category IN ('PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'MARKING AND LABELING')

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 17:45:55

This is what worked.

SELECT *
INTO Table1
FROM dbo.MSC_Structure
WHERE category IN ('PNEUMATICS', 'PARTS CLEANERS', 'MARKING AND LABELING', 'VALVES')

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-17 : 17:46:52

Thanks for the help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources