Improve company productivity with a Business Account.Sign Up

x
?
Solved

Copy records form one to another Table

Posted on 2004-04-09
8
Medium Priority
?
798 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

586 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