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.
| Author |
Topic |
|
Ramm10
Starting Member
2 Posts |
 Posted - 2014-08-11 : 05:59:50
|
| Hi All,I am inserting into a temp table by selecting few columns from INFORMATION_SCHEMA.COLUMNS. I am selecting COLUMN_NAME AND DATA_TYPE. The columns are inserting into temp table, but the order is not the order I gave, but it ordered as per the ORDINAL_POSITION.How to do it as per the order of columns given.INSERT INTO #tmpInputList (InputName,InputType) SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TypeLog' AND COLUMN_NAME IN ('UID','StartingDate', 'EndingDate', 'UploadDate', 'UserName' )As this other column of temp table gets value from Sp, the order is mismatching and incorrect value is taken.Please guide meThanksRamm |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-08-11 : 07:43:13
|
You need to use the ORDER BY to guarantee the sequence of records return from the SELECT query.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-11 : 08:14:01
|
AS kthan wrote, "You need to use the ORDER BY to guarantee the sequence of records return from the SELECT query." However, that might not be enough. Let's step back a bit.First off, there is no concept of ordering in a table (temporary or otherwise) unless it has a clustered index. Does your temp table have a clustered index and if so, on what column(s)? If your temp table has no CI defined then your table is organized as a heap and the ORDER BY clause suggested by khtan will not be considered by SQL when inserting the records. SQL will insert them as it pleases, usually wherever the new rows will fit.Second, how do you see what order SQL stores the rows? Is it by viewing the output of select * from #tmpInpuytList ? Note that if there is no ORDER BY clause on the above statement, SQL is free to return the rows as it sees fit, usually in the most efficient manner, which has nothing to do with any ordering of the data.Third, what do you mean by "column of temp table gets value from Sp"? And, how do you know that "the order is mismatching"? to mean this implies that you expect to join the two results (one from your query and the other from the SP) somehow, but how? You cannot do it based on any assumed ordering; you need to be explicit. To go further, please post:1. CREATE TABLE statement for #tmpInputList 2. The result of:SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TypeLog'AND COLUMN_NAME IN ('UID','StartingDate', 'EndingDate', 'UploadDate', 'UserName' )3. The definition of the Stored Procedure used to get the other column.4. The results you are getting now5. The results you want to get. |
|
|
|
|
|
|
|
|
how to avoid inserting a table by Ordinal Position