SQL query result into a table

I find this pretty useful plenty of time.

First an INSERT,

<cfquery name="Into_tempWorkHistory" datasource="myDataSource">
INSERT INTO toTable(WONUM, WorkerType, Worker, WorkStartDate)
SELECT WONUM, WorkerType, Worker, WorkStartDate
FROM fromTable
WHERE WONUM = '#ARGUMENTS.WONUM#'
</cfquery>

Second, an UPDATE could be also done via query result, read more

UPDATE TABLE1
SET WorkEndDate = T.Date
FROM TABLE2 T2
WHERE T2.WONUM = '#ARGUMENTS.WONUM#' AND T2.Action = 'R' AND T2.WorkerType = '#WorkerType#'
AND T2.Worker IN (SELECT T1.Worker
FROM TABLE1 T1
WHERE T1.WorkerType = '#WorkerType#'
AND T1.WONUM = '#ARGUMENTS.WONUM#')

*NOTE: Make sure not to use ALIAS on the UPDATE. And above UPDATE query is a mere example only, above update will do the job however result query doesn’t behave as expexted .

Added Bonus: I also found some nifty new (SQL 2005 and above) features. Have a look.

Advertisements
SQL query result into a table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s