SQL INSERT

cotton31 · 11 Feb 2011 at 10:05

I've got an SQL (edit: SQL Server 2005) table populated by on of our applications, but one of the fields (task status) isn't populating during this process.

I can extract this field manually and I want to insert this in to the table.

The value of the field obviously depends on the task, so I can't just do an insert where as there are thousands of rows I need to update.

How could I do this without having to run a query for each task?

GravyMonster · 11 Feb 2011 at 10:07

Sounds like you already have a record in place if one field isn't populating - in which case you'll need to use an update, not insert. Something like this should work...

Code:

UPDATE TableName SET TaskStatus = 'Value' WHERE Task = 'TaskValue'

Just repeat that changing the values as needed for each task.

cotton31 · 11 Feb 2011 at 10:14

Cheers Spunkey,

Yes I did mean UPDATE - I was trying to avoid having to repeat that as I've got thousands of tasks I need to update.

Any ideas?

GravyMonster · 11 Feb 2011 at 10:29

Ahh I understand now.

Does the value you need to put in the TaskStatus column come from the Tasks table? If so you could write a single query with a join to update the values.

cotton31 · 11 Feb 2011 at 11:07

How would I do this? I was thinking of inserting the missing data into a temp table, containing the taskID and taskStatus.

Then I could do some sort of UPDATE/JOIN query to insert the correct values????

GravyMonster · 11 Feb 2011 at 11:17

Yeah that would work. If you had an intermediary table like this:

Code:

Task_TaskStatus
------------------------------
TaskID      int
TaskStatus      nvarchar(128)

And populated that with your data you could then run this query to update it all in 1 go:

Code:

UPDATE TableName t 
INNER JOIN Task_TaskStatus ts ON t.TaskID = ts.TaskID 
SET t.TaskStatus = ts.TaskStatus

cotton31 · 11 Feb 2011 at 11:30

Cheers mate, I'll give that a go.

That's what I wanted to do but didn't realise you could do joins within updates.

sist_si · 11 Feb 2011 at 11:32

if the task value is missing from the table (presumably the corresponding 'task id'), how do you know which task applies to which record in the table with missing data?

cotton31 · 11 Feb 2011 at 11:37

No it's only the status which is missing (the field is there, just not populated).

I can extract this data manually from the app into a CSV, so I could have task ID and task status in this. Then I can join the id in the CSV to the id in the original table.

sist_si · 11 Feb 2011 at 12:50

ah ok, so does the task id 'belong' to the taskstatus? if so, and the taskid and status are in their own table anyway, you should probably be just storing the taskid (foreign key) in your table.
i may have misunderstood though so ignore me if so