Insert into a Row at Specific Position into SQL Server Table with Pk

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



Leave a reply



Submit