Help with update statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with update statement

Author

Topic

dynamorevy
Starting Member

6 Posts

Posted - 2008-03-20 : 07:48:48

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 07:57:46

You can either import Access table or create Access database as a linked server and then refer it directly in your UPDATE query.

Update s
set svrMemory = acs.memory
From server s JOIN AccessDB..Server acs on acs.[name] like svrname + '%'
Where <somecondition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dynamorevy
Starting Member

6 Posts

Posted - 2008-03-20 : 08:26:46

Hi - I have imported the memory table from access into the SQL2005 database. Therefore I now have:

dbo.server with field names of Name and memory.

dbo.memory with field names of Name and memory. (should they be changed?).

Please could you help with the SQL statement now both tables are in the same database.
Many thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-20 : 08:29:06

[code]Update s
set Memory = m.memory
From server s JOIN memory m on m.[name] like name + '%'
Where <somecondition> -- this is optional[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dynamorevy
Starting Member

6 Posts

Posted - 2008-03-25 : 06:07:52

Thanks Harsh Athalye for your help. I cleansed my data in the memory table and ran:

Update s
set Memory = m.memory
From server s JOIN memory m on m.[name] = s.name

Many thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:34:44

You can even directly perform update using OPENROWSET

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources