Solved

Copy records form one to another Table

Posted on 2004-04-09
8
787 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

21 Experts available now in Live!

Get 1:1 Help Now