Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add records to a SQL table based on values in another table

Posted on 2009-07-08
8
Medium Priority
?
274 Views
Last Modified: 2012-05-07
I would like to add records to the "DrgGrp" table based on records in the "Drg" table (please see attached files for screenshots of sample data and field names from both tables).

I want to add a record to the DrgGrp table for every time a a record exists in the Drg table with the text "CAP" anywhere in the FORM field and the records being added will all have the same value for DrgGrp.GroupID (lets say for the example of the query to make it 199).  Care must be taken to update the DrgGrp.Seq field properly.  If several records exist with the same DrgGrp.DrgID the DrgGrp.Seq must be incremented to the next higher number.  For example if there are already two records with the same DrgGrp.DrgID then the 3rd record that gets added would need to have a DrgGrp.Seq value of 3.
drg-Table.png
drggrp-Table.png
0
Comment
Question by:tsmit877
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 24805735
A query like this one (implemented here as a stored procedure for clarity) should do what you are looking for, as I understand it.  Here are the assumptions I will be making:

     "I want to add a record to the DrgGrp table for every time a a record exists in the Drg table with the text "CAP" anywhere in the FORM field "

We will be looking for the distinct word CAP, not the letters CAP embedded in another word.  Thus, the WHERE clause will be a bit complex, eg

WHERE form LIKE 'CAP %'
    OR form LIKE '% CAP'
    OR form LIKE '% CAP %'

If other separators than a space may occur, a variation would be needed.  For example, if the word could end with a period, space, or comma, then the WHERE would look like:

WHERE form LIKE 'CAP[ ,.]%'
    OR form LIKE '% CAP'
    OR form LIKE '% CAP[ ,.]%'


     "and the records being added will all have the same value for DrgGrp.GroupID (lets say for the example of the query to make it 199)."

Passed as a parameter into the stored procedure


     "If several records exist with the same DrgGrp.DrgID the DrgGrp.Seq must be incremented to the next higher number"

Assuming that the condition should be "If several records exist with the same DrgGrp.DrgID and DrgGrp.GroupID....."


Given these assumptions:

CREATE PROCEDURE drg_AddDrgGrpRecords
    @GroupID int
AS
BEGIN
CREATE PROCEDURE drg_AddDrgGrpRecords
    @GroupID int
AS
BEGIN
    CREATE TABLE #tmp (
        id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        DrgID INT NOT NULL
    )
 
    INSERT INTO #tmp (DrgID)
    SELECT ID 
    FROM Drg
    WHERE form LIKE 'CAP[ ,.]%'
        OR form LIKE '% CAP'
        OR form LIKE '% CAP[ ,.]%'
 
    INSERT INTO DrgGrp (
        DrgID,
        GroupID,
        Created,        ---- Not updated in sample, can be commented out if not needed
        Seq,
        CentralMaintFieldMask
        )
    SELECT 
        DrgID,
        @GroupID,
        CURRENT_TIMESTAMP,    -- Not updated in sample, can be commented out if not needed
        (SELECT COUNT(*) FROM #tmp x WHERE x.DrgID = t.DrgID AND x.id <= t.id) Seq,
        0
    FROM #tmp t
END

Open in new window

0
 
LVL 2

Author Comment

by:tsmit877
ID: 24805830
Where do I specify DrgGrp.GroupID in the stored procedure that will be used for all the records that get added?
0
 
LVL 2

Author Comment

by:tsmit877
ID: 24805885
Sorry, I just ran the stored procedure and see that it prompts for the value.  I will try it now and let you know the results shortly.
0
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 
LVL 2

Author Comment

by:tsmit877
ID: 24807576
Looks like it works well but the portion that is supposed to account for existing Seq #'s is not working.  My SQL is very basic but looking at the query it look like it might be because it is looking for existing Seq #'s in the TMP table instead of the DrgGrp table?

Right now when it is adding the records it simply uses Seq "1" but I think that's because there are never more than 1 seq #'s in the TMP table.
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 24807809
Ah - I see what you need then.  I missed the fact that you would be inserting multiple times into the same DrgGrp.  

The code snippet below handles all cases I can think of.



CREATE PROCEDURE drg_AddDrgGrpRecords
    @GroupID int
AS
BEGIN
    CREATE TABLE #tmp (
        id INT IDENTITY(1,1),
		Src CHAR(1) NOT NULL,
        DrgID INT NOT NULL,
		CONSTRAINT tmp_pk_drg_12935498 PRIMARY KEY CLUSTERED (Src, ID)
    )
 
	INSERT INTO #tmp (Src, DrgID)
		SELECT 
			'A',
			DrgID
		FROM DrgGrp
		WHERE DrgGrp.GroupID = @GroupID
 
    INSERT INTO #tmp (Src, DrgID)
    SELECT 
		'Z', 
		ID 
    FROM Drg
    WHERE form LIKE 'CAP[ ,.]%'
        OR form LIKE '% CAP'
        OR form LIKE '% CAP[ ,.]%'
 
    INSERT INTO DrgGrp (
        DrgID,
        GroupID,
        Created,        ---- Not updated in sample, can be commented out if not needed
        Seq,
        CentralMaintFieldMask
        )
    SELECT 
        DrgID,
        @GroupID,
        CURRENT_TIMESTAMP,    -- Not updated in sample, can be commented out if not needed
        (SELECT COUNT(*) FROM #tmp x WHERE x.DrgID = t.DrgID AND x.id <= t.id) Seq,
        0
    FROM #tmp t
	WHERE Src = 'Z'
END

Open in new window

0
 
LVL 2

Author Closing Comment

by:tsmit877
ID: 31601181
Worked Great.  Thanks bhess1
0
 
LVL 2

Author Comment

by:tsmit877
ID: 24827587
When I comment out where you have suggested I get the following error:

Msg 121, Level 15, State 1, Procedure drg_AddDrgGrpRecords, Line 26
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Can you please help with this?
0
 
LVL 2

Author Comment

by:tsmit877
ID: 24827667
Ok, fixed the last error but now getting this when I run the stored procedure:

(565 row(s) affected)
Msg 2601, Level 14, State 1, Procedure drg_AddDrgGrpRecords, Line 15
Cannot insert duplicate key row in object 'dbo.DrgGrp' with unique index 'IXC_DrgGrp_DrgID_GroupID'.
The statement has been terminated.

(1 row(s) affected)
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 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