Can NOT perform insert - 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

Other SQL Server Topics (2005)

Can NOT perform insert

Author

Topic

cwilli35
Starting Member

17 Posts

Posted - 2009-05-05 : 13:13:02

I'm trying to perform an insert but I'm receiving the following message upon execution of SQL.

Error: The name "wnn" is not permitted in this context.Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

INSERT INTO workitem
(number, external_reference, priority_lookup_id, status_lookup_id, category_lookup_id, type_lookup_id, category_tree_value, assigned_to_worker_id, created_by_worker_id, modified_by_worker_id, created_by_worker_contact_id, modified_by_worker_contact_id, created_on, minutes_spent, action, title, managed_object_id, contact_id, tag_collection_id, is_scheduled, bulletin_status, comment, auxdata, total_minutes_spent, owned_by_worker_id, rating, comment_visible_to_guest, rules_fired, updatelock, urgency_lookup_id, impact_lookup_id, close_code_lookup_id, modified_by_rule_guid, modified_by_rule_history_id, modified_by_workitem_number, modified_by_workitem_version, organization_lookup_id)

VALUES (wnn, T10.C1,

case T10.C538000103
When 'HIGH' then 200
When 'MEDIUM' then 300
When 'LOW' then 400
else 200
end

case T10.C538000109
When 'Requested' then 200
When 'Resolved' then 400
When 'Initiated' then 525
When 'In Progess' then 538
When 'Pending CAB Approval' then 550
When 'Release Management' then 575
When 'Pending EDS' then 588
When 'Pending MCNOSC' then 594
When 'Closed' then 600
else 200 end,

1, 10, 'Application Maintenance\Software Maintenance',5,
5, 5, 5, 5, dateadd(second, T10.C3,'1970-01-01'), 0 , 'In Progress', 'REMEDY TO ALTIRIS DATA CONVERSION', 5, 5, 5, 0,'i', T10.C538000144, T10.C4, T10.C538000149,0,
5, 0, 1, '620,638,644,352,319', 'd', 200, 200, 100, '00000000-0000-0000-0000-000000000000', 0, 0, 0,

case T10.C538000114
When 'Not Specified' then 50
When 'Headquarters' then 100
When 'Logistics Operation Center' then 200
When 'Logistics Capabilities Center' then 300
When 'Supply Management Center' then 400
When 'Maintenance Management Center' then 500
When 'Program Support Center' then 600
When 'Distribution Management Center' then 700
When 'Manpower' then 800
When 'Workforce Development Office' then 900
When 'Command Inspection EEO Office' then 1000
When 'Office of Counsel' then 1100
When 'Strategic Communications and Change Management' then 1200
When 'Programs and Resources Office' then 1300
When 'Contracts Department' then 1400
When 'Command Control Computers and Communications' then 1500
When 'Installations Environment & Safety Office' then 1600
When 'Logistics Modernization' then 1700
When 'Small Business Office' then 1800
When 'Band' then 1900
else 50
end)

FROM ARRemedy_TEST.dbo.T10 T10, ARRemedy_TEST.dbo.T33 T33,
AltirisHDS_TEST.dbo.worker,
AltirisHDS_TEST.dbo.workitem wnn

WHERE T10.C538000109 != 'Closed')

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 13:33:55

[code]INSERT INTO workitem
(number, external_reference, priority_lookup_id.....

SELECT
wnn, T10.C1,
case T10.C538000103
When 'HIGH' then 200
When 'MEDIUM' then 300
When 'LOW' then 400
else 200
end ...[/code]

cwilli35
Starting Member

17 Posts

Posted - 2009-05-05 : 14:02:00

In my workitem table I have a column named 'action' but SQL recognizes the ACTION as a kew word. How do I get around this? Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 14:30:41

[action]

cwilli35
Starting Member

17 Posts

Posted - 2009-05-05 : 14:40:20

THANKS SAKETS

cwilli35
Starting Member

17 Posts

Posted - 2009-05-05 : 15:09:00

Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'Progress'.

INSERT INTO AltirisHDS_TEST.dbo.workitem
(number, external_reference, priority_lookup_id, status_lookup_id
, category_lookup_id, type_lookup_id, category_tree_value
, assigned_to_worker_id, created_by_worker_id, modified_by_worker_id
, created_by_worker_contact_id, modified_by_worker_contact_id
, created_on, minutes_spent, [action], title, managed_object_id, contact_id, tag_collection_id
, is_scheduled, bulletin_status, comment, auxdata, total_minutes_spent
, owned_by_worker_id, rating, comment_visible_to_guest
, rules_fired, updatelock, urgency_lookup_id, impact_lookup_id
, close_code_lookup_id, modified_by_rule_guid, modified_by_rule_history_id
, modified_by_workitem_number, modified_by_workitem_version
, organization_lookup_id)

SELECT wnn, T10.C1,
case T10.C538000103
When HIGH then 200
When MEDIUM then 300
When LOW then 400
else 200
end,
case T10.C538000109
When Requested then 200
When Resolved then 400
When Initiated then 525
When [In] Progress then 538
When Pending CAB Approval then 550
When Release Management then 575
When Pending EDS then 588
When Pending MCNOSC then 594
When Closed then 600
else 200
end,
1, 10, 'Application Maintenance\Software Maintenance', 5,
5, 5, 5, 5, dateadd(second, T10.C3,'1970-01-01'), 0, 'In Progress', 'REMEDY TO ALTIRIS DATA CONVERSION', 5, 5, 5, 0,'i', T10.C538000144, T10.C4, 0, 5, 0, 1, '620,638,644,352,319', 'd', 200, 200, 100, '00000000-0000-0000-0000-000000000000', 0, 0, 0,0,

case T10.C538000114
When [Not] Specified then 50
When Headquarters then 100
When Logistics Operation Center then 200
When Logistics Capabilities Center then 300
When Supply Management Center then 400
When Maintenance Management Center then 500
When Program Support Center then 600
When Distribution Management Center then 700
When Manpower then 800
When Workforce Development Office then 900
When Command Inspection EEO Office then 1000
When Office [of] Counsel then 1100
When Strategic Communications and Change Management then 1200
When Programs and Resources Office then 1300
When Contracts Department then 1400
When Command Control Computers and Communications then 1500
When Installations Environment & Safety Office then 1600
When Logistics Modernization then 1700
When Small Business Office then 1800
When Band then 1900
else 50
end

FROM ARRemedy_TEST.dbo.T10 T10
WHERE T10.C538000109 != 'Closed'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 15:32:52

[code]SELECT
wnn,
T10.C1,
case T10.C538000103
When HIGH then 200
When MEDIUM then 300
When LOW then 400
else 200
end,
case T10.C538000109
When 'Requested' then 200
When 'Resolved' then 400
When 'Initiated' then 525
When '[In] Progress' then 538
When 'Pending CAB Approval' then 550
When 'Release Management' then 575
When 'Pending EDS' then 588
When 'Pending MCNOSC' then 594
When 'Closed' then 600
else 200
end,
1, 10, 'Application Maintenance\Software Maintenance', 5,
5, 5, 5, 5, dateadd(second, T10.C3,'1970-01-01'), 0, 'In Progress', 'REMEDY TO ALTIRIS DATA CONVERSION', 5, 5, 5, 0,'i', T10.C538000144, T10.C4, 0, 5, 0, 1, '620,638,644,352,319', 'd', 200, 200, 100, '00000000-0000-0000-0000-000000000000', 0, 0, 0,0,

case T10.C538000114
When '[Not] Specified' then 50
When 'Headquarters' then 100
When 'Logistics Operation Center' then 200
When 'Logistics Capabilities Center' then 300
When 'Supply Management Center' then 400
When 'Maintenance Management Center' then 500
When 'Program Support Center' then 600
When 'Distribution Management Center' then 700
When 'Manpower' then 800
When 'Workforce Development Office' then 900
When 'Command Inspection EEO Office' then 1000
When 'Office [of] Counsel' then 1100
When 'Strategic Communications and Change Management' then 1200
When 'Programs and Resources Office' then 1300
When 'Contracts Department' then 1400
When 'Command Control Computers and Communications' then 1500
When 'Installations Environment & Safety Office' then 1600
When 'Logistics Modernization' then 1700
When 'Small Business Office' then 1800
When 'Band' then 1900
else 50
end

FROM ARRemedy_TEST.dbo.T10 T10
WHERE T10.C538000109 != 'Closed'[/code]

cwilli35
Starting Member

17 Posts

Posted - 2009-05-05 : 15:40:03

Upon execution of the below listed script, I'm getting the following error: "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

- I have counted the number of SELECT values and INSERT columns, they both match. Can anyone help? Thanks!

INSERT INTO AltirisHDS_TEST.dbo.workitem
(number, external_reference, priority_lookup_id, status_lookup_id
, category_lookup_id, type_lookup_id, category_tree_value
, assigned_to_worker_id, created_by_worker_id, modified_by_worker_id
, created_by_worker_contact_id, modified_by_worker_contact_id
, created_on, minutes_spent, [action], title, managed_object_id, contact_id, tag_collection_id
, is_scheduled, bulletin_status, comment, auxdata, total_minutes_spent
, owned_by_worker_id, rating, comment_visible_to_guest
, rules_fired, updatelock, urgency_lookup_id, impact_lookup_id
, close_code_lookup_id, modified_by_rule_guid, modified_by_rule_history_id
, modified_by_workitem_number, modified_by_workitem_version
, organization_lookup_id)

SELECT wnn, T10.C1,
case T10.C538000103
When HIGH then 200
When MEDIUM then 300
When LOW then 400
else 200
end,
case T10.C538000109
When Requested then 200
When Resolved then 400
When Initiated then 525
When In Progess then 538
When Pending CAB Approval then 550
When Release Management then 575
When Pending EDS then 588
When Pending MCNOSC then 594
When Closed then 600
else 200
end,
1, 10, 'Application Maintenance\Software Maintenance', 5,
5, 5, 5, 5, dateadd(second, T10.C3,'1970-01-01'), 0 , 'In Progress', 'REMEDY TO ALTIRIS DATA CONVERSION', 5, 5, 5, 0,'i', T10.C538000144, T10.C4,

0, 5, 0, 1, '620,638,644,352,319', 'd', 200, 200, 100, '00000000-0000-0000-0000-000000000000', 0, 0, 0,0,

case T10.C538000114
When Not Specified then 50
When Headquarters then 100
When Logistics Operation Center then 200
When Logistics Capabilities Center then 300
When Supply Management Center then 400
When Maintenance Management Center then 500
When Program Support Center then 600
When Distribution Management Center then 700
When Manpower then 800
When Workforce Development Office then 900
When Command Inspection EEO Office then 1000
When Office of Counsel then 1100
When Strategic Communications and Change Management then 1200
When Programs and Resources Office then 1300
When Contracts Department then 1400
When Command Control Computers and Communications then 1500
When Installations Environment & Safety Office then 1600
When Logistics Modernization then 1700
When Small Business Office then 1800
When Band then 1900
else 50
end

FROM ARRemedy_TEST.dbo.T10 T10
WHERE T10.C538000109 != 'Closed'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 15:45:56

they don't match.. check again.

cwilli35
Starting Member

17 Posts

Posted - 2009-05-06 : 08:10:52

you're correct! they didn't match

cwilli35
Starting Member

17 Posts

Posted - 2009-05-06 : 11:20:46

From the above SQL I need to INSERT into another column named 'owned_by_worker_id'. Can anyone tell me how I can include the below listed SQL in the values section of my original select statement? Sakets are you out there? :-) Thanks!

select distinct ARRemedy_TEST.dbo.T10.C1, ARRemedy_TEST.dbo.T33.C538000011, ARRemedy_TEST.dbo.T10.C4 AS Assigned_to, ARRemedy_TEST.dbo.T10.C538000109
FROM ARRemedy_TEST.dbo.T10, ARRemedy_TEST.dbo.T33
WHERE ARRemedy_TEST.dbo.T10.C538000030 =
ARRemedy_TEST.dbo.T33.C538000030 AND ARRemedy_TEST.dbo.T10.C1 = ARRemedy_TEST.dbo.T33.C538000245
and ARRemedy_TEST.dbo.T10.c538000109 != 'Closed' order by ARRemedy_TEST.dbo.T10.C1

cwilli35
Starting Member

17 Posts

Posted - 2009-05-06 : 12:46:12

cwilli35
Starting Member

17 Posts

Posted - 2009-05-07 : 08:16:57

SOMEONE PLEASE HELP. I'm Stumped at the moment.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 09:14:37

Which column from the new sql do you want to insert to 'owned_by_worker_id' column in workitem?

cwilli35
Starting Member

17 Posts

Posted - 2009-05-07 : 10:43:32

The C4 column from the T10 table

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 12:09:34

do you want to insert those rows which do not have a match in T33 table ?? change it to an inner join if thats the case. Your over all query should look like this,,,

INSERT INTO AltirisHDS_TEST.dbo.workitem
(....,owned_by_worker_id)

SELECT 
	...,
	ARRemedy_TEST.dbo.T10.C4 
FROM 
	ARRemedy_TEST.dbo.T10 T10 left join ARRemedy_TEST.dbo.T10.C4 
	on ARRemedy_TEST.dbo.T10.C538000030 = ARRemedy_TEST.dbo.T33.C538000030 AND ARRemedy_TEST.dbo.T10.C1 = ARRemedy_TEST.dbo.T33.C538000245
WHERE 
	T10.C538000109 != 'Closed'

replace occurences of ... with what you had previously.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources