Need to copy a block of records within same table

Question

Post reply

Need to copy a block of records within same table

Ray Stille

SSC Journeyman

Points: 93
More actions
July 12, 2006 at 10:03 am

#168965

Hi, I need to select all the records in a table and copy them to the same table, changing values only in one column (the value is the same for all records). There are 1500 records aprox.
Right now, I read the records and run a Do While re-inserting them one-by-one, but I imagine this is not optimal. I read about the "Insert From" but could Any suggestions?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

It's not hard.

insert into tablename(fieldname1, fieldname2, updatedfield, ..., fieldnamen)

select fieldname1, fieldname2, 'zzz', ..., fieldnamen

from tablename

each time you run it, it doubles the size of your table.

Ray Stille SSC Journeyman Points: 93 More actions · Answer 2

Ray Stille

SSC Journeyman

Points: 93

July 12, 2006 at 10:39 am

#648760

Yup! that did it. Thx Phil!!

Dave Gotera Valued Member Points: 50 More actions · Answer 3

ei guys i need ur help ive been reading ur post but i hardly understand it...i wanted to copy a record within same table changing only the value of a 1 field...can u explain it to me clearly pls?...i have a table named SERVICE..the fields inside it are as follows....

...groupno,servicecode,servicename,systemno,upload,controlno..

i wanted to copy all records from service where groupno = 1 and insert all the records within the table replacing only the field value groupno = 1 into groupno = 6. tnx in advance..

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,ControlNo)

SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,ControlNo

FROM Service

WHERE GroupNo=1

The select takes all the records in Service that have a GroupNo of 1 (Where...)

The constant in the select clause (SELECT 6, ...) means take the values from the other columns, but take the constant value 6 as the first field in the resultset.

The insert then adds the resultng recordset to the Service table.

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Gotera Valued Member Points: 50 More actions · Answer 5

I hav tried the script u have given but it didnt work.................it show something like this...

violation of primary or unique key constraint "INTEG_25" on table "SERVICE"

wat does it mean?

by the way i did the testing in database workbench to test the sql script and it show something like above..

i am new to sql so pls bear wd me...tnx!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

It means that you have a primary key on the table, a constraint that forces all values in a column to be unique.

Since you tried to insert duplicate values (a copy of a portion of the table with a different groupNo), the query failed.

You need to find out which column has the pk, and how new values for it are generated. Is there someone around who knows the system?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Gotera Valued Member Points: 50 More actions · Answer 7

wat if il remove the pk of the table will it work? how will i know if wat field in the table is the primary key...i am using a gdb database with clarion interface.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

Don't remove the primary key unless you really really know what you are doing! The PK is there to enforce uniqueness between the records in the table - if you remove it, you will lose the integrity of your database. Also, you will not be able to put back the PK once you have run the above query, because the PK column will no longer contain unique values.

As already mentioned, you need to find out which column is the PK and how new PK values are generated. Then we can help you formulate the query to do what you want.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

You'll have to look at the database structure to find what field is the pk, or ask someone who knows the system. You can see the db structure with Query analyser or SQL Management studio. I'm not familiar with Clarion.

What's gdb?

I would advise you not to remove the primary key unless you know what you're doing. It's there for a reason, to ensure data integrity.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Gotera Valued Member Points: 50 More actions · Answer 10

its gnome database....a database used by firebird/interbase..if mysql uses *.frm for database file type...firebird/interbase uses *.fdb and *.gdb....so the pk just refuses duplicate entry just like wat i expected...ok tnx for the help...il try it with no pk and ill inform u later for the outcome...where are u anyway?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Once more with feeling. Don't take the primary key off unless you know exactly what you're doingf and exactly what the consequences will be.

It's there to ensure data integrity. Without a pk, you're likely to get duplicate data which is a pain to remove later.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Gotera Valued Member Points: 50 More actions · Answer 12

ok tnx for the care...dont wori im gonna test it first into another database...if theres a way somehow to do it without removing the pk...is it possible?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 13

Yes, but you need to find out what field is the pk and how the values for it are generated. It may be an autogenerated value, it may be a user-entered code that must be unique.

Once we know how the values for the pk are generated, then we can generate a query to insert with unique values.

btw, I'm from South Africa

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Gotera Valued Member Points: 50 More actions · Answer 14

the problem still unsolved....it returned an error below

Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,timestampstr)

SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,TIMESTAMPSTR

FROM Service

WHERE GroupNo=1

error

violation of PRIMARY or UNIQUE KEY constraint "INTEG_29" on table "SERVICE"

the primary key is the timestampstr

how can i make this possible is there anyway i can do this?

the primary key do not allow sql statement at all.