Splitting a table into two 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

New to SQL Server Programming

Splitting a table into two tables

Author

Topic

ebi007
Starting Member

15 Posts

Posted - 2012-07-16 : 03:42:14

i have a table with only the date column as primary key, i would like to
split that table into two tables with distinct records.

ex: table A
name birthdate
erica 2012-07-1987 12:24:45
ocean 2012-05-1990 07:20:00

table A1
name birthdate
erica 2012-07-1987 12:24:45
ocean 2012-05-1990 07:20:00

table A2
josé 2012-11-1982 12:00:35
jessica 2012-07-1987 05:11:45

thanks

---------------------------
there is no limits only God can stop me.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 04:01:56

How do you want to do the split (and why)?
You can start off with two views with queries to simulate the two tables.
Then maybe decide if you need to go any further.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ebi007
Starting Member

15 Posts

Posted - 2012-07-16 : 04:14:54

the two tables will only be temp tables , will drop them afterwards.
in fact i want to get the other half part of the table A
i mean i need the 2 last records in another table.

---------------------------
there is no limits only God can stop me.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-16 : 04:33:43

Use SELECT * INTO statement with your requried filter in WHERE clause. Then with same filter remove from first one.

SELECT * INTO NewTable FROM Old Table WHERE conditionhere

--------------------------
http://connectsql.blogspot.com/

ebi007
Starting Member

15 Posts

Posted - 2012-07-16 : 06:13:24

[quote]Originally posted by lionofdezert

Use SELECT * INTO statement with your requried filter in WHERE clause. Then with same filter remove from first one.

SELECT * INTO NewTable FROM Old Table WHERE conditionhere

--------------------------
Can you use my previous example to write the query please ?

---------------------------
there is no limits only God can stop me.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 06:40:00

select *
into TableA1
from TableA
where birthdate in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

select *
into TableA2
from TableA
where birthdate not in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

Back to my original question.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ebi007
Starting Member

15 Posts

Posted - 2012-07-16 : 08:39:33

quote:
Originally posted by nigelrivett

select *
into TableA1
from TableA
where birthdate in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

select *
into TableA2
from TableA
where birthdate not in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

Back to my original question.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

birthdate is in smalldatetime format not varchar.

---------------------------
there is no limits only God can stop me.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 09:14:41

Shouldn't matter.
The problem is with your data
2012-07-1987 12:24:45
You don't have valid dates.

If you correct the values it should be ok.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 09:53:27

quote:
Originally posted by ebi007

quote:
Originally posted by nigelrivett

select *
into TableA1
from TableA
where birthdate in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

select *
into TableA2
from TableA
where birthdate not in ('2012-07-1987T12:24:45', '2012-05-1990T07:20:00')

Back to my original question.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

why are date values like this? why is day portion four digits?
birthdate is in smalldatetime format not varchar.

---------------------------
there is no limits only God can stop me.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources