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

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

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 *?

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

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.

I see the typo.

>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

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>

The column userid is the identity column and should autoincrement after I populate the table.

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#)

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