By the way, this is almost a solution in Common Lisp using the format string mini-language:
(destructuring-bind (tbl key . fields)
'(Study (StudyId (int) studyseq) ;; this will always be the key field.
(StudyCode (string 25)) ;; and always encapsulate the type info in their own list
(StartDate (datetime 8))
(Description (string 25)))
(format t "insert into ~a (~{ ~a~^,~}) values (~{ :~a~^,~}) returning ~a into ~a"
tbl
(append (mapcar 'car fields)(list (car key)))
(append (mapcar 'car fields)(list (car (last key))))
(car key) (car key)))
It does not do the nextVal bit, and I would have to put bars around things like |StudyCode| to preserve case. But the neat thing is the ~{~} list expander (like @ in backquoted lists) and the escape thing ~^ that arranges for the comma not to print after the last item.
