[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL copying tables from one database to another

Posted on 2014-04-24
4
Medium Priority
?
158 Views
Last Modified: 2014-04-24
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
Comment
Question by:urthrilled
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 501 total points
ID: 40021464
use the following method:

SET IDENTITY_INSERT <myTable> ON
insert into <myTable>...
SET IDENTITY_INSERT <myTable> OFF
0
 
LVL 4

Author Comment

by:urthrilled
ID: 40021474
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
 
LVL 13

Assisted Solution

by:magarity
magarity earned 498 total points
ID: 40021571
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
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 501 total points
ID: 40021573
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question