We help IT Professionals succeed at work.

Edit Top 200 Rows On a Temporary Table

All,

I've been tasked with updating a little over 200 rows in our DB.  Problem is, I only have INSERT, DELETE, AND UPDATE permissions.  I've created a temporary table #Cell_Phones, but I just realized you can't use the Edit Top 200 Rows with a temporary table.  Is there another way to accomplish this?  I've already made all 200 edits in Excel and just want to paste the new results into this table.  I have a cursor already set to update the descriptions as long as the id=@id.  

Hope someone can help me so I don't have to do this manually - AGAIN!  :)
Comment
Watch Question

Make sure the primary key is in your Excel file.  Then, use the SQL Server import and export wizard to import the data into a temp table.  (Right click on the database, select tasks then import data.)  Then join the real table with the temp table and update the values.

Greg

Brad Sims, CCNACyberSecurity Analyst

Author

Commented:
The import wizard is trying to create a new table as well.  The options to update are greyed out so I can't choose them.  Any other suggestions?
You don't have to create a new table.  On the Select Source Tables and Views screen, after you check the box of the sheet that you want to import, the destination column is a drop down of the available tables.  You should be able to select the table that you already created.  I'm not sure if this will work with a temp table.  You can't go directly to your final destination table either.  You may have to create a permanent table to temporarily store the data and then drop it later.  

Greg

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Typically all users can create temp tables, and it sounds like you can too.

From SSMS, expand "System Databases", right-click on "tempdb", select "Tasks", "Import Data...", choose "Excel" as data source, point to the file, select the desired sheet of the spreadsheet, click "Next", make sure "tempdb" is specified as the Destination db, click "Next" until you see the screen that says "Select Source Table and Views", and change the destination table name to dbo.#whatever.

Hopefully SQL will them import the data into the temp table.  If that works, let us know and we can take you from there.
CyberSecurity Analyst
Commented:
Sorry guys.  Neither of these worked.  I keep getting the "Error: CREATE TABLE permission denied".
Brad Sims, CCNACyberSecurity Analyst

Author

Commented:
I had someone with higher permissions than me create a temporary table for me.