SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 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

547 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
2202 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
52309 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000