Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

SQL Insert, multiple tables

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
crosstf
Asked:
crosstf
  • 8
  • 6
1 Solution
 
vinurajrCommented:
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
 
8080_DiverCommented:
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
 
crosstfAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
8080_DiverCommented:
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
 
crosstfAuthor Commented:
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
 
crosstfAuthor Commented:
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
 
8080_DiverCommented:
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
 
crosstfAuthor Commented:
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
 
8080_DiverCommented:
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
 
crosstfAuthor Commented:
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
 
crosstfAuthor Commented:
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
 
crosstfAuthor Commented:
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
 
8080_DiverCommented:
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
 
crosstfAuthor Commented:
How would I delete the above query, if I needed the records removed?
0
 
8080_DiverCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now