Solved

SQL Insert, multiple tables

Posted on 2009-04-13
15
189 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

685 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