Copying a record with an identity field

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 2005 Forums

Transact-SQL (2005)

Copying a record with an identity field

Author

Topic

eglons
Starting Member

15 Posts

Posted - 2010-06-23 : 08:35:15

I am trying to do a simple record copy:

insert into tableA SELECT * FROM tableB where unique_id=unique_value

The problem is that tableA has an identity field in it and when trying to do the insert I get and error:

"An explicit value for the identity column in table 'tableA' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Specifying the column list is a real pain as it's a very big table and columns are added and removed all of the time.

Is there a way to copy a record in some other way that anyone can help me with?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 08:38:43

set indentity_insert tableA on
insert into tableA SELECT * FROM tableB where unique_id=unique_value
set indentity_insert tableA off

Madhivanan

Failing to plan is Planning to fail

eglons
Starting Member

15 Posts

Posted - 2010-06-23 : 08:55:30

Sorry I realise that, but that still forces a column list and as I say the table is huge and the fields change frequently.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-23 : 09:01:45

copy and paste the result

Select name+',' from sys.columns where object_id=object_id('table_name')
order by column_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-23 : 09:21:22

Maybe this will do it for you? ->

DECLARE 
	@sql nvarchar(max),
	@columns varchar(max) = '', 
	@source varchar(50) = 'tableA',
	@dest varchar(50) = 'tableB'

SELECT @columns = @columns + source.COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS source
	INNER JOIN INFORMATION_SCHEMA.COLUMNS dest
		ON source.COLUMN_NAME = dest.COLUMN_NAME
WHERE source.TABLE_NAME = @source
	AND dest.TABLE_NAME = @dest
	
SET @columns = LEFT(@columns, LEN(@columns) -1)

SET @sql = 'INSERT INTO ' + @dest + '(' + @columns + ') SELECT ' + @columns + ' FROM ' + @source
EXEC sp_executesql @sql

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-23 : 09:22:27

Oh...and if the identity column is a part of the matched columns you need to set the identity_insert on as madhi just showed you.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

eglons
Starting Member

15 Posts

Posted - 2010-06-23 : 09:30:39

Thanks fo rthat, I'd gone down a similar tack and come up with this

SELECT @columnlist = COALESCE(@columnlist + ',','') + COLUMN_NAME FROM information_schema.columns WHERE table_name = 'tableA' ORDER BY ORDINAL_POSITION
SET IDENTITY_INSERT dbo.tableA ON;
SELECT @sql='INSERT INTO dbo.tableA (' + @ColumnList + ') SELECT * FROM #tableB;'
EXEC (@sql)
SET IDENTITY_INSERT dbo.tableA ON;

Which seems to work, is one better than the other?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-23 : 09:32:41

quote:
Originally posted by eglons

Sorry I realise that, but that still forces a column list and as I say the table is huge and the fields change frequently.

Wow, that is scary. The columns in the table change frequently?

Just use what Lumbago gave you, it won't matter that columns change. his script will adapt to that.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

eglons
Starting Member

15 Posts

Posted - 2010-06-23 : 12:44:26

quote:
Originally posted by DonAtWork

quote:
Originally posted by eglons

Sorry I realise that, but that still forces a column list and as I say the table is huge and the fields change frequently.

Wow, that is scary. The columns in the table change frequently?

Just use what Lumbago gave you, it won't matter that columns change. his script will adapt to that.

The issue is that the columns might change and I dont want to have to adjust the stored proc I'm using every time.

I'd already implemented the code I posted which seems to work, is the other code better for some reason?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-24 : 02:03:57

If the schema of tableA and tableB are in sync at all times then your method will work. If they are not in sync it will fail...the method I posted will always work (as long as the column names are the same in both tables).

That being said; this is a HORRIBLE design and you should change it. Tables should not have their schema change unless you are doing major redesigns. If it needs to be changed all the time you are probably doing something that could be solved in a far better way...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources