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
Solved

SQL copying tables from one database to another

Posted on 2014-04-24
4
152 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
4 Comments
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 167 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 166 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 22

Assisted Solution

by:Steve Wales
Steve Wales earned 167 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

792 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