Insert 'n' first elements of a table into another and X-n into other table SQL Server

If I have a table with X registers,(here X = 6)

id field
--------
1  1232
2   346
3   349
4   465
5    68
6   545

How could you insert the n (n = 3) first elements in a table, and then the rest X-n into another

table1
id fi开发者_开发百科eld
--------
1  1232
2   346
3   349


table2
id field
--------
1   465
2    68
3   545

---

With RankedItems As
    (
    Select id, field
        , Row_Number() Over ( Order By id ) As Num
    From Registers
    )
Insert Table1( id, field )
Select id, field
From RankedItems
Where Num <= 3

With RankedItems As
    (
    Select id, field
        , Row_Number() Over ( Order By id ) As NewId
    From Registers
    )
Insert Table2( id, field )
Select Num - 3, field
From RankedItems
Where Num > 3

---

I don't know if you're looking for a generic way to do it, or a method specific to your scenario, but let's suppose you are looking for something specific to your scenario.

Since it appears that your table already has a consecutively increasing column, we can use that:

INSERT INTO Table1
SELECT [ID], [Field]
  FROM [TableX]
 WHERE [ID] <= 3

INSERT INTO Table2
SELECT [ID] - 3, [Field]
  FROM [TableX]
 WHERE [ID] >= 4

But that really is a very specific answer. More generally, there are different ways to select a specific number of rows from a table. Another approach, for example, would be:

INSERT INTO Table1
SELECT TOP 3 [ID],[Field]
  FROM [TableX]
 ORDER BY [ID]

INSERT INTO Table2
SELECT TOP 3 [ID] - 3, [Field]
  FROM [TableX]
 ORDER BY [ID] DESC

Also, if the [id] columns of the tables you are inserting into are identity columns, then you can skip inserting them and let identity take care of assigning values for the [id] column, e.g.:

INSERT INTO Table1 ([Field])
SELECT TOP 3 [Field]
  FROM [TableX]
 ORDER BY [ID]

INSERT INTO Table2 ([Field])
SELECT TOP 3 [Field]
  FROM [TableX]
 ORDER BY [ID] DESC

There are other approaches, but these are among the simplest. Their applicability will depend on specifics of your situation. For example, if you need a fixed number of rows to be in table1 and table2 (you specifically want 3 rows), but you have duplicate [id] values in tableX, then the first approach would not work (one of the tables would get more than 3 rows, and the other table would get fewer).

I hope this gives you some ideas to start from. Incidentally, Thomas's approach is excellent if you are concerned that there may be gaps or duplicates in the values of the [id] column.