SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rowversion Duplication


Rowversion Duplication

Author
Message
Krishna1
Krishna1
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4052 Visits: 5843
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
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5414 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;-)
Steve Hall
Steve Hall
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: 11573
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)
);

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

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
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 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
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5414 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