Simple update query balloons tempdb I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is running on).

I load the csv file using a bulk copy in to a temporary table that has matching schema to the destination table, I then do the query 

Update [Med_Rec_Entries] 
set [Comments] = [Tmp_Med_Rec_Entries].[Comments] 
from [Tmp_Med_Rec_Entries] 
where [Tmp_Med_Rec_Entries].[ID] = [Med_Rec_Entries].[ID]


And that is where temp db blows up in size. Both the source and destination tables have 1,770,373 rows in it (there is a 1=1 on ID relationship for the update).

I tried running the database tuning advisor and it had me make a new index, but it did not help. The database I am inserting in to is just the middle step in a data migration and no queries other than other csv imports like one I am doing here and a bulk export at the end will be run against this database so it does not have any indexes or keys itself.

Here is the create table statement for Med_Rec_Entries

CREATE TABLE [dbo].[Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Patient_ID] [nvarchar](80) NULL,
    [Med_Rec_Code] [nvarchar](20) NULL,
    [Tx_Date_Performed] [nvarchar](254) NULL,
    [Comments] [nvarchar](max) NULL,
    [Staff_Code] [nvarchar](21) NULL,
    [Quantity] [real] NULL,
    [Hide_This_Entry] [bit] NULL,
    [Exclude_From_Printed_History] [bit] NULL,
    [Image_ID] [int] NULL,
    [Remote_Status] [nvarchar](2) NULL,
    [Special_Flag] [nvarchar](20) NULL,
    [Tx_Time_Performed] [nvarchar](254) NULL,
    [Tx_Date_Recorded] [nvarchar](254) NULL,
    [Appended_Comments] [nvarchar](max) NULL,
    [Tx_Time_Recorded] [nvarchar](254) NULL,
    [Entry_Is_Locked] [bit] NULL,
    [Create_User] [nvarchar](21) NULL,
    [Audit_DTS] [nvarchar](20) NULL,
    [Audit_Actor] [nvarchar](21) NULL,
    [vQPixAreax_] [varbinary](max) NULL,
    [Route] [nvarchar](80) NULL,
    [Units] [nvarchar](20) NULL,
    [CFR_Reason_Code] [nvarchar](40) NULL,
    [rpl_Key] [nvarchar](14) NULL,
    [rpl_DateTime] [nvarchar](14) NULL,
    [Medical_Description] [nvarchar](max) NULL,
    [caseID] [int] NULL,
    [Sign_Off_Target] [nvarchar](21) NULL,
    [Sign_Off_Date] [nvarchar](254) NULL,
    [Location_Code] [nvarchar](50) NULL,
    [ACUP_ID] [nvarchar](15) NULL,
    [ExcludeFromWeb] [bit] NULL,
    [SecondarySort] [int] NULL,
    [AutosaveState] [nvarchar](20) NULL,
    [_CommentsAndFindingsText] [nvarchar](max) NULL,
    [NewFormat] [bit] NULL,
    [Temp] [nvarchar](80) NULL,
    [Colour] [nvarchar](20) NULL,
    [PrimaryKey] [nvarchar](200) NULL,
    [Sign_Off_Time] [nvarchar](254) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--From the database engine tuning advisor
CREATE NONCLUSTERED INDEX [_dta_index_Med_Rec_Entries_13_933578364__K1] ON [dbo].[Med_Rec_Entries]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]


Here is the create table statement for Tmp_Med_Rec_Entries

CREATE TABLE [dbo].[Tmp_Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Comments] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


What can I do to stop tempdb from growing too large?
You could run the import in small batches.

TempDB is growing large enough to accommodate running various queries necessary for the import routine.  Running smaller imports would lessen the amount of tempdb required during each run.