Select the rows that just inserted

问题:

How can I retrieve the row just inserted?

INSERT INTO LETTRE_VOIT
select rsVoit.NOID, NO_ORDRE, rsOrdre.CODE_DEST, rsOrdre.MODAL_MODE, rsOrdre.MODAL_PORT,
CASE rsOrdre.MODAL_PORT
            WHEN 'false' THEN 'D'
            ELSE 'P'
        END, 
rsOrdre.LIVRS_EXPRS,
CASE rsOrdre.LIVRS_EXPRS
            WHEN 'false' THEN 'L'
            ELSE 'E'
        END, 
  rsOrdre.ENLEV_UNITE, LIBELLE, NBR_COLIS,POID,ENLEV_CREMB,ENLEV_DECL
from ORDRE rsOrdre
inner join
(
  select CODE_DEST,MODAL_MODE, MODAL_PORT, LIVRS_EXPRS,ENLEV_UNITE, ROW_NUMBER() over (order by CODE_DEST) as NOID
  from ORDRE
  group by CODE_DEST,MODAL_MODE,MODAL_PORT,LIVRS_EXPRS,ENLEV_UNITE
) rsVoit on rsVoit.CODE_DEST = rsOrdre.CODE_DEST and rsVoit.MODAL_MODE = rsOrdre.MODAL_MODE
and rsVoit.MODAL_PORT = rsOrdre.MODAL_PORT and rsVoit.LIVRS_EXPRS = rsOrdre.LIVRS_EXPRS
and rsVoit.ENLEV_UNITE = rsOrdre.ENLEV_UNITE

LEFT JOIN T_UNITE ON rsOrdre.ENLEV_UNITE = T_UNITE.NOID
WHERE (STATUT_ORDRE = 3) AND IS_PRINT = 'false' AND (TRANSPORTEUR IN (SELECT ParsedString From dbo.ParseStringList(@Trans)))
order by rsVoit.NOID, NO_ORDRE

SELECT * FROM LETTRE_VOIT WHERE ???

For example:

I inserted 2, it return 2

and then I inserted 3, I want return 3 instead of 5 rows.

Thanks in advance.

Stev

PS: May be I need to use a stored procedure ?

回答1:

I'm not 100% sure what exactly you want back.... but SQL Server has an OUTPUT clause that can output stuff from INSERT and UPDATE and DELETE statements:

INSERT INTO dbo.YourTable(col1, col2, ..., colN)
OUTPUT Inserted.Col1, Inserted.IDCol, Inserted.Col17
VALUES(val1, val2, ...., valN)

Here, you're inserting values and the inserted values for the IDCol (e.g. an INT IDENTITY column), Col1 and Col17.

If just getting back the results into your grid in Mgmt Studio is good enough - then use the OUTPUT clause! Read more about the OUTPUT clause on Books Online

回答2:

If you are using SQL Server

And you know how many row inserted then go through

SELECT top 2 * FROM LETTRE_VOIT order by primaryKeyId desc

put the number of row inserted at place of 2.

It may be help you, If you know the number of inserted rows, and then you can provide the numbers with top keyword

回答3:

How about saving the records in a variable before inserting them or adding a date field and retrieve them by date?