How to move data from one table to multiple tables

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

General SQL Server Forums

Database Design and Application Architecture

How to move data from one table to multiple tables

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-03-18 : 05:49:58

Hello All,

I do have one large table, say "emp" having 80 columns. now as the requirement changes, i have to partition the "emp" table to 8 tables.

I want all of my existing data ["emp" table data] to be there in my new tables . i don't want to delete the existing data from "emp" table.

Cal any one please help me out to resolve this issue.

Thanks
Prashant Hirani

pootle_flump

1064 Posts

Posted - 2008-03-18 : 06:35:24

I would think hard about it first TBH - I bet a supertype\ subtype or even the current design would be fine.

In any event, why not

INSERT T1 (col1, col2...)
SELECT col1, col2
FROM emp
WHERE emp_type = 1

INSERT T2 (col1, col2...)
SELECT col1, col2
FROM emp
WHERE emp_type = 2

etc.?

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-03-18 : 07:36:15

Thanks for reply.

Is there any other way?

Thanks
Prashant Hirani

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-18 : 07:46:13

how do want to partition it? splitting off columns or rows? how about using views instead?

Em

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-03-18 : 08:03:38

how do want to partition it? splitting off columns or rows? how about using views instead?

I want to partition it on the basis of columns.

i dont know abt views, so please guide me if it's a suitable option.

Thanks
Prashant Hirani

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-18 : 08:07:41

you can read about Views in Books online, it may well be a good option for you.

you'd define 8 views with just the columns you want in each then your users can query the views in the same way they would a table but the data itself remains in the underlying table.

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources