Perform SELECT INTO Statement and add Primary Key afterwards

How can I imrpove the performance of this T-SQL statement?

SELECT Code, Description 
INTO [DBApps_Pulse_WarehouseAreas].[dbo].[Tbl_Partmaster]
FROM SYSDataBridge.dbo.View_Pulse_Partmaster_Synchronized

ALTER TABLE [DBApps_Pulse_WarehouseAreas].[dbo].[Tbl_Partmaster] ADD PRIMARY KEY (Code)

Your current code effectively writes the table twice.

Once as a heap and then again as a clustered index (with an intermediate sort step) after you add the primary key.

I'd just create the table upfront with the desired primary key and insert into it.

USE DBApps_Pulse_WarehouseAreas;

CREATE TABLE [dbo].[Tbl_Partmaster]
  (
     Code        VARCHAR(10) NOT NULL CONSTRAINT PK_Tbl_Partmaster PRIMARY KEY,
     Description NVARCHAR(500) NOT NULL
  );

INSERT INTO [dbo].[Tbl_Partmaster]  WITH (TABLOCKX)
SELECT Code,
       Description
FROM   SYSDataBridge.dbo.View_Pulse_Partmaster_Synchronized;

For at least three versions of SQL Server an insert into an empty B tree has been able to be minimally logged.

In order to avoid page splits there might be a sort operator in the execution plan if the estimated number of rows is sufficiently high (or if the source has an index that could potentially present the selected columns in order of code this direct insert might avoid the need to sort all together).

Finally avoid Hungarian notation such as Tbl_. At best it adds nothing but clutter. At worst at some point you might want to refactor the database and transparently replace the table with a view without updating calling code. Then the Tbl_ prefix would be down right misleading.

StackExchange Code Review Q#87160, answer score: 3