When I add news rows using the Insert into select code, the new rows get added randomly in between the already existing rows, instead of getting added to the end of the table.
I'm using, Insert into Table1 (Name1) select Name from Table2.
Asked
Active
Viewed 349 times
-2
Bogdan Sahlean
- 19,233
- 3
- 42
- 57
Aditya Singh
- 1
- 3
-
Does your table have some identity column already? – Rigerta Jul 07 '17 at 05:42
-
The Table2 has one – Aditya Singh Jul 07 '17 at 05:43
2 Answers
0
SQL tables are modeled after unordered sets, and hence you should not assume that there is any order to your data in the table. The only order which exists is what you specify when you query using ORDER BY, e.g.
SELECT Name1
FROM Table1
ORDER BY Name1
An index can also be thought of a way of ordering your records, but these two are mostly distinct entities from your actual table.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
That I get, but I have to update many columns as well and i want the new rows to be added at the end and in the table, not as a view. – Aditya Singh Jul 07 '17 at 05:48
-
@AdityaSingh Then you don't get it. Just insert your data and don't worry about structure. Of course, statistics may have changed, you might have to alter your tuning, but that is probably beyond the scope of your question. – Tim Biegeleisen Jul 07 '17 at 05:50
0
I agree with Tim's answer. But if you still want the data inserted in the way you want, then you can try to add the primary key yourself which is incremental (like 1,2,3 ... or 10,20,30 ...). Although I don't recommend it, but I think following can help you if you don't want to handle the primary key yourself.
How do I add a auto_increment primary key in SQL Server database?
Waleed Iqbal
- 1,308
- 19
- 35