CODEWITHSUNDEEP
sqlsql-serverexcelt-sql
pgunston
pgunston

Reputation: 302

With SQL, how can I update a list of records in a table?

I have a table in SQL Server containing assets.

Some of these assets are actually 'deactivated' however, in my table, they are all marked as 'active'.

I have a list in Excel of all the deactivated assets. This list just consists of the Asset#.

I want to update my SQL Server table to make all of these assets (from my Excel list) 'deactivated'.

How can I achieve this?

Example:

UPDATE ASSETTABLE
SET Status = deactivated
where Asset# = (LIST OF ASSET# fields from EXECL)

Upvotes: 0

Views: 219

Answers (3)

RdPC
RdPC

Reputation: 689

Seems your problem is getting the information from your excel to the SQL server, to do that you could use

SQL Server linked servers and distributed queries You can find information here, http://support.microsoft.com/kb/306397 The example here might give you an idea of what you need, depending on the version of your SQL server you might need to use another data provider

In my case I use Microsoft.ACE.OLEDB.12.0 you could do something like:

CREATE TABLE #Temp (Asset int) -- Instead of a Temp table you can use a table variable
DECLARE @SQL nvarchar(MAX)
SET @SQL = ' INSERT INTO #TEMP '
SET @SQL = @SQL +' SELECT Asset '
SET @SQL = @SQL +' FROM '
SET @SQL = @SQL +' OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', '
SET @SQL = @SQL +' ''Excel 12.0 Xml;Database='+ @Path_to_your_Excel_file ';'', '
SET @SQL = @SQL +' ''SELECT * from ' + @Sheet + '''  )' 
-- The path can be to a shared folder or where ever you like
EXEC(@SQL)

UPDATE ASSETTABLE
Set Status = 'deactivated'
WHERE AssetNumber in (SELECT * FROM #Temp)

You can also try a BULK INSERT

DECLARE @bulkinsert NVARCHAR(max)
SET @bulkinsert =               ' BULK INSERT #TEMP '
SET @bulkinsert = @bulkinsert + '   FROM ' + '''' +  @Path_to_your_Excel_file + ''''
SET @bulkinsert = @bulkinsert + '   WITH '
SET @bulkinsert = @bulkinsert + '   ('
SET @bulkinsert = @bulkinsert + '       FIELDTERMINATOR = ''\t'','
SET @bulkinsert = @bulkinsert + '       ROWTERMINATOR = ''\n''
SET @bulkinsert = @bulkinsert + '   )'
EXEC sp_executesql @bulkinsert

But I have only used it with csvs and can't tell you if it will work

Upvotes: 0

Damian Ramirez
Damian Ramirez

Reputation: 11

If it's a short list then you can just type it in.

WHERE asset# IN (1,2,3,4)

Upvotes: 1

simo.3792
simo.3792

Reputation: 2236

You don't say what type of database you are using, but if it's SQL Server, then the SQL Server Management Studio provides a Data Import and Export Wizard.

In this case I am assuming that you are going to do this as a once off, or at least only a limited number of times per year. If this was a daily or weekly event, then creating a more formal process is better using some kind of customised application / web page etc.

Use the Data Import and Export Wizard to import your Excel data into a new temporary table, rather than directly into you main table. Even if the temporary table is just a list of the deactivated Asset Numbers - ie you can filter the raw data in excel before importing if that is simpler.

Then use your SQL almost as you wrote it:

UPDATE ASSETTABLE
Set Status = 'deactivated'
WHERE AssetNumber in (SELECT ExcelAssetNumber FROM ImportedExcelAssets)

After that you can drop the table, either directly in SQL Server Management Studio, or through a DROP TABLE statement using our favourite SQL browser.

Example of procedure here, or search for SQL Server Data Import and Export Wizard. But as I say not sure if it's relevant to your platform.

Upvotes: 1

Related Questions