July 15, 2020 at 4:16 am
Hi,
I having a table called TableA, the record in TableA already in order sequence as below:-
ID Name Age
1 Lee 32
2 Liu 33
3 Magi 31
...
..
and so on
When i run below
Insert into TableB (Name, Age)
Select Name, Age from TableA order by Id asc
Note, in TableB, there is [id] as auto increment column, so i never list on my insert query.
Issues:-
Could it be the reason, TableB having other real-time insertion /interruption?
July 15, 2020 at 10:35 am
In relational databases, relations (tables) are unordered sets.
It looks are though you just want to copy the id from TableA to TableB:
SET IDENTITY_INSERT TableB ON;
INSERT INTO TableB (ID, [Name], Age)
SELECT ID, [Name], Age
FROM TableA;
SET IDENTITY_INSERT TableB OFF;
ps I presume this is an example as one should always hold DOB and not Age.
July 15, 2020 at 5:24 pm
Also, a query from a SELECT is never in any order without an ORDER BY clause.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 15, 2020 at 9:07 pm
>> I have a table called TableA, the record [sic] in TableA already in order sequence as below: <<
NO! Rows are nothing like a records. By definition, a table has no ordering. This is usually covered in the first two or three chapters of any book on SQL or RDBMS. You also failed to post any DDL in the picture that you did post is completely wrong.
1) there is no such thing as a generic identifier called "id" in RDBMS An identifier must identify something in particular and not a vague generic thing. Please read a book on basic logic and learn the law of identity (to be is to be something in particular; to be nothing in particular, or everything in general, is to be nothing at all).
2) a table must have a key and it must be declared as such
3) likewise, the data element "name" is too generic to be valid. It must be the name of something in particular. When you finally get around to reading a basic book on data modeling, you will find things like this are called "attribute properties" in the ISO standards use an score to connect them to the name of the attribute to which they belong
4) never store the age of something. Think about it! It's constantly changing! We store a birthdate or starting date and compute the age as needed.
5) the goal of all databases, RDBMS included, is to reduce redundancy. As you seek to increase it by having the same facts repeated into different tables.
6) Identifiers use a nominal scale. Therefore, identifiers cannot be numerics because you do know computations on them.
CREATE TABLE Students
(student_id CHAR(3) NOT NULL PRIMARY KEY,
student_name VARCHAR(10) NOT NULL,
birth_date DATE NOT NULL);
INSERT INTO Students
VALUES
('001', 'Lee', '1988-01-12'),
('002', 'Liu', '1987-01-05'),
('003', 'Magi', '1989-01-10'),
>> Record is [sic] sometime in correct order, but sometime is not. <<
Again, there is no concept of ordering in RDBMS or SQL. Rows are located by keys, not by physical ordering. That was punchcard and magnetic tapes over 50 years ago
You're doing everything completely wrong.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply