Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I SELECT top 500 rows from table1 and INSERT them to table2?

Tags:

sql

tsql

insert

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?

like image 838
pencilCake Avatar asked Dec 29 '11 13:12

pencilCake


People also ask

What does SELECT * from table1 table2 do?

It produces a cartesian product, so the number of rows in the result set will be the number of rows from table1 multiplied by number of rows from table2 (assuming there are no constraints in the WHERE clause). It effectively pairs each row from table1 with a row coming from table2 .

How do I insert the same data in multiple rows?

Select all the cells where you want to enter the same data Put the cursor to the first cell in the column (or the second one if your Table has headers), then press Shift+Ctrl+End to go to the end of your table, hold Shift and press the Left key repeatedly until only the needed column gets selected.

How do I insert multiple rows from one table to another?

You can use the SELECT and INSERT statements to copy rows from one SQL table to another. This is the basic syntax: INSERT INTO table_name1 (columns) SELECT columns FROM table_name2; In this example, I have created a cats table with three rows in it with the same column names as the dogs table.


2 Answers

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
like image 90
gbn Avatar answered Oct 31 '22 16:10

gbn


Something like this:

INSERT INTO t1
SELECT TOP 500 * FROM t2

You select the top 500, and insert them.

like image 25
Tom van der Woerdt Avatar answered Oct 31 '22 16:10

Tom van der Woerdt