Hi

I'm trying bulk import some KB articles by importing them via CSV into an asset type I've created, then copying those asset entries into new KB articles using a helpdesk rule.

I'm stuck on the SQL syntax. I've tried using a Select statement in the Select part of the rule to select all from my KB article asset type and then use the update part to insert the values. But I cant reference the values in the Insert statement that the Select found. i.e. the Insert statement doesnt recognise strID when the Select statement says SELECT ID as strID FROM Asset_Data_38

So I've tried to combine the both but get errors about unrecognised fields.

What am I doing wrong?

insert into ADVISORY(ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;

Error:
41:17> Starting: Mon, 24 Jan 2011 12:41:17 +0000
41:17> Executing Select Query...
41:17> selected 2 rows
41:17> Executing Update Query...
41:17> mysql error: [1054: Unknown column 'TITLE' in 'field list'] in EXECUTE("insert into ADVISORY(ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT ID,TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN FROM ASSET_DATA_38;")

41:17> Ending: Mon, 24 Jan 2011 12:41:17 +0000
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
If you do a select * from ASSET_DATA_38 you will see that there are no such columns as TITLE, NOTE, etc.

ASSET will look something like FIELD_X where X is an integer corresponding to the ID from ASSET_FIELD_DEFINITION. Your query for the asset data will look something like this:

select FIELD_X as TITLE, FIELD_Y as NOTE /*, etc */ from ASSET_DATA_38

Also you do not want to select the ID field. ID in the ADVISORY table is an autoincrement field so just let it increment. If you must specify the numbers then make sure they are unique.
Answered 01/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thanks GillySpy, knew I was being stupid somehow! For reference here is the simple helpdesk rule to create knowledgebase articles.

Select statement:
SELECT * FROM ASSET_DATA_38

Update statement:
insert into ADVISORY(TITLE,NOTE,CATEGORY,IMPORTANCE,PLATFORM,MODIFIED,CREATED,MARKDOWN)
SELECT FIELD_384,FIELD_385,FIELD_388,FIELD_386,FIELD_388,FIELD_389,FIELD_390,FIELD_391 FROM ASSET_DATA_38;
Answered 01/24/2011 by: stubox
Blue Belt

Please log in to comment
0
Glad you got it working! Wanted to note that this query will only work for you in this ORG of your KBOX. Queries on assets are not re-useable without some modifications because the field numbers will be different on other KBOXes (and ORGs).
Answered 01/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity