Copy a record with autonumber on

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Copy a record with autonumber on

Author

Topic

fastercall
Starting Member

1 Post

Posted - 2004-07-23 : 15:07:51

Simple question, but I've yet to find the true answer I'm looking for.

I want to copy a record out of an access database and duplicate all of the info in the fields in a new record in the same table - except I have autonumber turned on for the primary key, and I want the new record to generate a new autonumber.

I've seen suggestions to use the Select, and Insert Into. Problem is the table is rather large and I have about 50 fields.

What I'm trying to do is this:

INSERT INTO table
SELECT * FROM same table
WHERE record ID=XXX

The problem though is that this statement grabs the autonumber as well and I get an error statement that says I can't have duplicate primary keys.

Is there a way to SELECT * except the autonumber field or a way around this? The only other alternative for me is to SELECT (field1, field2,......field 50) which is a pain.

Thanks,
Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-23 : 15:10:26

No, you will need to specify an explicity column list, just exclude the autonumber column in the both lists (INSERT and SELECT).

Tara

CactusJuice
Starting Member

46 Posts

Posted - 2004-07-24 : 01:32:59

DTS and enable identity insert.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-24 : 01:36:57

quote:
Originally posted by CactusJuice

DTS and enable identity insert.

That's a sledgehammer approach.

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-26 : 00:47:00

DTS and enable identity insert.

Will you please explain bit more for me.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 11:57:34

That would be highly not recommended to use for this. DTS is a bit overkill for something like lazy programming, no offense. You can easily generate the column list for you using the INFORMATION_SCHEMA views or the object browser in Query Analyzer.

Tara

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 12:20:10

Column list for MyTable


SELECT  COLUMN_NAME + ', '
FROM 	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources