Updating identity column sql server
But the problem with IDENTITY columns is that you can only assign a specific value during INSERT using the IDENTITY_INSERT option. I would need a to create a new table, use IDENTITY_INSERT to copy over the rows from the current table, then recreate all foreign keys that refer to the old table so that they refer to the new table, then drop foreign keys referring to the old table, then drop the old table, then rename the new table to the old name. But I must admit, if you already have a table with an IDENTITY column, with rows, with foreign keys, it is already too late to switch to using a SEQUENCE, or at least you need the complicated plan, described above.
But in my case I generated a data warehouse house with bi GENi US, a data warehouse automation tool.
I need to keep certain ID column values across the database and was worried about turning on and off identity column AND automating the XML to DB scripting. SET IDENTITY_INSERT tablename ONSET IDENTITY_INSERT tablename OFFIt's wicked cool. I realize now, that it is referring to the SQL statements ability to insert identity column value, and hence, I need to turn it ON to run my scripts. Tool GO I have used the SET IDENTITY_INSERT feature several times when migrating data, but have always done so in an "off-line" mode.
I added ID’s to Data Vault 2.0 hubs to use for SCD Type 1 Dimensions.It is Hans is an Independent Business Intelligence and Datawarehouse Consultant & Microsoft SQL Server Consultant, working in the Netherlands.He has been working in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on datawarehouse- and business intelligence projects using Microsoft technology, using a Datavault and Kimball architecture.The flexibility of bi GENi US made it possible for me to change the template for the generated hub, and use a column that gets a value from a SEQUENCE instead of an IDENTITY column.So 15 minutes later I had what I wanted, an updatable sequence number in the hub that had a hashkey as primary key.