Articolo
09/28/2010

Esempi di istruzioni INSERT (Transact-SQL)

In questo argomento vengono forniti alcuni esempi relativi all'utilizzo dell'istruzione INSERT. Gli esempi sono raggruppati in base alle categorie seguenti.

Categoria	Elementi di sintassi inclusi
Sintassi di base	INSERT • costruttore di valori di tabella
Gestione dei valori di colonna	IDENTITY • NEWID • valori predefiniti • tipi definiti dall'utente
Inserimento di dati da altre tabelle	INSERT…SELECT • INSERT…EXECUTE • espressione di tabella comune WITH • TOP
Specifica di oggetti di destinazione diversi dalle tabelle standard	Viste • variabili di tabella
Inserimento di righe in una tabella remota	Server collegato • funzione per set di righe OPENQUERY • funzione per set di righe OPENDATASOURCE
Caricamento bulk di dati da tabelle o file di dati	INSERT…SELECT • funzione OPENROWSET
Override del comportamento predefinito di Query Optimizer tramite hint	Hint di tabella
Acquisizione dei risultati dell'istruzione INSERT	Clausola OUTPUT

Sintassi di base

Negli esempi contenuti in questa sezione vengono illustrate le funzionalità di base dell'istruzione INSERT tramite la sintassi minima richiesta.

A. Inserimento di una sola riga di dati

Nell'esempio seguente viene inserita una riga nella tabella Production.UnitMeasure. Le colonne nella tabella sono UnitMeasureCode, Name e ModifiedDate. Poiché i valori per tutte le colonne vengono specificati ed elencati nello stesso ordine delle colonne nella tabella, non è necessario specificare i nomi delle colonne nell'elenco delle colonne.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

B. Inserimento di più righe di dati

Nell'esempio seguente viene utilizzato il costruttore di valori di tabella per inserire tre righe nella tabella Production.UnitMeasure in un'unica istruzione INSERT. Poiché i valori per tutte le colonne vengono specificati ed elencati nello stesso ordine delle colonne nella tabella, non è necessario specificare i nomi delle colonne nell'elenco delle colonne.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO

C. Inserimento di dati in un ordine diverso rispetto alle colonne della tabella

Nell'esempio seguente viene utilizzato un elenco di colonne per specificare in modo esplicito i valori inseriti in ogni colonna. L'ordine delle colonne nella tabella Production.UnitMeasure è UnitMeasureCode, Name, ModifiedDate. Le colonne, tuttavia, non sono elencate in tale ordine in column_list.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

Gestione dei valori di colonna

Negli esempi contenuti in questa sezione vengono illustrati i metodi di inserimento dei valori nelle colonne definite con una proprietà IDENTITY, un valore DEFAULT oppure definite con tipi di dati quali uniqueidentifer o colonne di tipo definito dall'utente.

A. Inserimento di dati in una tabella con colonne che presentano valori predefiniti

Nell'esempio seguente viene illustrato l'inserimento di righe in una tabella con colonne che generano automaticamente un valore o presentano un valore predefinito. Column_1 è una colonna calcolata che genera automaticamente un valore tramite il concatenamento di una stringa con il valore inserito in column_2. Column_2 è definito con un vincolo predefinito. Se per questa colonna non viene specificato un valore, viene utilizzato il valore predefinito. Column_3 viene definita con il tipo di dati rowversion, che genera automaticamente un numero binario incrementale univoco. Column_4 non genera automaticamente un valore. Quando non viene specificato alcun valore per questa colonna, viene inserito un valore NULL. Le istruzioni INSERT inseriscono righe che contengono valori solo per alcune delle colonne. Nell'ultima istruzione INSERT non viene specificata alcuna colonna e solo i valori predefiniti vengono inseriti tramite la clausola DEFAULT VALUES.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

B. Inserimento di dati in una tabella con una colonna Identity

Nell'esempio seguente vengono illustrati diversi metodi per l'inserimento di dati in una colonna Identity. Le prime due istruzioni INSERT consentono di generare valori Identity per le nuove righe. La terza istruzione INSERT ignora la proprietà IDENTITY per la colonna con l'istruzione SET IDENTITY_INSERT e inserisce un valore esplicito nella colonna Identity.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2) 
    VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO

C. Inserimento di dati in una colonna uniqueidentifier tramite NEWID()

Nell'esempio seguente viene utilizzata la funzione NEWID() per ottenere un GUID per column_2. Diversamente dalle colonne Identity, Motore di database non genera automaticamente valori per le colonne con il tipo di dati uniqueidentifier, come illustrato nella seconda istruzione INSERT.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2) 
    VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2
FROM dbo.T1;
GO

D. Inserimento di dati in colonne di tipo definito dall'utente

Le istruzioni Transact-SQL seguenti consentono di inserire tre righe nella colonna PointValue della tabella Points. In questa colonna viene utilizzato un tipo CLR definito dall'utente. Il tipo di dati Point è costituito da valori integer X e Y esposti come proprietà del tipo definito dall'utente. È necessario utilizzare la funzione CAST o CONVERT per eseguire il cast dei valori X e Y delimitati da virgole al tipo Point. Le prime due istruzioni utilizzano la funzione CONVERT per convertire un valore stringa nel tipo Point, mentre la terza istruzione utilizza la funzione CAST. Per ulteriori informazioni, vedere Manipolazione dei dati UDT.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Inserimento di dati da altre tabelle

Negli esempi contenuti in questa sezione vengono illustrati i metodi per l'inserimento di righe da una tabella in un'altra.

A. Utilizzo delle opzioni SELECT ed EXECUTE per inserire dati da altre tabelle

Nell'esempio seguente viene illustrato come inserire dati da una tabella in un'altra tramite l'istruzione INSERT…SELECT o INSERT…EXECUTE. Ogni metodo è basato su un'istruzione SELECT su più tabelle, in cui l'elenco di colonne include un'espressione e un valore letterale.

La prima istruzione INSERT utilizza un'istruzione SELECT per recuperare i dati dalle tabelle di origine (Employee, SalesPerson e Contact) e archivia il set di risultati nella tabella EmployeeSales . Nella seconda istruzione INSERT viene utilizzata la clausola EXECUTE per chiamare una stored procedure che contiene l'istruzione SELECT. Nella terza istruzione INSERT viene utilizzata la clausola EXECUTE per fare riferimento all'istruzione SELECT come stringa letterale.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  EmployeeID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName, 
        sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp  
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName, 
    sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp 
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

B. Utilizzo dell'espressione di tabella comune WITH per la definizione dei dati inseriti

Nell'esempio seguente viene creata la tabella NewEmployee. Le righe di una o più tabelle da inserire nella tabella NewEmployee vengono definite tramite un'espressione di tabella comune (EmployeeTemp). L'istruzione INSERT fa riferimento alle colonne nell'espressione di tabella comune.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
        e.EmployeeID, c.LastName, c.FirstName, c.Phone,
        a.AddressLine1, a.City, sp.StateProvinceCode, 
        a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

C. Utilizzo della clausola TOP per limitare i dati inseriti dalla tabella di origine

Nell'esempio seguente viene utilizzata la clausola TOP per limitare il numero di righe inserite nella tabella NewEmployee dalla tabella Employee. Nell'esempio vengono inseriti dati relativi agli indirizzi per il primo set casuale di 10 dipendenti dalla tabella Employee. Viene quindi eseguita l'istruzione SELECT per verificare il contenuto della tabella NewEmployee.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
INSERT TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.EmployeeID, c.LastName, c.FirstName, c.Phone,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID;
GO
SELECT  EmployeeID, LastName, FirstName, Phone,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

Specifica di oggetti di destinazione diversi dalle tabelle standard

Negli esempi contenuti in questa sezione viene illustrato come inserire righe specificando una vista o una variabile di tabella.

A. Inserimento di dati specificando una vista

Nell'esempio seguente viene specificato il nome di una vista come oggetto di destinazione. La nuova riga, tuttavia, viene inserita nella tabella di base sottostante. L'ordine dei valori nell'istruzione INSERT deve corrispondere all'ordine delle colonne della vista. Per ulteriori informazioni, vedere Modifica di dati tramite una vista.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS 
SELECT column_2, column_1 
FROM T1;
GO
INSERT INTO V1 
    VALUES ('Row 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

B. Inserimento di dati in una variabile di tabella

Nell'esempio seguente viene specificata una variabile di tabella come oggetto di destinazione.

USE AdventureWorks;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

Inserimento di righe in una tabella remota

Negli esempi di questa sezione viene illustrato come inserire righe in una tabella di destinazione remota tramite un server collegato o una funzione per i set di righe per fare riferimento alla tabella remota.

A. Inserimento di dati in una tabella remota tramite un server collegato

Nell'esempio seguente vengono inserite righe in una tabella remota. L'esempio inizia con la creazione di un collegamento all'origine dati remota tramite sp_addlinkedserver. Il nome del server collegato, MyLinkServer, viene successivamente specificato come parte del nome di oggetto in quattro parti nel formato server.catalogo.schema.oggetto.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks';
GO

USE AdventureWorks;
GO
-- Specify the remote data source in the FROM clause using a four-part name 
-- in the form linked_server.catalog.schema.object.

INSERT INTO MyLinkServer.AdventureWorks.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

B. Inserimento di dati in una tabella remota tramite una funzione OPENQUERY

Nell'esempio seguente viene inserita una riga in una tabella remota specificando la funzione per i set di righe OPENQUERY. Viene utilizzato il nome del server collegato creato nell'esempio precedente.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

B. Inserimento di dati in una tabella remota tramite una funzione OPENDATASOURCE

Nell'esempio seguente viene inserita una riga in una tabella remota specificando la funzione per i set di righe OPENDATASOURCE. Specificare un nome server valido per l'origine dati utilizzando il formato nome_server oppure nome_server\nome_istanza.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

Caricamento bulk di dati da tabelle o file di dati

Negli esempi di questa sezione vengono illustrati due metodi per eseguire il caricamento bulk dei dati in una tabella tramite l'istruzione INSERT.

A. Inserimento di dati in un heap con registrazione minima

Nell'esempio seguente viene creata una nuova tabella (heap) in cui vengono inseriti dati da un'altra tabella con registrazione minima. L'esempio presuppone che il modello di recupero del database AdventureWorks sia impostato su FULL. Per assicurare l'utilizzo della registrazione minima, il modello di recupero del database AdventureWorks viene impostato su BULK_LOGGED prima che le righe vengano inserite e reimpostato su FULL dopo l'istruzione INSERT INTO…SELECT. Inoltre, viene specificato l'hint TABLOCK per la tabella di destinazione Sales.SalesHistory. In tal modo, si assicura l'utilizzo da parte dell'istruzione di uno spazio minimo nel log delle transazioni con risultati efficienti.

USE AdventureWorks;
GO
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
    SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL,
    LineTotal money NOT NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
    ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
    (SalesOrderID, 
     SalesOrderDetailID,
     CarrierTrackingNumber, 
     OrderQty, 
     ProductID, 
     SpecialOfferID, 
     UnitPrice, 
     UnitPriceDiscount,
     LineTotal, 
     rowguid, 
     ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;

B. Utilizzo della funzione OPENROWSET con BULK per eseguire il caricamento bulk dei dati in una tabella

Nell'esempio seguente vengono inserite righe da un file di dati in una tabella specificando la funzione OPENROWSET. Per l'ottimizzazione delle prestazioni, viene specificato l'hint di tabella IGNORE_TRIGGERS. Per ulteriori esempi, vedere Importazione di dati per operazioni bulk utilizzando BULK INSERT o OPENROWSET(BULK...).

-- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.

INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

Override del comportamento predefinito di Query Optimizer tramite hint

Negli esempi contenuti in questa sezione viene illustrato come utilizzare gli hint di tabella per eseguire temporaneamente l'override del comportamento predefinito di Query Optimizer durante l'elaborazione dell'istruzione INSERT.

Attenzione
Poiché Query Optimizer di SQL Server consente in genere di selezionare il piano di esecuzione migliore per una query, gli hint devono essere utilizzati solo se strettamente necessario da sviluppatori e amministratori di database esperti.

A. Utilizzo dell'hint TABLOCK per specificare un metodo di blocco

Nell'esempio seguente viene impostata l'acquisizione di un blocco esclusivo (X) sulla tabella Production.Location. Tale blocco viene mantenuto attivo fino al termine dell'istruzione INSERT.

USE AdventureWorks;
GO
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

Acquisizione dei risultati dell'istruzione INSERT

Negli esempi contenuti in questa sezione viene illustrato come utilizzare la clausola OUTPUT per restituire informazioni da (o espressioni basate su) ogni riga interessata da un'istruzione INSERT. Questi risultati possono essere restituiti all'applicazione di elaborazione per l'utilizzo nei messaggi di conferma, l'archiviazione e altri scopi simili dell'applicazione.

A. Utilizzo della clausola OUTPUT con un'istruzione INSERT

Nell'esempio seguente viene inserita una riga nella tabella ScrapReason e viene utilizzata la clausola OUTPUT per restituire i risultati dell'istruzione alla variabile di tabella @MyTableVar. Poiché la colonna ScrapReasonID è definita con una proprietà IDENTITY, per tale colonna non viene specificato un valore nell'istruzione INSERT. Si noti tuttavia che il valore generato dal Motore di database per tale colonna viene restituito nella clausola OUTPUT nella colonna INSERTED.ScrapReasonID.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

B. Utilizzo della clausola OUTPUT con colonne Identity e calcolate

Nell'esempio seguente viene creata la tabella EmployeeSales, in cui vengono quindi inserite diverse righe tramite un'istruzione INSERT con un'istruzione SELECT per il recupero dei dati dalle tabelle di origine. La tabella EmployeeSales contiene una colonna Identity (EmployeeID) e una colonna calcolata (ProjectedSales). Poiché questi valori vengono generati dal Motore di database durante l'operazione di inserimento, nessuna di queste colonne può essere definita in @MyTableVar.

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

C. Inserimento dei dati restituiti da una clausola OUTPUT

Nell'esempio seguente vengono acquisiti i dati restituiti dalla clausola OUTPUT di un'istruzione MERGE e tali dati vengono inseriti in un'altra tabella. L'istruzione MERGE aggiorna la colonna Quantity della tabella ProductInventory con frequenza giornaliera, in base agli ordini elaborati nella tabella SalesOrderDetail. Vengono inoltre eliminate le righe dei prodotti le cui scorte vengono azzerate. Nell'esempio vengono acquisite le righe eliminate, che vengono inserite in un'altra tabella denominata ZeroInventory che tiene traccia dei prodotti senza scorte.

USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20030401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
GO
SELECT ProductID FROM Production.ZeroInventory;

Vedere anche

Riferimento

INSERT (Transact-SQL)

IDENTITY (proprietà) (Transact-SQL)

Condividi tramite