How can I SELECT top 500 rows from table1 and INSE

问题:

I have completely same defined 2 tables : t2 and t1.

t2 has 1000 rows and t1 is totally empty.

How can I SELECT top 500 rows from t2 and INSERT them to t1?

回答1:

I'll use "emptyTable" and "populatedTable" because the questions is confusing

Important TOP without an ORDER BY gives 500 arbitrary rows. There is never an implied or natural order to a table

INSERT INTO emptyTable
SELECT TOP 500 * 
FROM populatedTable
ORDER BY What -- need this to define TOP 500

The lack of column list here is usually defined as bad practice but works only if

• the tables are identical
• there is no IDENTITY column in emptyTable

Edit:

ORDER BY is required to guarantee row order. See these. It's also in the ANSI standard

• http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx
• http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

回答2:

Something like this:

INSERT INTO t1
SELECT TOP 500 * FROM t2

You select the top 500, and insert them.

回答3:

insert t2(fieldslist)
select top 500 fieldslist from t1

回答4:

If both tables have the same schema, it's as simple as:

INSERT INTO t1
SELECT TOP 500 * 
FROM t2