Copy records form one to another Table

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......)
DoomsDirkAsked:
Who is Participating?
 
plqConnect With a Mentor Commented:
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
 
plqCommented:
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
 
DoomsDirkAuthor Commented:
Is there a solution that doesn't include a temp table ? The problem I do not have access rights to make new tables....
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
plqCommented:
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
 
LowfatspreadConnect With a Mentor Commented:
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
 
ATAHACCommented:
yuo can make identity your column GeweigerdeKostId
and then all your records will have unique GeweigerdeKostId
0
All Courses

From novice to tech pro — start learning today.