Help with update statement

[dynamorevy]

Hi new to SQL but need some help. Database is SQL2005. I have a 'server' table:

svrID
svrName
Plus many other columns.
I have added svrMemory as a new column.

svrname will be 'server1', 'server2' 'server3' and so on.

I need to update the new svrMemory field with data I have collected from DELL ITA. I have put this data into an access table. The name field however in this table is different 'server1@domain.co.uk', server2@domain.co.uk' etc. The memory field is a number.

So I want to add the memory data from my accesss table and input this into my memory feild in the SQL2005 'Server' table.

Should I first import the access table into my SQL2005 database for the initial update? Could you please help with the SQL statement to add the memory data to my server table?

Many thanks

 

[chigley]

Import the access table into SQL first using SSIS, into a new table called import_server for arguments sake.

Then you need to do an update statement with a join in the WHERE clause using PATINDEX to strip out the server name before the @ sign

UPDATE SERVER SET SVRMEMORY = import_server.memory
FROM SERVER JOIN import_server
WHERE SERVER.svrName = LEFT(import_server.svrName,1,PATINDEX('@',import_server.svrname)-1)

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk