Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy records form one to another Table

Posted on 2004-04-09
8
Medium Priority
?
795 Views
Last Modified: 2012-08-13
I need to copy records from one table to another.

I figured out that I have to use the Insert Into statement but I ran into a problem
The records need to be inserted behind all the other records and the technical needs to be incremented by one for each record.

I have something like this atm:

insert into A_GeweigerdeKost(GeweigerdeKostId,
                        BijlageId,
                        CodeInstelling,
                        PersoneelsNr,
                        AfrekeningDtm,
                        LidNr,
                        ClaimNr,
                        FactuurNr,
                        BijlageNr,
                        ClausuleTeWeigeren,
                        OmschrijvingXML,
                        TeWeigerenMuntUBDg)
select (select max(GeweigerdeKostId) from A_GeweigerdeKost)+1,
            BijlageId,
            CodeInstelling,
            PersoneelsNr,
            AfrekeningDtm,
            LidNr,
            ClaimNr,
            FactuurNr,
            BijlageNr,
            Clausule,
            OmschrijvingXML,
            0
from A_BijlageClausule
where Clausule > 999 and Clausule < 4000

The problem is that I get an error: Violation of PRIMARY KEY constraint 'PK_A_GeweigerdeKost'. Cannot insert duplicate key in object 'A_GeweigerdeKost'

the technical is in the insert table is GeweigerdeKostId. For each record it should become the last max key + 1

How do I solve this ? (In Visual basic or something I would use a for loop but in sql......)
0
Comment
Question by:DoomsDirk
6 Comments
 
LVL 8

Expert Comment

by:plq
ID: 10790004
This is a "latency" problem. (select max(GeweigerdeKostId) from A_GeweigerdeKost)+1 will return the same value for each record.

Move into a temp table with an identity

create table #temp(id int identity(1,1), fields...)

Then

insert into #temp (fields excluding id) select .....

and finally

declare @maxid int
select @maxid = max(idfield) from A_

insert into A_  .... select id + @maxid, fields from #temp


0
 

Author Comment

by:DoomsDirk
ID: 10790052
Is there a solution that doesn't include a temp table ? The problem I do not have access rights to make new tables....
0
 
LVL 8

Expert Comment

by:plq
ID: 10790131
I don't think so, but maybe others will know

Unless of course you don't care about wasting IDs. Then you can simply add @maxid to GeweigerdeKostId - thats not a nice solution



0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Accepted Solution

by:
plq earned 800 total points
ID: 10790149
This little beauty generates a rownumber against each record of the table, its a way of simulating the Oracle ROWNUM field in Sql Server

   select rank=count(*), a.mytableid
   from mytable a, mytable b
    where a.mytableid > b.mytableid
   group by a.mytableid
   order by 1

All you need to do then is add rank to @maxid

Get it working as a select before you try inserting.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 10790294
you should have the rights to create a temporary table prefixed #

 but i'm puzzled why isnt GeweigerdeKostId, an identity column anyway if your just incrementing by 1?

0
 
LVL 4

Expert Comment

by:ATAHAC
ID: 10790371
yuo can make identity your column GeweigerdeKostId
and then all your records will have unique GeweigerdeKostId
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

877 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