Alternative for a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Alternative for a query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-27 : 05:38:29

I have table with possible 100 to million of record

i have to create a temp result set and then insert it into a table.
In the result set i have to generate a unique Id (RowId) for each row in that result set.Currently i have following query.Please suggest any improvement in it

SELECT DISTINCT cqt.fkJobID,
cqt.fkContainerID,
RowId = RIGHT('00000000' + Ltrim(Str(Row_number() OVER (ORDER BY cqt.fkJobID, cqt.fkContainerID, cqt.cqtCQTDatabaseID))), 8),
cqt.cqtCQTDatabaseID
INTO #CQTIDs
FROM ContainerQuantityRecord cqt WITH (nolock)
INNER JOIN #CSMIDs csmS
ON cqt.fkJobID = csmS.fkJobID
AND csmS.csmContainerID = cqt.fkContainerID

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-27 : 05:52:36

basically i need improvement in
RIGHT('00000000' + Ltrim(Str(Row_number() OVER (ORDER BY cqt.fkJobID, cqt.fkContainerID, cqt.cqtCQTDatabaseID))), 8),

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-27 : 06:45:01

Got solution

CREATE TABLE #TableV1
(
Id int IDENTITY(1,1) NOT NULL,
col1 as (right('0000' + convert(varchar,id), 4) ),
col2 nvarchar(10),
col3 nvarchar(10)
)

insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'
insert into #TableV1 select 'NAME1','ADDRESS'
insert into #TableV1 select 'NAME2','ADDRESS'

select * from #TableV1

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 06:50:30

see article on this below

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-27 : 12:39:43

last one is good but i doesn't wanted to use even temp table like

INSERT INTO PieceDetailRecord
(fkJobID,
pdrPieceID,
fkCQTDatabaseID,
fkPackageID,
pdrPDRRecordStatus,
)
SELECT @MAJobID,
RowId = RIGHT('0000000000000000000000' + Ltrim(Str(Row_number() OVER (ORDER BY pdr.fkJobID))), 22),
cqtS.RowId,
pqtS.RowId,
pdrPDRRecordStatus
FROM PieceDetailRecord pdr WITH (nolock)
INNER JOIN #PQTIDs pqtS
ON pqtS.fkJobID = pdr.fkJobID
AND pqtS.fkCQTDatabaseID = pdr.fkCQTDatabaseID
And pqtS.pqtPackageID = pdr.fkPackageID
INNER JOIN #CQTIDs cqtS
ON cqtS.fkJobID = pqtS.fkJobID
And cqtS.cqtCQTDatabaseID = pqtS.fkCQTDatabaseID
WHERE pdrIsDeleted = 0
AND pdrPDRRecordStatus <> 'D'

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 12:47:28

whats the PK of destination table (PieceDetailRecord) ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-28 : 01:24:14

fkJobID,
fkCQTDatabaseID,
fkPackageID,
pdrPieceID,

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 01:43:23

will RowId values from both tables vary eachtime? else how do you ensure uniqueness?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-28 : 02:29:34

@MAJobID,
cqtS.RowId,
pqtS.RowId,

will vary

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:30:47

ok...then it should be ok

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-12-28 : 04:16:38

suppose i don't wanted to use temp table then what can i do?

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 07:19:29

use the logic based on row_number so that it generates id along with query itself without need of insertion into a table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources