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

SQL copying tables from one database to another

I have to copy data into tables with incremented identifiers...  I did a select into and then renamed the table, but then SQL 2012 will not allow me to change the column into what was the identifier because it now has data?
0
urthrilled
Asked:
urthrilled
3 Solutions
 
ProjectChampionCommented:
use the following method:

SET IDENTITY_INSERT <myTable> ON
insert into <myTable>...
SET IDENTITY_INSERT <myTable> OFF
0
 
urthrilledAuthor Commented:
So, it looks like that would turn the identity on during copying the data in?

I need to copy about 8500 rows and keep the identifier they currently have
0
 
magarityCommented:
the name of the command does look a little funny at first glance but it means "let me (instead of the system) insert identity values"
0
 
Steve WalesSenior Database AdministratorCommented:
The usual behavior is that you can't insert a value into an identity column.

With IDENTITY_INSERT set to ON, you are allowed to insert a value.

See the docs for an example: http://technet.microsoft.com/en-us/library/ms188059.aspx
0

Featured Post

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.

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