June 27, 2011 at 2:21 pm
I've got a large table (70+G) with an identity column. I want to rebuild the table using paritioning but don't want to lose the identity or the existing values in the column. How can I do that?
Let's say I bcp out the data. When I reload the table, I have to have it be a non-identity integer column. But then there's no way to turn the identity on at that, right?
And I can't load the data if I make it an identity.
Hope that makes sense...
What's the way around this?
June 27, 2011 at 2:43 pm
IDENTITY_INSERT will let you explicitly insert values into an identity column, and then behave like a normal identity column once you turn IDENTITY_INSERT off.
http://msdn.microsoft.com/en-us/library/ms188059%28v=SQL.90%29.aspx
June 28, 2011 at 5:16 pm
Whisper9999 (6/27/2011)
I've got a large table (70+G) with an identity column. I want to rebuild the table using paritioning but don't want to lose the identity or the existing values in the column. How can I do that?Let's say I bcp out the data. When I reload the table, I have to have it be a non-identity integer column. But then there's no way to turn the identity on at that, right?
And I can't load the data if I make it an identity.
Hope that makes sense...
What's the way around this?
IDENTITY_INSERT is relevant for T-SQL. For retaining identity values when loading data with bcp you can use the -E option:
http://technet.microsoft.com/en-us/library/ms162802.aspx
If retaining your original IDENTITY SEED value is also important you can run this query before bcp'ing out:
DBCC CHECKIDENT('dbo.table_name')
resulting in something like this:
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
And then using the current identity value you received above, run this after you have loaded the data back into your database:
DBCC CHECKIDENT('dbo.table_name',RESEED,1)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 7:18 pm
opc.three (6/28/2011)
Whisper9999 (6/27/2011)
I've got a large table (70+G) with an identity column. I want to rebuild the table using paritioning but don't want to lose the identity or the existing values in the column. How can I do that?Let's say I bcp out the data. When I reload the table, I have to have it be a non-identity integer column. But then there's no way to turn the identity on at that, right?
And I can't load the data if I make it an identity.
Hope that makes sense...
What's the way around this?
IDENTITY_INSERT is relevant for T-SQL. For retaining identity values when loading data with bcp you can use the -E option:
http://technet.microsoft.com/en-us/library/ms162802.aspx
If retaining your original IDENTITY SEED value is also important you can run this query before bcp'ing out:
DBCC CHECKIDENT('dbo.table_name')resulting in something like this:
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
And then using the current identity value you received above, run this after you have loaded the data back into your database:
DBCC CHECKIDENT('dbo.table_name',RESEED,1)
Thank you! I was planning on using bcp actually...
June 28, 2011 at 11:51 pm
Whisper9999 (6/28/2011)
Thank you! I was planning on using bcp actually...
No problem. Happy something I said was useful 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply