Solved

SQL Insert, multiple tables

Posted on 2009-04-13
15
185 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:crosstf
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
How would I delete the above query, if I needed the records removed?
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
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…
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…

743 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

17 Experts available now in Live!

Get 1:1 Help Now