UPDATE From SubQuery

UPDATE [TargetTable]
SET 
	[TargetTable].First = [OtherTable].Notes, 
	[TargetTable].Last = [OtherTable].Notes
FROM [PatientProfile] AS [TargetTable]
INNER JOIN [MIKPatientVisit] AS [OtherTable]
ON [OtherTable].PatientProfileId = [TargetTable].[PatientProfileId]

INSERT INTO

INSERT INTO SampleTable 
(ID, Col1)
VALUES 
(1, 'One'), 
(2, 'Two'), 
(3, 'Three');

• The following script will insert a set of results from a different query.

INSERT INTO SampleTable 
(ID, Col1)
SELECT MyId, MyColumn)
FROM [MyTable]

SELECT INTO

• With SELECT INTO, none of the indexes, constraints, calculated columns, or triggers defined in the source table are transferred to the new table.

SELECT * 
INTO MyBicycles
FROM Production.Product
WHERE ProductNumber LIKE 'BK%';

• The SELECT INTO creates the destination table, so it exclusively owns that table and is quicker compared to the INSERT INTO SELECT.
• Since the INSERT INTO SELECT inserts data into an existing table, it is slower and requires more resources due to the higher number of logical reads and greater transaction log usage.

SCOPE_IDENTITY(), IDENT_CURRENT, @@IDENTITY

• They return the last identity value inserted into an identity column.
• Two statements are in the same scope if they are in the same stored procedure, function, or batch.
• SCOPE_IDENTITY(), IDENT_CURRENT, and @@IDENTITY are similar functions.
• SCOPE_IDENTITY() and @@IDENTITY return the last identity values in any table in the current session.
  • SCOPE_IDENTITY() returns values inserted only within the current scope.
  • @@IDENTITY is not limited to a specific scope.
• IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.
• For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1.
  • When a row is inserted to T1, the trigger fires and inserts a row in T2.
  • This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
  • Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY() will return different values at the end of an INSERT statement on T1.
  • @@IDENTITY will return the last identity column value inserted across any scope in the current session.
  • This is the value inserted in T2.
  • SCOPE_IDENTITY() will return the identity value inserted in T1.
• The SCOPE_IDENTITY() will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

SELECT 
	SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
	@@IDENTITY AS [@@IDENTITY]
	IDENT_CURRENT('Production.Product') AS [IDENT_CURRENT];