Solved

Copy records form one to another Table

Posted on 2004-04-09
8
786 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
8 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 8

Accepted Solution

by:
plq earned 200 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 200 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

17 Experts available now in Live!

Get 1:1 Help Now