• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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