Insert SQL Server row into specific position in formatted table
To help you clear some things up:
- ID field seems to be int and not unique identifier
- Insert statements are not made randomly in the DB, they go to the last record. E.G if you have 201,203 and you insert 202 it will go after 203.
- The way you select the records (and thus they fetched and displayed) is another thing. You may run a query that return 202 before 203 but this doesn't mean that this is the way that are stored in the DB
If ID are actually of type int I recon you make them auto incremental
Select * from Customer Order by Id_ Desc
Insert row into SQL table at a specified index
Firstly, best practice is to never ever modify the primary key of a row once it is inserted (I assume id
is your primary key?). Just Don't Do It™.
Instead, create an additional column (called importance
/priority
/something, and modify that field as necessary.
That said, the query would look something like:
START TRANSACTION;
# increment every importance value that needs incrementing to make way for the new row:
UPDATE mytable SET importance = importance + 1 WHERE importance >= newimportance;
# insert the new row:
INSERT INTO mytable (title, text, importance) VALUES ('newtitle', 'newtext', newimportance);
COMMIT;
How to insert row into a SQL table with auto-increment primary key
Like so:
INSERT INTO dbo.InvestmentJourneys
DEFAULT VALUES;
SQL - Inserting a row and returning primary key
For MS SQL Server:
SCOPE_IDENTITY()
will return you the last generated identity value within your current scope:
SELECT SCOPE_IDENTITY() AS NewID
copy and insert row in same table with new primary key+php
Because you're trying to insert every field in that row, which includes the primary key. If you want to specify a subset of fields, specify a subset of fields:
INSERT INTO messages (ColumnA, ColumnB, Etc)
SELECT ColumnA, ColumnB, Etc
FROM messages WHERE id='$id'
That way you're not also inserting a value into id
.
(This is of course assuming that the database auto-generates the primary key. If it doesn't, you'd need to insert whatever value would be required for a primary key.)
As an aside, the use of what appears to be a PHP variable (and explicit quotes) in your SQL code strongly implies that you are likely vulnerable to SQL injection. Right now is the best time to correct that.
Insert into first row when the table has a primary key set
One of the ways to fix it is to update the record with the ID=1 to '--':
update yourTable set Value = '--' where id = 1
Then you will be required to re-insert the first record into the table:
INSERT INTO yourTable (Value)
VALUES('the value that was originally inserted as 1')
However, if the order of the already inserted records is important then you can insert the '--' value as the ID = 0. In this case you need to disable the IDENTITY column using the SET IDENTITY_INSERT:
SET IDENTITY_INSERT yourTable ON
INSERT INTO yourTable (ID, Value)
VALUES(0, '--')
SET IDENTITY_INSERT yourTable OFF
This way the order of inserted records will be preserved and the '--' will be inserted with the ID of 0
BTW, for mySQL you can insert into the IDENTITY column by default
Insert row in proper order on table based off of primary key.
There is no order you can rely on.
Unless you specify ORDER BY, there are no guarantees about what rows
will be returned first.But in practice, the order will typically match the clustered index
order, and will not vary between calls. Don't rely on this behaviour.
Does MySQL's LIMIT keyword guarantee order of returned data?
There is no default sort order. Even if the table has a clustered
index, you are not guaranteed to get the results in that order. You
must use an order by clause if you want a specific order.
SQL best practice to deal with default sort order
Just insert the data; tables are not fundamentally ordered. The only ordering of table results occurs when you define it yourself in your select statement.
Is it possible to insert data into the mid section of a table using the INSERT command?
You can't. There is no such thing.
To be precise: in MyISAM engine, current order is whatever the order
was when the last ALTER TABLE ORDER BY was performed, modified by all
the deletions and insertions since.InnoDB just sorts the tables as it wishes.
view current table order
However be aware of the advice from another poster above: the order in
which rows are returned without an ORDER BY clause is arbitrary and
may change without notification. It would be best to amend your table
if at all possible.
Reverse the “natural order” of a MySQL table without ORDER BY?
.... and maaany more.
Related Topics
Executing a Stored Procedure Within a Stored Procedure
Best Practices for Inserting/Updating Large Amount of Data in SQL Server 2008
SQL Function to Get Count of How Many Times String Appears in Column
SQL Do Inner Join If Condition Met
Postgresql:How to Select Top N Percent(%) Entries from Each Group/Category
SQL Server Rounding Issue Where There Is 5
4 Byte Unsigned Int in SQL Server
Oracle SQL Developer: How to Transpose Rows to Columns Using Pivot Function
Go with SQL Server Driver Is Unable to Connect Successfully, Login Fail
Copy Multiple Records with a Master-Details Relationship
SQL Statements with Equals VS In
SQL Query for Point-In-Polygon Using Postgresql
"Similar Posts" Like Functionality Using Ms SQL Server
SQL Insert into with Subquery and Value
On Update Current_Timestamp and JPA
There Is Already an Object Named '#Tmptable' in the Database
SQL Pivot Select from List (In Select)
Postgresql Error: Function To_Tsvector(Character Varying, Unknown) Does Not Exist