Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rowversion Duplication


Rowversion Duplication

Author
Message
Krishna1
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 556
Hi All

Most off our tables have rowversion columns. We whant this row version to be unique through out the DB. But as per msdn site it syas that rowverison can get deuplicated if "SELECT INTO " command is used?

Is there any way that we can use "SELECT INTO" and still avaoid rowversion dupllication?

Regards
Shilpa
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2859 Visits: 5320
It's only duplicated if the rowversion column is included in the column list of the select into statement.
Don't do that, and your values will stay unique.

Cheers
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
shilpaprele (1/14/2013)
Is there any way that we can use "SELECT INTO" and still avaoid rowversion dupllication?
YEs you can try like


select "all coumns except rowversion column " into newtable from oldtable 


then

alter table newtable add rowverison column 



The above is just code hint not the actual sql code

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2016 Visits: 11174
SELECT INTO creates the target table, and the only exception along the lines that you are after is with the IDENTITY column.
You could leave the Rowversion column out, as already suggested but we don't know the size of the tables concerned so altering the schema may be a large overhead.

Why not create the target table first and then use INSERT INTO? That will allow what you require.

CREATE TABLE Test1(
   Col1 rowversion,
   col2   VARCHAR(20)
   Wink;

CREATE TABLE Test2(
   Col1 rowversion,
   col2   VARCHAR(20)
   Wink;

INSERT INTO Test1(col2)
VALUES( 'test1'),( 'test2')

INSERT INTO test2(col2) SELECT Col2 FROM Test1

SELECT * FROM test1;
SELECT * FROM test2;



Steve Hall
Linkedin
Blog Site
Krishna1
Krishna1
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 556
Thanks all for your replies.
I agree that we should exclude row version column but there my be many developers working on the DB. And if sombody uses the select into then we will have dupliction.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
shilpaprele (1/16/2013)
there may be many developers working on the DB. And if sombody uses the select into then we will have dupliction.
and this cant be get restricted or stopped , so you should make them understand that why and what needs to follow ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search