We help IT Professionals succeed at work.

sql cf8 table migration - need to turn off autoincrement then back on

I am migrating data to a new database in SQL 2005. I need to clear the target table, turn off autoincrement, migrate data, turn autoincrement back on.
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
TRUNCATE TABLE urTable
SET IDENTITY_INSERT urTable  ON
go
INSERT INTO urTable (ColumnList )
SELECT * FROM urSourceTbale
GO
SET IDENTITY_INSERT urTable OFF

Author

Commented:
I guess I need my UserID column to be the indentity column. It is not set now. Why is the insert into spec'd before the select *?

Author

Commented:
I'm confused. Why would I set indentity on, write data, then turn it off?

Author

Commented:
Still confused.


<cfquery name="ttd1" datasource="#request.dsn#">
truncate table users
set indentity_insert users off
</cfquery>


 [Macromedia][SQLServer JDBC Driver][SQLServer]'indentity_insert' is not a recognized SET option.

Author

Commented:
I see the typo.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>I guess I need my UserID column to be the indentity column. It is not set now. Why is the insert into spec'd before the select *?

If at present there is no identity column,  first you need to alter your table to add an Identity column

ALTER TABLE urTable
ADD identityColumn int identity
go
TRUNCATE TABLE urTable
SET IDENTITY_INSERT urTable  ON
go
INSERT INTO urTable (ColumnList )
SELECT ColumnList  FROM urSourceTbale
GO
SET IDENTITY_INSERT urTable OFF

Author

Commented:
still confused;

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

<cfquery name="ttd1" datasource="#request.dsn#">
TRUNCATE TABLE USERS
SET IDENTITY_INSERT USERS OFF
</cfquery>

<cfquery name="QI" datasource="AS400"  dbtype="ODBC">
select *
from istatic
where iscomp = 12504
order by isrecn
</cfquery>

<cfoutput query="QI" group="isrecn">

Rec No.-#QI.isrecn#   #trim(TitleCase(QI.isfnam))#  #trim(TitleCase(QI.islnam))#  #trim(TitleCase(QI.isadr1))#  #trim(TitleCase(QI.isadr2))#  #trim(TitleCase(QI.iscity))#  #QI.isstat#
<cfquery name="QusersInsert" datasource="#request.dsn#">
Insert into users
(userid,membername,password,email,dateestablished)
values(#val(QI.isrecn)#,'#trim(TitleCase(QI.isfnam))#' + ' ' + '#trim(TitleCase(QI.islnam))#','#QI.isrecn#','#QI.isemai#',#QI.isdent#)
</cfquery>

Author

Commented:
The column userid is the identity column and should autoincrement after I populate the table.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
I am not so clear eventhough I have the following suggession

if you want that identity column to start from 1 then you just have to do a normal insert , and no need of setting the Identiy_insert on and off


Insert into users(membername,password,email,dateestablished)   -- removd the userId from the list
values('#trim(TitleCase(QI.isfnam))#' + ' ' + '#trim(TitleCase(QI.islnam))#','#QI.isrecn#','#QI.isemai#',#QI.isdent#)

Author

Commented:
OK. I get the identity_insert on and off now. Just for grins, I would like to learn how to turn autoincrement on and off. Also, I have 2 tables to insert into. Users and users.info. I can truncate users but I get an error when I truncate users.info. It says it can't find the table users.info.

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot find the object "INFO" because it does not exist or you do not have permissions
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
is ur table name 'info' or User.Info in case of the Later use   [Users.Info]

Explore More ContentExplore courses, solutions, and other research materials related to this topic.