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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Displaying row numbers & values in TEMP table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

55 Posts

Posted - 05/21/2013 :  23:29:31  Show Profile  Reply with Quote
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 - 05/21/2013 :  23:54:40  Show Profile  Reply with Quote
How are you getting the first 5 values?
How many rows are in your temp table?

Edited by - MuMu88 on 05/21/2013 23:55:23
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 05/22/2013 :  00:14:54  Show Profile  Reply with Quote
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

Edited by - bandi on 05/22/2013 00:25:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 05/22/2013 :  00:24:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000