• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 33
  • Last Modified:

Need help with SQL INSERT statement

Need help with SQL INSERT statement. Need to add the value RECEIVING INSPECTION to every record to the TASK DESCRIPTION column.  Attached an example of the data structure. I am using the following statement
 INSERT INTO [PELCONV1].[dbo].[WO_TASK] (TASK_DESCRIPTION)
 VALUES ('RECEIVING INSPECTION')
 FROM [PELCONV1].[dbo].[WO_TASK]
 WHERE TASK_ID = WO_NUMBER

Open in new window


but it not working.
Capture.PNG
0
maximus1974
Asked:
maximus1974
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
INSERT means add new rows that don't already exist.
UPDATE means change the values of existing rows.

By any chance do you intend to UPDATE?

UPDATE [PELCONV1].[dbo].[WO_TASK] 
SET TASK_DESCRIPTION = 'RECEIVING INSPECTION')
WHERE TASK_ID = WO_NUMBER

Open in new window

0
 
maximus1974Author Commented:
Jim, I may have not explained myself correctly. I want to add RECEIVING INSPECTION in addition to the PARTS_MANAGEMENT. In other words, I wish every record to have PARTS MANAGEMENT and RECEIVING INSPECTION. update would replace the existing value. I attached a new example.
Capture.PNG
0
 
Mark WillsTopic AdvisorCommented:
Update will only update the rows you qualify as being WHERE TASK_ID = WO_NUMBER

From the looks of your image, you want to duplicate the rows with 'PARTS MANAGEMENT' but save them (in addition to) as new rows with task_description of  'RECEIVING INSPECTION'

You will need to include all the other columns as well, you cant just add in a new value for TASK_DESCRIPTION , and if duplicating 'PARTS MANAGEMENT' then should include that in your WHERE statement as well.

So, rather than INSERT ... VALUES
You will be doing  INSERT ... SELECT and replace the column with the new value.

You will need to provide a list of column names, but going from your screen capture image, it would be something like

INSERT [PELCONV1].[dbo].[WO_TASK] (WOT_AUTO_KEY,TASK_ID,WO_NUMBER,TASK_DESCRIPTION,SEQUENCE,STATUS_DESCRIPTION,DATECLOSED)
 SELECT WOT_AUTO_KEY,TASK_ID,WO_NUMBER,'RECEIVING INSPECTION',SEQUENCE,STATUS_DESCRIPTION,DATECLOSED
 FROM [PELCONV1].[dbo].[WO_TASK]
 WHERE TASK_ID = WO_NUMBER
AND TASK_DESCRIPTION = 'PARTS MANAGEMENT'

Open in new window


BUT just run the select first and make sure you want to add those extra rows...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now