Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rowversion Duplication Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 10:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
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
Post #1407023
Posted Tuesday, January 15, 2013 4:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 1,976, Visits: 3,337
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
Post #1407153
Posted Tuesday, January 15, 2013 4:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #1407166
Posted Tuesday, January 15, 2013 4:48 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 1,464, Visits: 8,287
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;



BrainDonor
Linkedin
Blog Site
Post #1407177
Posted Wednesday, January 16, 2013 1:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
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.
Post #1407652
Posted Wednesday, January 16, 2013 2:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #1407686
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse