Solved

Insert into table - into columns based upon where clause in select from

Posted on 2009-07-07
1
296 Views
Last Modified: 2012-08-14
I have the below table - it has three seperate column "sets"/"groups" where I want to take my select code sample and where row = 1 insert into 1st column set, where row = 2 insert into 2nd. etc.


CREATE TABLE [dbo].[tblPROFEE_RVU_Mods](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [HCPCS] [varchar](20) NULL,
      [carrierLoc] [varchar](20) NULL,
      ------ First col set
      [1mod] [varchar](20) NULL,
      [1facRVU] [decimal](5, 2) NULL,
      [1nonFacRVU] [decimal](5, 2) NULL,
      [1mpRVU] [decimal](5, 2) NULL,
      [1fac] money NULL,
      [1nonFac] money NULL,
------ second col set
      [2mod] [varchar](20) NULL,
      [2facRVU] [decimal](5, 2) NULL,
      [2nonFacRVU] [decimal](5, 2) NULL,
      [2mpRVU] [decimal](5, 2) NULL,
      [2fac] money NULL,
      [2nonFac] money NULL,
------ third col set
      [3mod] [varchar](20) NULL,
      [3facRVU] [decimal](5, 2) NULL,
      [3nonFacRVU] [decimal](5, 2) NULL,
      [3mpRVU] [decimal](5, 2) NULL,
      [3fac] money NULL,
      [3nonFac] money NULL,
SELECT DISTINCT c.hcpcs, c.CarrierLoc,

                        CASE WHEN r.MOD IS NULL THEN '-'

                             ELSE r.[MOD]

                        END AS [MOD],

                        r.[FULLY IMPLEMENTED FACILITY PE RVU],

                        r.[FULLY IMPLEMENTED NON-FAC PE RVU],

                        r.[MP RVU],

                        c.Fac,

                        c.NonFac,

                        CASE WHEN c.mod IS NULL OR c.mod = '' THEN '1' WHEN c.mod = '26' then '2' ELSE '3' END AS row

              FROM      dbmasterdata.dbo.tblPROFEE AS c

                        LEFT JOIN dbmasterdata.dbo.tblPROFEE_RVU AS r ON c.HCPCS = r.HCPCS

                                                                      AND ISNULL(r.MOD, N'') = ISNULL(c.MOD, N'')

			  WHERE c.mod <> '53'

Open in new window

0
Comment
Question by:tbaseflug
1 Comment
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
ID: 24799524
Hi
I am not sure you are approaching this in the right way (not really enough info) but I would suggest your main table is formatted more like this:
CREATE TABLE [dbo].[tblPROFEE_RVU_Mods](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [HCPCS] [varchar](20) NULL,
      [carrierLoc] [varchar](20) NULL,
      [1mod] [varchar](20) NULL,
      [1facRVU] [decimal](5, 2) NULL,
      [1nonFacRVU] [decimal](5, 2) NULL,
      [1mpRVU] [decimal](5, 2) NULL,
      [1fac] money NULL,
      [1nonFac] money NULL,
      [colset] INT
There are less "duplicated" columns and there is a colset column instead to identify which "sets"/"groups" your data relates to, you might need to work out the relationships of your data and then look at your database design.
To do what you want if I am understanding you correctly would mean that only 1  "sets"/"groups" of would ever be populated meaning that you would have a great deal of empty space in your table which is undesirable.  If you still want to do this I suggest you do it in 3 seperate quereis one for each  "sets"/"groups".  I think the snippet below for example would return the data for "sets"/"groups"  2, you would then have a seperate one for 1 and for 3 and run them independantly.

SELECT DISTINCT

	c.hcpcs

	, c.CarrierLoc

	, CASE

		WHEN r.[MOD] IS NULL THEN '-'

		ELSE r.[MOD]

		END AS [MOD]

	, r.[FULLY IMPLEMENTED FACILITY PE RVU]

	, r.[FULLY IMPLEMENTED NON-FAC PE RVU]

	, r.[MP RVU]

	, c.Fac

	, c.NonFac

	FROM

		dbmasterdata.dbo.tblPROFEE AS c

		LEFT JOIN dbmasterdata.dbo.tblPROFEE_RVU AS r ON c.HCPCS = r.HCPCS  AND ISNULL(r.[MOD], N'') = ISNULL(c.[MOD], N'')

	WHERE

		c.[mod] = '26'

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now