A table into a multiple table made in a stored procedure

• satellitekid_12

  SSC Enthusiast

  Points: 127

  More actions

  May 28, 2012 at 4:47 am

  #392355

  I want to split a table into multiple table.

  what I need is a stored procedure that prints a code that goes like this

  /*

  SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_1 FROM LR_TABLE_TEST ORDER BY NEWID()

  SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_2 FROM LR_TABLE_TEST WHERE guidsource NOT IN (

  SELECT guidsource FROM LR_TABLE_TEST_1

  ) ORDER BY NEWID()

  SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_3 FROM LR_TABLE_TEST WHERE guidsource NOT IN (

  SELECT guidsource FROM LR_TABLE_TEST_1 UNION ALL

  SELECT guidsource FROM LR_TABLE_TEST_2

  ) ORDER BY NEWID()

  SELECT * INTO dbo.LR_TABLE_TEST_4 FROM LR_TABLE_TEST WHERE guidsource NOT IN (

  SELECT guidsource FROM LR_TABLE_TEST_1 UNION ALL

  SELECT guidsource FROM LR_TABLE_TEST_2 UNION ALL

  SELECT guidsource FROM LR_TABLE_TEST_3

  ) ORDER BY NEWID()

  */

  I have managed to make the first and second statement (SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_1 FROM LR_TABLE_TEST ORDER BY NEWID()

  SELECT TOP 50000 * INTO dbo.LR_TABLE_TEST_2 FROM LR_TABLE_TEST WHERE guidsource NOT IN (

  SELECT guidsource FROM LR_TABLE_TEST_1

  ) ORDER BY NEWID() )

  what i need is how to insert a record into the third and succeding table which records are not in the previous table as shown above. and to select * records(not top 50000*) into the final table I am stock on this I need a help . thank you somuch

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 28, 2012 at 5:07 am

  #1493732

  If guidsource in dbo.LR_TABLE_TEST is unique, ou can try this way:

  SELECT * INTO dbo.LR_TABLE_TEST_1

  FROM (SELECT ROW_NUMBER() OVER (ORDER BY guidsource) AS RN, * FROM dbo.LR_TABLE_TEST) q

  WHERE RN <= 50000

  SELECT * INTO dbo.LR_TABLE_TEST_2

  FROM (SELECT ROW_NUMBER() OVER (ORDER BY guidsource) AS RN, * FROM dbo.LR_TABLE_TEST) q

  WHERE RN BETWEEN 50001 AND 100000

  SELECT * INTO dbo.LR_TABLE_TEST_3

  FROM (SELECT ROW_NUMBER() OVER (ORDER BY guidsource) AS RN, * FROM dbo.LR_TABLE_TEST) q

  WHERE RN BETWEEN 100001 AND 150000

  SELECT * INTO dbo.LR_TABLE_TEST_4

  FROM (SELECT ROW_NUMBER() OVER (ORDER BY guidsource) AS RN, * FROM dbo.LR_TABLE_TEST) q

  WHERE RN > 150000

  -- if you don't wnat RN column in result you can drop it as:

  ALTER TABLE LR_TABLE_TEST_1 DROP COLUMN RN

  ALTER TABLE LR_TABLE_TEST_2 DROP COLUMN RN

  ALTER TABLE LR_TABLE_TEST_3 DROP COLUMN RN

  ALTER TABLE LR_TABLE_TEST_4 DROP COLUMN RN

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]