• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

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.
0
larksys
Asked:
larksys
  • 7
  • 4
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now