?
Solved

SQL - Copy and Paste with modified values

Posted on 2015-02-19
12
Medium Priority
?
51 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

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