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.
LVL 1
larksysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
larksysAuthor 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 *?
0
larksysAuthor Commented:
I'm confused. Why would I set indentity on, write data, then turn it off?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

larksysAuthor 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.
0
larksysAuthor Commented:
I see the typo.
0
Aneesh RetnakaranDatabase AdministratorCommented:
>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
0
larksysAuthor 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>
0
larksysAuthor Commented:
The column userid is the identity column and should autoincrement after I populate the table.
0
Aneesh RetnakaranDatabase AdministratorCommented:
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#)

0
larksysAuthor 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
0
Aneesh RetnakaranDatabase AdministratorCommented:
is ur table name 'info' or User.Info in case of the Later use   [Users.Info]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.