?
Solved

I have two tables... I would like to update one table to have rep number that I want to have all the designations...

Posted on 2011-05-04
12
Medium Priority
?
308 Views
Last Modified: 2012-05-11
I would like to add every designation from the profdesignation table to the reptable for a single rep...so that rep 1998 has every designation that exists in the other table... please the pictures attache for a better understanding... they're screen shots of the actual tables...
SO THE REPTABLE SHOULD HAVE...
REPCODE      Designation
1998      AAMS
1998      AIF
1998      AIFA
ETC...
profdesig.jpg
reptable.jpg
0
Comment
Question by:syeager305
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 9

Expert Comment

by:sarabhai
ID: 35692979
Not getting what you want, please explain again.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35693004
I do not udnerstand. Where is RepCode 1998 in your screenshot?
0
 
LVL 5

Author Comment

by:syeager305
ID: 35693108
KEE      REPCODE      DESIGNATION      relin        reason
416      1998      AAMS®      null      null      null
417      1998      AIF®      null      null      null
418      1998      AIFA®      null      null      null
419      1998      APA      null      null      null
420      1998      APR      null      null      null
421      1998      Attorney      null      null      null

I just want a script to add every designation from the professionaldesignation table to the reptable for the number 1998 ...

the only think that changes is the designation... in each entry is the designation, the designation is from the professiondesignation table....
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Author Comment

by:syeager305
ID: 35693152
The key should just automatically generate...the repcode will stay the same, and designation will be the next designation from the professionaldesignation table... the rest of the values in yearearned relinquished and reasonwill remain null....
am I making sense?...:)
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35693435
no :)
0
 
LVL 5

Author Comment

by:syeager305
ID: 35693493
I want to take the designations from the first table and add them to rep number 1998 in the second table, the rep number can repeat, I can have muliple rows with rep number 1998...

Or another way of putting it, i want to add additional rows with rep number 1998 with every designation from the professionaldesignations table (first table)...
0
 
LVL 5

Author Comment

by:syeager305
ID: 35693522
INSERT INTO reptable (kee, repcode, designation,...)
VALUES (nextnumber, 1998, AAMS,...)
INSERT INTO reptable (kee, repcode, designation,...)
VALUES (nextnumber, 1998, AIF,...)
INSERT INTO reptable (kee, repcode, designation,...)
VALUES (nextnumber, 1998, AIFA,...)
ETC...

Does that make more sense? :(
0
 
LVL 5

Author Comment

by:syeager305
ID: 35693535
the only two values that change are the KEE and the AAMS... but rather than typing ...

INSERT INTO reptable (kee, repcode, designation,...)
VALUES (nextnumber, 1998, AIF,...)
out a 100 times, I just want a script to that will do it for me...
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35693571
I assume kee is identity column and exclude it from the columns list in INSERT statement. You can try like this.
INSERT INTO reptable (repcode, designation,...)
SELECT distinct 1998,Designation,null,null -- NULLs for all other columns in the INSERT clause
  FROM profdesignation

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35693725
maybe this
INSERT INTO reptable (kee, repcode, designation)
select (select max(kee) from reptable) + t2.rn Kee, 1998, t2.designation 
from (
select row_number() over (order by designation) rn, designation 
from (SELECT distinct Designation FROM profdesignation ) t1
) t2

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35693740
or this one
with maxKee as (select max(kee) BaseKee from reptable)
INSERT INTO reptable (kee, repcode, designation)
select maxKee.BaseKee + t2.rn Kee, 1998, t2.designation 
from (
select row_number() over (order by designation) rn, designation 
from (SELECT distinct Designation FROM profdesignation ) t1
) t2, maxKee

Open in new window

0
 
LVL 5

Author Closing Comment

by:syeager305
ID: 35693821
That worked, thank you!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 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