Solved

SQL - Copy and Paste with modified values

Posted on 2015-02-19
12
46 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:
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 37
SQL server 2014 replication error 20 42
SQL Server Configuration Manager WMI Error 11 20
sql server query 18 42
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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

828 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