Solved

SQL - Copy and Paste with modified values

Posted on 2015-02-19
12
41 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 4

Author Comment

by:Kent Fichtner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No.  You posted that after my initial post of the SQL code.
0
 
LVL 4

Author Comment

by:Kent Fichtner
Comment Utility
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
Comment Utility
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

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

762 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

7 Experts available now in Live!

Get 1:1 Help Now