How to split records evenly into 4 tables from one table is sql

Question

How to split records evenly into 4 tables from one table is sql

Learner44

SSCarpal Tunnel

Points: 4466
More actions
April 7, 2016 at 6:49 am

#309771

Hi,
I have to design solution that will help me out to load data into 4 tables from 1 master table.
It's simple and I guess that's why it's hard!!
all that function or ssis package suppose to do is following.
1. Count total number of rows in a master table
2. Divide by 4
3. Load into table 1,2,3 and 4.
every time we run this function wipe out 4 tables and do the above process again and name of the main table and destination tables will be always same.
example:
Master Table has 4200 records than,
Table A will get 1-1000
Table B will get 1001-2000
Table C will get 2001-3000
Table D will get 3001-4200.
Please help!
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

SSIS and Conditional Split on a computed 1-4 sequence. 4 outputs from the conditional split, one into each of your 4 tables. Easy-peasy, lemon-squeezy!! 😀

https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-conditional-split/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could use the NTILE function.

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

E2(n) AS(

SELECT a.n FROM E a, E b

),

E4(n) AS(

SELECT a.n FROM E2 a, E2 b

),

cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E4

)

SELECT n, NTILE(4) OVER(ORDER BY n)

FROM cteTally;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

nTile window function can create a column with numbers 1 to 4. After that it should be easy to do an INSERT ... WHERE Tile = 1...

CREATE TABLE #Test (

SomeNumber INT

)

INSERT INTO #Test

(SomeNumber

)

SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM sys.columns

-- query

SELECT SomeNumber, NTILE(4) OVER (ORDER BY SomeNumber) AS Tile

FROM #Test

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

Learner44

SSCarpal Tunnel

Points: 4466

April 7, 2016 at 8:00 am

#1870494

THANKS

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 5

Learner44

SSCarpal Tunnel

Points: 4466

April 7, 2016 at 8:00 am

#1870496

THANKS

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 6

GilaMonster (4/7/2016)
nTile window function can create a column with numbers 1 to 4. After that it should be easy to do an INSERT ... WHERE Tile = 1...
CREATE TABLE #Test (
SomeNumber INT
)
INSERT INTO #Test
(SomeNumber
)
SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM sys.columns
-- query
SELECT SomeNumber, NTILE(4) OVER (ORDER BY SomeNumber) AS Tile
FROM #Test

THANK YOU

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Since you are wanting to put 1/4 rows in each of 4 different tables you MUST ensure your "sequence generator", whatever you use, is REPEATABLY RIGOROUS, or you run the risk of bad data. Specifically. any form of ordering must be completely repeatable because you will need to do it 4 times on the main table if you do this in SQL Server. That is one of the reasons I mentioned SSIS, since it can do this in a single pass.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service