Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table? By doing so technically splitting the table into smaller parts.

share|improve this question

closed as unclear what you're asking by Paul White, Phil, RolandoMySQLDBA, Kin, Derek Downey Jun 4 '14 at 18:49

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Why are you performing a keyword search in Excel, when you could perform the search in SQL Server? Then you could just export the rows that match if you really need to. What you're asking for is like bringing the whole deli home, picking out the meat and cheese you want, and throwing everything else away. Maybe you need to solve the "very complex" requirements - I can't even begin to envision a search that you can perform in Excel that you couldn't perform in SQL Server. – Aaron Bertrand Jun 4 '14 at 14:38
@Sarge elaborate on THAT and help us solve THAT problem. Pulling 16 GB of data into Excel is not going to end well, I promise, regardless of how you break the table up. – Aaron Bertrand Jun 4 '14 at 14:45
... the sum of which will be 16 GB, and if they are in separate files, then it's really going to be a usability problem if people have to repeat their search across all of those files, and aggregate the results together manually. I'm still missing the benefit of using Excel here, other than the hand-waving of "particular discovery software." – Aaron Bertrand Jun 4 '14 at 14:49
Does your table have a primary key? Are the rows fixed size or variable width? Can you post your actual table structure in the question and indicate whether you need all of the columns or only some? Also can you indicate what you want to do if data is changing as this split happens? – Aaron Bertrand Jun 4 '14 at 14:59
SELECT * INTO dbo.newtable FROM dbo.existingtable WHERE column >= 1 AND column <= 2000; - just note that this will give you a predictable number of rows, but won't control size of row. – Aaron Bertrand Jun 4 '14 at 15:30
up vote 4 down vote accepted

Addressing the question behind the question:

Maybe you can answer this. Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table?

If you already have a column by which you can "partition" the table, then you can do something like this:

 INTO dbo.newtable_0001_to_2000 
 FROM dbo.existingtable 
 WHERE column >= 1 AND column <= 2000;

 INTO dbo.newtable_2001_to_4000 
 FROM dbo.existingtable 
 WHERE column >= 2001 AND column <= 4000;


Just note that this will give you a predictable number of rows, but won't control the size of the rows, so depending on the data in each "partition" these tables will not likely be of equal sizes.

share|improve this answer

You can add a column with a unique row number by using the row_number() function:

            FLAGS ,
            USERID ,
            FOLDER ,
            FOLDER2 ,
            ACCOUNTNO ,
            CREATEON ,
            MAILSIZE ,
            MAILDATE ,
            MAILTIME ,
            MAILREF ,
            LOPRECID ,
            RFC822_Old ,
            RECID ,
            MAILID ,
            EXT ,
            RFC822 ,

However, I strongly agree with Aaron Bertrand that you are creating inefficiencies, possibly on a massive scale. SQL is much better at searching/filtering data than Excel. You should, at the very least, consider using any parts of the search to filter the data in SQL BEFORE you pull it down to Excel, that will reduce the amount of data.

share|improve this answer
Aaron comment was exactly what i wanted. Thank you for your answer. I should point out i was never going to perform the search within the file produced (be it excel or some other). Question really was how I select a bunch of a rows in some form of order via unique value and copy them into another. that's it. I had added a new column before with the number correct number value for each. – Sarge Jun 4 '14 at 15:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.