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
 General SQL Server Forums
 New to SQL Server Programming
 Displaying row numbers & values in TEMP table

Author  Topic 

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-21 : 23:29:31
I am in the AdventureWorks2012 database. I am creating a temp table to hold row numbers, and in the 2nd column I am inserting the values from a column in the Production.WorkOrder table. Here is my code:

CREATE TABLE #table4 (rowNumber INT IDENTITY(1,1), prod INT)

INSERT INTO #table4 (prod)
SELECT ProductID FROM Production.WorkOrder

SELECT *
FROM #table4


The 1st 5 values for ProductID in Production.WorkOrder are 722,725,726, 729, 730. But my results for the TEMP table are showing as:

rowNumber column = 22849, 22850, 22851, 22852, 22853
prod column = 534, 534, 534, 534, 534

Why aren't the 1st 5 values for the rowNumber column in the TEMP table showing as 1 thru 5? Why aren't the 1st 5 values for the prod column in the TEMP table 722,725,726, 729, 730?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-21 : 23:54:40
How are you getting the first 5 values?
How many rows are in your temp table?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-22 : 00:14:54
How this happened >> rowNumber column = 22849, 22850, 22851, 22852, 22853 ?
rownumber should start with 1, 2, 3, 4, 5 ........

one thing you do..
-- run this script for re-populate data into that table
DELETE FROM #table4;
DBCC CHECKIDENT ('#table4', RESEED, 0);
INSERT INTO #table4
SELECT ProductId FROM Production.WorkOrder;
SELECT * FROM #table4;

Then you can find out the rownumber start with 1.

Secondly, run this select statement (SELECT ProductId FROM Production.WorkOrder; ). Post us the first 5 records






--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:24:22
show us how you're doing population of #TEMP table. Are you applying filters after rownumber generation? if yes then you cant expect it to return rownos 1 to 5.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -