Copying a table with an identity column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Copying a table with an identity column?

Author

Topic

Debbie2
Starting Member

11 Posts

Posted - 2006-09-16 : 00:48:58

Does anyone know how I can get this query to work? I'm copying a row from a table into another identical table.

quote:
set identity_insert character.dbo.charinfo on

insert into [character].dbo.charinfo
select * from character_bk.dbo.charinfo
where AID=438122

set identity_insert character.dbo.charinfo off

It returns this error:

quote:
Server: Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'character.dbo.charinfo' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I know the query would work if I typed this:

quote:
set identity_insert character.dbo.charinfo on

insert into character.dbo.charinfo (GID,CharName,job,clevel,sppoint,exp,
jobpoint,STR,AGI,VIT,INT,DEX,LUK,hp,maxhp, sp,maxsp, AID,mapName,
xPos, yPos,GuildID,CharNum,speed,money,jobexp,virtue,honor,
haircolor,joblevel,bodystate,healthstate,sxPos,syPos,restartMapName,
effectstate,head,weapon,accessory,headpalette,bodypalette,
accessory2,accessory3,shield)
(select * from character_bk.dbo.charinfo where AID=438122)

set identity_insert character.dbo.charinfo off

but I'd rather not hardcode all the column names into my code, because then if the table definition changes in the future my code will break.

Suggestions?

Kristen
Test

22859 Posts

Posted - 2006-09-16 : 01:27:35

"Suggestions?"

'Fraid not, you have to specify all columns.

I think in these circumstances you are right to have a column list on the Target table (INSERT) and a SELECT * on the Source table, at least that way if a column is added the code will break and someone will find and fix it.

However, if two columns get swapped over you will be in big trouble - unless the datatypes happen to cause an error ... so you're between a rock and a hard place!

I think on balance it would be better to have full column lists for both INSERT and SELECT, and an additional statement that checks the number of columns in INFORMATION_SCHEMA.COLUMNS and raises an error if the number is wrong.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-16 : 08:56:08

Simple way of concatenating all columns

Declare @sql varchar(8000)
Select @sql=COALESCE(@sql+',','')+column_name from information_Schema.columns
where table_name='yourTableName'
Print @sql

Madhivanan

Failing to plan is Planning to fail

Debbie2
Starting Member

11 Posts

Posted - 2006-09-16 : 20:21:27

quote:
Originally posted by madhivanan

Simple way of concatenating all columns

Declare @sql varchar(8000)
Select @sql=COALESCE(@sql+',','')+column_name from information_Schema.columns
where table_name='yourTableName'
Print @sql

Hmm, is there a way to execute a variable as a query? e.g.:

@sql = 'select * from foo'
exec @sql

Or would such a thing have to be done using a script outside MSSQL?

Kristen
Test

22859 Posts

Posted - 2006-09-17 : 02:17:59

You can use

EXEC (@sql)

which is fine. I suggest you also have a look at sp_ExecuteSQL - probably won't help for this example, but if you have any Dynamic SQL that you can parametrise then sp_ExecuteSQL will perform much more efficiently than EXEC (@SQL)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources