Solved

Copy into table with identity insert

Posted on 2011-02-15
5
568 Views
Last Modified: 2012-05-11
How can I run the code below with identity insert  when I copy from the...BU to live?

delete from proc_cfa.dbo.P_AvailableForSale

Select * into proc_cfa.dbo.P_AvailableForSale
from proc_cfa.dbo.P_AvailableForSaleBU
 
0
Comment
Question by:lrbrister
[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
  • 3
5 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34897868
SET IDENTITY INSERT myTable ON;
insert into myTable values (...);
SET IDENTITY INSERT myTable OFF;
0
 
LVL 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 34897880
oops, _

SET IDENTITY_INSERT myTable ON;
insert into myTable values (...);
SET IDENTITY_INSERT myTable OFF;
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 500 total points
ID: 34897894
you can turn it on only for one table at a time:

http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx
0
 

Author Closing Comment

by:lrbrister
ID: 34897909
Perfect...thanks
0
 
LVL 13

Expert Comment

by:devlab2012
ID: 34897932
Here is the set of queries:

delete from proc_cfa.dbo.P_AvailableForSale


SET IDENTITY_INSERT proc_cfa.dbo.P_AvailableForSale ON

Select * into proc_cfa.dbo.P_AvailableForSale
from proc_cfa.dbo.P_AvailableForSaleBU

SET IDENTITY_INSERT proc_cfa.dbo.P_AvailableForSaleOFF

Note that there is "_" in indetity_insert.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

707 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