Set Identity on existing column
Database of one of our project is stored by Microsoft Access. As you know, MS.Access can’t store much of data amount in it. Lack of such appearance, our website is spontaneously down whenever users surfing through it. That’s why we need to upgrade our DBMS to MsSQL. So, I’m in rush of doing so. First of all, I need to export all data as XML file. Then, import these data to Ms.SQL. But I wanna keep certain ID column of each tables. That’s why I import all data as OFF identity Mode. After importing, I need to turn ON. At that time, I’m in trouble. NO idea to do how to turn ON identity and thinking of various ways. Eventually, I gotcha.
Here we go..
<cftransaction>
<!– Creating Temp Table –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
CREATE TABLE Categories2
(
C_ID int primary key identity(1,1),
C_Name nvarchar(500),
C_SortOrder int,
C_Text nvarchar(4000),
C_Parent int
)
</cfquery><!– Set OFF Identity –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
SET IDENTITY_INSERT Categories2 ON;
</cfquery><!– Migrate Data –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
INSERT INTO Categories2(C_ID, C_NAME, C_SortOrder, C_Text, C_Parent) SELECT C_ID, C_NAME, C_SortOrder, C_Text, C_Parent FROM Categories
</cfquery><!– Set On Identity –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
SET IDENTITY_INSERT Categories2 OFF;
</cfquery><!– Drop old table –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
DROP TABLE Categories
</cfquery><!– Rename Table –>
<cfquery name=Items datasource=”#application.ds#” password=”#application.pw#” username=”#application.un#”>
SP_RENAME Categories2, Categories;
</cfquery>
</cftransaction>
