Solved

SQL - Copy and Paste with modified values

Posted on 2015-02-19
12
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:
Scott Pletcher 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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:Scott Pletcher
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
 
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

717 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