Solved

SQL Insert, multiple tables

Posted on 2009-04-13
15
190 Views
Last Modified: 2012-05-06
I have a select statement below which queries three tables and results in the following:

CERTIFICATION      ENROLLMENT
TRANSCRIPTAUTHFORM      ENROLLMENT
DISCLOSURE      ENROLLMENT
RIGHTTOKNOW      ENROLLMENT
PROGRAMGRADINFO      ENROLLMENT
CERTIFICATION      REENTRY

The first column refers to a document name, the second is the state in which the document can be used in the application. Basically, I need to write a SQL insert statement (sql server 2005) which inserts new records for the additional documents, besides CERTIFICATION, with a state of REENTERY, based on the criteria of the select statement and I am unsure how to use that select statement to insert the new records. The result I am looking for would be that when the SQL select query listed below is run, the result would be:

CERTIFICATION      ENROLLMENT
TRANSCRIPTAUTHFORM      ENROLLMENT
DISCLOSURE      ENROLLMENT
RIGHTTOKNOW      ENROLLMENT
PROGRAMGRADINFO      ENROLLMENT
CERTIFICATION      REENTRY
TRANSCRIPTAUTHFORM      REENTRY
DISCLOSURE      REENTRY
RIGHTTOKNOW      REENTRY
PROGRAMGRADINFO      REENTRY
select d.shortname,p.shortname from document d,
 
processdocuments pd,
 
process P
 
where d.shortname in ('RIGHTTOKNOW', 'DISCLOSURE','TRANSCRIPTAUTHFORM','PROGRAMGRADINFO','CERTIFICATION')
 
and d.id=documentid
 
and p.id=processid

Open in new window

0
Comment
Question by:crosstf
[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
  • 8
  • 6
15 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 24129692
Try this...
select d.shortname,p.shortname from document d,
processdocuments pd,
process P
where d.shortname in ('RIGHTTOKNOW', 'DISCLOSURE','TRANSCRIPTAUTHFORM','PROGRAMGRADINFO','CERTIFICATION')
and d.id=documentid
and p.id=processid
union all
select d.shortname,case when (d.shortname='CERTIFICATION' and p.shortname='REENTRY')then 'REENTRY' end  from document d,
processdocuments pd,
process P
where d.shortname in ('RIGHTTOKNOW', 'DISCLOSURE','TRANSCRIPTAUTHFORM','PROGRAMGRADINFO','CERTIFICATION')
and d.id=documentid
and p.id=processid

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130037
Perhaps a slightly simpler form of the query would be better:
SELECT D.shortname,
       P.shortname
FROM   document D,
       processdocuments PD,
       process P
WHERE  D.shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                       'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                       'CERTIFICATION')
  AND D.id=documentid
  AND P.id=processid
  and P.shortname in ('ENROLLMENT', 'REENTRY')
ORDER BY P.shortname,
         D.shortname;

Open in new window

0
 

Author Comment

by:crosstf
ID: 24130453
Okay, well what I need to happen is this.
I have a table called processdocuments with three columns. Those colums are ProcessID, DocumentID, and active.

What I need to actually is run this select statement:

Select ID,active from dbo.[document]
where Shortname IN('RIGHTTOKNOW', 'DISCLOSURE',
                       'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                       'CERTIFICATION')

and then I need an update statement to update  those three columns from  dbo.[ProcessDocuments] with
the values ( '4', document.ID, document.active)

So I need to cross-reference values from the dbo.[documents] table and update records into processdocuments with those values, resulting in 5 total records.


I misunderstood what I need to do based on the data structure earlier, but this is what needs to be done.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130903
When you say "I need an update statement to update  those three columns from  dbo.[ProcessDocuments] with
the values ( '4', document.ID, document.active)", do you really mean that you need to insert a new record into the table with those values?  
If so, check SQL_1 below.
However, if you really mean that you want to update a record, then you need to provide a little more information about which record you want to update.

SQL_1:
 
INSERT INTO dbo.[ProcessDocuments]
(
 ProcessID, 
 DocumentID, 
 active
)
SSELECT '4',
       ID,
       active 
FROM dbo.[document] 
WHERE Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION');

Open in new window

0
 

Author Comment

by:crosstf
ID: 24130947
This is what i came up with, but I am getting a primary_key constraint....

but I am getting this error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_ProcessDocuments'. Cannot insert duplicate key in object 'dbo.ProcessDocuments'.
The statement has been terminated.

any suggestions?  Now I know what the error is saying but if I run this select query:


Select* from ProcessDocuments
where ProcessDocuments.DocumentID IN (37)

I get:

ProcessID   DocumentID   Required
1      37      1
4      37      1

so it must be allowing duplicates entries somehow....



update dbo.[ProcessDocuments]
set ProcessDocuments.DocumentID=Document.ID, ProcessDocuments.ProcessID='4', ProcessDocuments.Required = Document.active
from ProcessDocuments, Document
where Document.Shortname IN('RIGHTTOKNOW', 'DISCLOSURE',
                       'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                       'CERTIFICATION') 
and
 
ProcessDocuments.ProcessID='4' 

Open in new window

0
 

Author Comment

by:crosstf
ID: 24130981
8080_Diver: I entered my previous comment as you were entering yours so it is before I saw your comment. As to your question. Yes, I need to insert the information, but there may be instances where an entry already exists, so I am trying to avoid a duplication.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24131073
In that case, the code needs a slight modification.  What is the Primary Key of the target table and, if the row already exists, what do you want done?  
Right now, I am assuming that you do NOT want anything entered because that row already exists as you would have it (either by updating or inserting).
I am also assuming that the ProcessID and the DocumentID are the Primary Key. ;-)

INSERT INTO dbo.[ProcessDocuments]
(
 ProcessID, 
 DocumentID, 
 active
)
SSELECT '4',
       D.ID,
       D.active 
FROM dbo.[document] D
LEFT OUTER JOIN dbo.[ProcessDocuments] PD
  ON PD.ProcessID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION')
  AND PD.ID IS NULL;

Open in new window

0
 

Author Comment

by:crosstf
ID: 24131203
Yes ProcessID and DocumentID are the PK's in dbo.[ProcessDocuments].
What I need to occur is this:

the number 4 is referenced as 'REENTRY' from another table.

So what I need to have happen  is when the select query from your first comment is run this results:

CERTIFICATION                     ENROLLMENT
TRANSCRIPTAUTHFORM      ENROLLMENT
DISCLOSURE                        ENROLLMENT
RIGHTTOKNOW                    ENROLLMENT
PROGRAMGRADINFO          ENROLLMENT
CERTIFICATION                    REENTRY
TRANSCRIPTAUTHFORM     REENTRY
DISCLOSURE                       REENTRY
RIGHTTOKNOW                   REENTRY
PROGRAMGRADINFO         REENTRY

So, in effect there needs to be a REENTRY or '4' entered in the table dbo.[processdocuments] for all five cases, but I need to cover the possiblity that one or more may already be entered and avoid entering duplicates.  

The table structure for dbo.[processdocuments]
is:

ProcessID: 1, 2, 3, or 4. The only relevant scenario for this query is 4. I am entering cases for REENTRY.
I need to dynamically reference DocumentID from dbo.[document]  ex. enrollment = 1

DocumentID. This the select query from dbo[document], we are selecting the numberic ID's from Document.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION')


Required: set this equal  to document.active. It  is boolean, so it can be set to true if that is easier.



0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24131304
The last set of code I provided should insert a new row for any of the 5 rows that do not already have a row indicating 'REENTRY'.
If you run that INSERT query prior to running a SELECT and you include the 'REENTRY' in the selections, then you should get the results you want.
0
 

Author Comment

by:crosstf
ID: 24131332
The code you provided, renenterd below, gets this error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_ProcessDocuments'. Cannot insert duplicate key in object 'dbo.ProcessDocuments'.
The statement has been terminated.


Should I run a delete query ahead of the query below to wipe out any instance of REENTRY where the select statement exists? Thus removing the chance a primary key violation?


INSERT INTO dbo.[ProcessDocuments]
(
 ProcessID, 
 DocumentID, 
 Required
)
SELECT '4',
       D.ID,
       D.active 
FROM dbo.[Document] D
LEFT OUTER JOIN dbo.[ProcessDocuments] PD
  ON PD.ProcessID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION')
  AND PD.ProcessID IS NULL
  AND PD.DocumentID is NULL;

Open in new window

0
 

Author Comment

by:crosstf
ID: 24131361
Maybe something Like this?
Delete 
 
 [ProcessID, 
 DocumentID, 
 Required]
 
from dbo.[ProcessDocuments] 
 
where EXISTS (
SELECT '4',
       D.ID,
       D.active 
FROM dbo.[Document] D
LEFT OUTER JOIN dbo.[ProcessDocuments] PD
  ON PD.ProcessID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION'));

Open in new window

0
 

Author Comment

by:crosstf
ID: 24132504
Ok. I think that will work,but how do you reverse the above insert statement and delete the values entered?
INSERT INTO dbo.[ProcessDocuments]
(
 ProcessID, 
 DocumentID, 
 Required
)
SELECT '4',
       D.ID,
       D.active 
FROM dbo.[Document] D
LEFT OUTER JOIN dbo.[ProcessDocuments] PD
  ON PD.ProcessID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION')
  AND PD.ProcessID IS NULL
  AND PD.DocumentID is NULL;

Open in new window

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24132717
Oops!  Slight typo on my part.
You shouldn't need to delete rows just so you can reinsert them. ;-)
The LEFT OUTER JOIN is on the PK values from your ProcessDocuments and joining on the Document table, in case I still don't have it quite right.  (Can't see your tables from here so I am flying on instruments only. ;-)

INSERT INTO dbo.[ProcessDocuments]
(
 ProcessID, 
 DocumentID, 
 Required
)
SELECT '4',
       D.ID,
       D.active 
FROM dbo.[Document] D
LEFT OUTER JOIN dbo.[ProcessDocuments] PD
  ON PD.DocumentID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION')
  AND PD.ProcessID IS NULL
  AND PD.DocumentID is NULL;

Open in new window

0
 

Author Comment

by:crosstf
ID: 24132874
How would I delete the above query, if I needed the records removed?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24133010
Kind of depends on whether you want to nuke selected ones or all of them.  SQL_1 nukes 'em all and SQL _2 should nuke the ones that matched the original insert criteria.

SQL_1:
 
DELETE FROM dbo.[ProcessDocuments]
WHERE ProcessID='4';
 
 
SQL_2:
 
DELETE PD
FROM dbo.[ProcessDocuments] PD
INNER JOIN dbo.[ProcessDocuments] PD
  ON PD.DocumentID=D.ID AND
     PD.ProcessID='4' 
WHERE D.Shortname IN ('RIGHTTOKNOW', 'DISCLOSURE',
                    'TRANSCRIPTAUTHFORM','PROGRAMGRADINFO',
                    'CERTIFICATION');

Open in new window

0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 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