Creating a pre-filled field during a Select_Into Statement

In preparation to performing a Union query between a table of procedures using CPT codes and a table of procedures using ICD-9 codes, I want my field names to match, but for a field called “PROC_CODE” to identify which code is which type once the two tables are combined.

I did a little googling and the answers I found seemed to be overly complicated for what I needed: Basically making a blank column, and then updating it. Alternatively, creating the table without that column and then adding it after the fact.

So I tried something simple and intuitive (and probably well-documented if not for my weak google-fu) – and it appears to work. Without further ado, this is my query that makes “CPT” the default string inside the “PROC_CODE_TYPE” field:

IF OBJECT_ID(‘tempdb..##EncListCPTMatches’) IS NOT NULL DROP TABLE ##EncListCPTMatches;

Select Distinct ##EncList.PAT_ID,
                             ##EncList.PAT_MRN_ID,
                             ##EncList.PAT_ENC_CSN_ID,
                             ##EncList.SURG_DT,
                              ‘CPT’ AS PROC_CODE_TYPE,
                              Proc_CPT.CPT_CODE AS PROC_CODE,
                              PROC.PROC_NAME AS PROC_NAME
INTO ##EncListCPTMatches
FROM ##EncList
INNER JOIN PAT_SURG
ON ##EncList.PAT_ID = PAT_SURG.PAT_ID
AND PAT_SURG.SURG_DATE = ##Enclist.SURG_DT
INNER JOIN PROC 
ON PAT_SURG.PROC_ID = PROC.PROC_ID
INNER JOIN Proc_CPT
ON PROC.PROC_ID = Proc_CPT.PROC_ID
AND Proc_CPT.CPT_CODE IN (/*Approved CPTs*/‘32220’, ‘32662’, ‘32674’, ‘33020’)
ORDER BY ##EncList.PAT_MRN_ID, ##EncList.SURG_DT

Advertisements

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