Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Closed Thread
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 19th, 2010, 04:26 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default Changing Identity Column value?

I know, I know - normally you wouldn't want to do such a thing, but...

I am trying to copy data from a production database to a development version. Identity columns exist in the production copy and the test version must maintain the exact same identity values when I copy the rows from one table to the other. Not only is there an integrity issue with other tables, but I want to use the Update command to pass an entire table and it requries an identity field.

It seems like the only way to do this is to turn off the Identity specification before the copy, and then turn it back on afterwards. Is there a simple way to do this in VB.NET 2010?

Or is there a better way to do this that will result in exactly duplicate data in both production and test database tables without disconnecting the production database or referencing backups? Both involve our DBA and as this will need to happen routinely during testing, I'd rather do it myself.

Thanks!
  #2 (permalink)  
Old October 20th, 2010, 01:08 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

yes u can simple do it in ur command in VB by SQLCommand
but u should have the role to ALTER tables
__________________
Always,
Hovik Melkomian.
  #3 (permalink)  
Old October 20th, 2010, 02:49 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Quote:
I know, I know - normally you wouldn't want to do such a thing, but
It's not that uncommon, especially not in the scenario you're describing where you import existing data.

The trick is turn on the identity insert, like this:

Code:
 
SET IDENTITY_INSERT TableName ON
From there, you can supply explicit values, like this:

Code:
INSERT INTO TableName(IdColumn, OtherColumn) VALUES (15, 'Test')
Hope this helps,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #4 (permalink)  
Old October 20th, 2010, 01:01 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

That did the trick - thanks to both of you!
Closed Thread


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identity Column mike_remember SQL Server 2000 3 July 9th, 2007 07:12 PM
checking column is either identity or not. g_vamsi_krish SQL Server 2000 2 March 17th, 2006 12:38 PM
identity column rohit_sant SQL Language 4 June 6th, 2005 08:27 AM
Identity column jbenson001 SQL Server 2000 5 April 12th, 2005 02:01 PM



All times are GMT -4. The time now is 01:38 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.