help to split data into 2 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

SQL Server 2008 Forums

Transact-SQL (2008)

help to split data into 2 tables

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-09-01 : 10:57:10

declare @test table
(month int, serialno int, statusID int)

insert into @test
select 1, 2220,1 union all
SELECT 2, 2220,1 UNION ALL
SELECT 3, 2220,3 UNION ALL
SELECT 4, 2220,1 UNION ALL
SELECT 5, 2220,1 UNION ALL
SELECT 6, 2220,1 UNION ALL
SELECT 7, 2220,1 UNION ALL
SELECT 8, 2220,1 UNION ALL
SELECT 1, 4440,2 UNION ALL
SELECT 2, 4440,3 UNION ALL
SELECT 3, 4440,1 UNION ALL
SELECT 4, 4440,1 UNION ALL
SELECT 5, 4440,1 UNION ALL
SELECT 6, 4440,2 UNION ALL
SELECT 7, 4440,2 UNION ALL
SELECT 1, 9990,1 UNION ALL
SELECT 2, 9990,1 UNION ALL
SELECT 3, 9990,2 UNION ALL
SELECT 4, 9990,3 UNION ALL
SELECT 5, 9990,2 UNION ALL
SELECT 6, 9990,2

select * from @test

Data should get split in 2 tables.
As soon as a value of 3 is found for a given month the reminaing rows after that month should be removed from @test
and put into another table or data be split into 2 tables. for each serialno there is only 1 value of 3 always.
So we need to split the above table in 2 tables which should look like as following tables.
this can be done using SQL 2005, 2008.

declare @test1 table
(month int, serialno int, statusID int)

insert into @test1
select 1, 2220,1 union all
SELECT 2, 2220,1 UNION ALL
SELECT 3, 2220,3 UNION ALL
SELECT 1, 4440,2 UNION ALL
SELECT 2, 4440,3 UNION ALL
SELECT 1, 9990,1 UNION ALL
SELECT 2, 9990,1 UNION ALL
SELECT 3, 9990,2 UNION ALL
SELECT 4, 9990,3

select * from @test1

declare @test2 table
(month int, serialno int, statusID int)

insert into @test2
SELECT 4, 2220,1 UNION ALL
SELECT 5, 2220,1 UNION ALL
SELECT 6, 2220,1 UNION ALL
SELECT 7, 2220,1 UNION ALL
SELECT 8, 2220,1 UNION ALL
SELECT 3, 4440,1 UNION ALL
SELECT 4, 4440,1 UNION ALL
SELECT 5, 4440,1 UNION ALL
SELECT 6, 4440,2 UNION ALL
SELECT 7, 4440,2 UNION ALL
SELECT 1, 9990,1 UNION ALL
SELECT 5, 9990,2 UNION ALL
SELECT 6, 9990,2

select * from @test2

-----------------------------------------------------------------------------------------------
Ashley Rhodes

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-01 : 12:27:35

so how do we know what order to sort these in to find "As soon as a value of 3 is found for a given month".
You DO realize that the order you enter them into the database means nothing?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources