Solved

SQL - Copy and Paste with modified values

Posted on 2015-02-19
12
43 Views
Last Modified: 2015-03-03
Attached is a super simple table with the part code, the facility it is in and a primary key (gem_id).

what I would like to do is copy the part codes and append them to the bottom but change the facility to 5 and allow the gem_id to continue counting.

I am pretty sure part of this is possible but I haven't found a way that allows covers everything.
Book1.xlsx
0
Comment
Question by:Kent Fichtner
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40618959
if you change B3 to =B2 and C3 to =C2+1, and copy B and C down to row 5, then

If you copy rows 2-5 into rows 6-9 and change B5 to 5, that would do it.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40619136
SELECT new_data.part_code, 5 AS facility, max_gem_id + row_num AS gem_id
FROM (
    SELECT part_code, ROW_NUMBER() OVER (ORDER BY part_code) AS row_num
    FROM table_name
) AS new_data
CROSS JOIN (
    SELECT MAX(GEM_ID) AS max_gem_id
    FROM table_name WITH (TABLOCKX)
) AS max_values
0
 
LVL 32

Expert Comment

by:awking00
ID: 40619430
A6 = A2, B6 = B2+2, C6 = C5+1 then copy these to rows 7, 8, and 9
0
 
LVL 4

Author Comment

by:Kent Fichtner
ID: 40619576
I am sorry that I wasn't clear, I am looking for SQL help, the excel document was just to show what the data looked liked.  I will try Scott P's suggestion and go from there.
0
 
LVL 4

Author Comment

by:Kent Fichtner
ID: 40619645
I am not 100% clear on what the SQL command above is supposed to be doing.  the new_data part is throwing me the most.  I have attached the actual data from the table dbo.PM_AUTHORIZATION_GROUPS.  The green is what is in the table now, the orange is what I want to add to the table.

GEM_DBKEY - increase by one per entry
COMPANY_CODE - Stays the same
Factory - 05
PM_Auth_Group - QA or PC
CODE_DESC - same idea as auth group
PM-AUTHORIZATION-GROUPS.xlsx
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40619876
You didn't specify anything about checking for only unique entries earlier.  Thus, my code simply duplicated every row in the table.  I've added DISTINCT below, and changed "part_code" to "company_code":

SELECT max_gem_id + row_num AS gem_id, rows_from_existing_table.company_code, 5 AS facility, rows_from_existing_table.code_desc
FROM (
     SELECT *, ROW_NUMBER() OVER (ORDER BY company_code) AS row_num
     FROM (
         SELECT DISTINCT COMPANY_CODE, PM_Auth_Group, CODE_DESC
         FROM table_name
    ) AS derived
) AS rows_from_existing_table
 CROSS JOIN (
     SELECT MAX(GEM_ID) AS max_gem_id
     FROM table_name WITH (TABLOCKX)
 ) AS max_values
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 4

Author Comment

by:Kent Fichtner
ID: 40621091
Scott, thank you so much for the help!  the Select Distinct part ends with from table_name.  Do I have to fill this in with the name of the table or how does it know where to run?

I am asking because I pasted the above in to a query and checked it and it has no errors (not suppressed I just don't know how this works).  The only think I can think of is to make it a stored procedure on the tables I need.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40621340
You have to put in the name of the table.  You never mentioned your table name (at least that I recall), so I just used a generic name.
0
 
LVL 4

Author Comment

by:Kent Fichtner
ID: 40621566
scott, I did it was the name of the excel file and I also said
I have attached the actual data from the table dbo.PM_AUTHORIZATION_GROUPS

no matter, were there any other generic names that you used?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40621784
No.  You posted that after my initial post of the SQL code.
0
 
LVL 4

Author Comment

by:Kent Fichtner
ID: 40621958
Scott you are correct, I was saying that I pasted it before your revised code.  It doesn't matter, it was my fault I didn't get the correct information first.

Did you use any other generic names for anything else?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40621965
No, they are all from the same, base table.  In order to get the maximum existing GEM_ID/GEM_DBKEY, I do a separate read of the base table to get it.  I'm hoping that column is the leading key to an index, so that a single seek can find the max value very quickly.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 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

16 Experts available now in Live!

Get 1:1 Help Now