1

I have user table in my DB. I have 1 excel sheet having list of some users (not all) with changes. How can I Update the values given in excel sheet to my user table? is it possible with import data option?

2

I only learned this in the past year. You can query an excel sheet as if it's a table. As long as your keys are also in excel you can join the excel sheet to the user table.

You can have a look at this Microsoft Article that discusses options for importing from Excel to SQL Server.

http://support.microsoft.com/kb/321686

  • Problem is not about joining(appending), I have rows in DB table I have to update the values. the new values are in excel with same field names. – M.S.Nayak Mar 17 '12 at 5:48
  • 1
    The update statement would look the same, you can just treat the excel worksheet like a table using openrowset. Here's another link that may show you in more detail: beyondrelational.com/quiz/sqlserver/tsql/2011/questions/… – Erik Mar 19 '12 at 13:24
0

If you are interested, I have built an Excel to SQL Server tool where end users update their data in Excel sheets on the intranet and the application will take care of parsing the Excel spreadsheet and export the data to a SQL Server table.

I'am currently looking for feedback so I give away licenses (two end users) to people who give feedback on the product.

The tool is called SQL Spreads: www.sqlspreads.com

Regards, Johannes

0

The workaround is:

  1. Import the Excel sheet to a staging table
  2. Run this SQL query:

    update main_table 
    set main_table.field1 = staging.field1, -- ...
    from main_table
    inner join staging on main.primary_key = staging.primary_key;`
    
  3. Delete staging table.

0

Follow the steps:

Declare @FileFath Varchar(Max)='C:\aa.csv'
Declare @Col Varchar(max)
Declare @SQL Varchar(max)

CREATE TABLE #DTemp (TempColumn VARCHAR(Max))
   SET @Sql = 
        'BULK INSERT #DTemp  
        FROM ''' + @FilePath + '''  
       WITH (  
                 FIELDTERMINATOR =''\n'',
                 FIRSTROW = 1,  
                 ROWTERMINATOR = ''\n'',
                 LASTROW = 1 
              )         
       ' 
   Exec (@Sql)
SET @COL=SELECT TempColumn FROM #DTemp)
SET @Col='(' + REPLACE (@Col,',',' VARCHAR (999) ,') + ' VARCHAR (999),'

SET @Sql = 'Create TABLE ##TempTable' + @Col + ''
Exec (@Sql)

SET @sql='BULK INSERT  ##TempTable FROM '''+@FilePath+'''
WITH
(
      FIRSTROW = 2,
       FIELDTERMINATOR = '','', 
      ROWTERMINATOR =''\n'', 
      TABLOCK
)'
EXEC(@Sql)

Then Use above table to update the value to main table by joining.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.