August 2, 2008 at 1:59 am
Hi Guys,
I have a file with comma delimitted. I want to insert into table with ascending order. Please see the file below
41;1;M ;;;;400000;1;080707;080825;;;
51;Y;080707;1;2010;30;BEBR00760000;400000;NM;;;;;;;;;;;;;;1;;;;
42;DODGERS, VARIOUS;
41;1; T ;;;;400000;1;080707;080825;;;
51;Y;080722;2;1836;30;BEBR00760000;400000;NM;;;;;;;;;;;;;;2;;;;
42;DODGERS, VARIOUS;
41;2; T ;;;;0;1;080701;080701;;;
51;Y;080701;2;1927;30;BEBR00760000;0;NM;;;;;;;;;;;;;;1;;;;
42;DODGERS, VARIOUS;
41;3;MTWTFSS;;;;0;10;080630;080706;;;
51;Y;080630;1;0102;30;BEBR00760000;0;NM;;;;;;;;;;;;;;9;;;;
51;Y;080630;1;2234;30;BEBR00780000;0;NM;;;;;;;;;;;;;;4;;;;
51;Y;080630;1;0600;30;BEBR00840000;0;NM;;;;;;;;;;;;;;1;;;;
51;Y;080630;1;1658;30;BEBR00760000;0;NM;;;;;;;;;;;;;;10;;;;
51;Y;080630;1;2356;30;BEBR00760000;0;NM;;;;;;;;;;;;;;8;;;;
42;ROS, 6A-6A;
I was able to insert into table as it is.right now it is inserting like rows 41,51,42. But i want to insert like
rows 41,42,51.
I tried ascending order but it is inserting all in the ascending order. there are 3 groups in this file which starts with 41,51,42 and again 41,51,42 and so on. i want in the same order but i want 42nd row to be inserted after 41. Can you guys give me some idea?
Thanks
August 2, 2008 at 8:12 am
SQL Server doesn't care about insertion order. As an RDBMS, it does not order rows.
You insert the rows as you find them and then when you retrieve them, you add an ORDER BY clause to ensure sorting.
August 2, 2008 at 9:51 am
I agree with Steve... doesn't matter what the insert order is, you should use ORDER BY in your queries and NOT rely on a physical order. You can force a logical processing order by putting a clustered index on the column(s), though... and that may be what you're looking for.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2008 at 12:01 pm
Hi,
I am inserting this file into a staging table called Stage1 and it is in the format. C1,C2 and C3 are column Headers.
C1 C2 C3
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
it is inserting in the staging table like this. Now from this staging table i want to insert into another temp table in the same order but i want to insert row42 after row41. ex: 41,42,51 and again 41,42,51 and so on. If this is the case how do i query to insert into another table
August 2, 2008 at 2:54 pm
Ummm... you're missing the point a bit. The order in which something is inserted doesn't really matter in an RDBMS. Why do you think you need a "natural order" in a table? Not busting your chops... just trying to figure out what the end result of what you're trying to do will be. Perhaps if you told us why the natural storage order is so important.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2008 at 6:46 pm
Of course, the order problem can be easily remedied if you just add an Identity column. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 2, 2008 at 9:27 pm
Hi,
I can add identity column, Can you tell me how to sort order by. if possible can you show me the query ?
Thanks
August 2, 2008 at 10:31 pm
rbarryyoung (8/2/2008)
Of course, the order problem can be easily remedied if you just add an Identity column. 🙂
I don't think it will, especially if parallelism kicks in. If you use OPTION (MAXDOP 1), it will number them correctly, but it won't guarantee the physical order of the data as it's stored in the table.
The only thing that comes close is what I said before... putting a clustered index on the column(s) you want the data to be ordered by. From Books Online...
[font="Arial Black"]Using Clustered Indexes[/font]
A clustered index determines the physical order of data in a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2008 at 10:46 pm
vkres1,
Forget about the identity column... if you want to store the data in the correct physical order, then put a clustered index on the column(s) that you want the data to be sorted by. But understand this... that does NOT necessarily guarantee a return order in a SELECT... it only guarantees the order of processing only if that index "kicks in" or is forced to activate using an index hint in a query.
Here's the proof of that... read the comments in the code and read the following article... I got the code below from the "FINAL Test Data Configuration" section of that article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--===== Create and populate a 1,000,000 row test table 100 rows at a time to
-- simulate inserting multiple sets of rows.
-- Column "RowNum" is an IDENTITY and has a range of 1 to 1,000,000 unique numbers
-- Column "AccountID" has a range of 1 to 50,000 non-unique numbers
-- Column "Amount has a range of 0.0000 to +/- 99.9900 non-unique numbers
-- Column "Date" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
--===== If the test table already exists, drop it
SET NOCOUNT ON
IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
DROP TABLE dbo.JBMTest
GO
--===== Create the test table
CREATE TABLE dbo.JBMTest
(
RowNum INT IDENTITY (1,1) NOT NULL,
AccountID INT NULL,
Amount MONEY NULL,
Date DATETIME NULL,
RunBal MONEY NULL,
GrpBal MONEY NULL,
RunCnt INT NULL,
GrpCnt INT NULL
)
--===== Add the primary key
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY NONCLUSTERED (RowNum) --nonclustered to resolve "Merry-go-Round"
--===== Add the "sorting index" to the table
CREATE CLUSTERED INDEX IX_JBMTest_AccountID_Date --clustered to resolve "Merry-go-Round"
ON dbo.JBMTest (AccountID, Date)
--===== Build the table 100 rows at a time to "mix things up"
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter < 1000000
BEGIN
--===== Add 1000 rows to the test table
INSERT INTO dbo.JBMTest
(AccountID, Amount, Date)
SELECT TOP 100
AccountID = ABS(CHECKSUM(NEWID()))%50000+1,
Amount = CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY),
Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Increment the counter
SET @Counter = @Counter + 100
END
GO
--===== Demonstrate that the "Merry-go-Round" symptoms don't apply to the CLUSTERED index
SELECT *
FROM dbo.JBMTest WITH (INDEX(IX_JBMTest_AccountID_Date)) --CLUSTERED Index resolves "Merry-go-Round"
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2008 at 11:03 pm
Jeff,
rbarryyoung is telling it is eaier with identity column. Do you know how to figure out with
identity column?looks like clustered index is not guaranteed as you said. any idea
Thx
August 3, 2008 at 1:13 am
Actually, after re-reading your original post, I think that I misunderstood what you were asking for and that Jeff is right.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2008 at 9:12 am
vkres1 (8/2/2008)
Jeff,rbarryyoung is telling it is eaier with identity column. Do you know how to figure out with
identity column?looks like clustered index is not guaranteed as you said. any idea
Thx
rbarryyoung (8/3/2008)
Actually, after re-reading your original post, I think that I misunderstood what you were asking for and that Jeff is right.
Like I said, add a clustered index... it's the closest thing to want you want to achieve. If you use and INDEX hint for the clustered index like I did in my example code, it will force the order even if the optimizer decides that NOT using things in order may be better.
To be clear... there is nothing that absolutely guarantees the "display" order of data in an RDBMS other than an ORDER BY... you can use a clustered index force the logical processing order and that usually (but not always) includes the "display" order... but there are some unpredictable exceptions. You can prevent one of those exceptions by using the OPTION (MAXDOP 1) option in the query... of course, that may make your query run slower if parallelism turns out to be a good thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2008 at 6:46 pm
Jeff,
I know you are every where. I am just catching up some ideas to post to different forums. some body was telling to try this query, I haven't tried yet. see below. Do you think this will work?
SELECT ID=identity(int,1,1), [Name], Color, ListPrice
INTO #NewTempTable
FROM AdventureWorks.Production.Product
ORDER BY Color, ListPrice desc
SELECT * from #NewTempTable
August 3, 2008 at 7:16 pm
vkres1 (8/3/2008)
Jeff,I know you are every where. I am just catching up some ideas to post to different forums. some body was telling to try this query, I haven't tried yet. see below. Do you think this will work?
SELECT ID=identity(int,1,1), [Name], Color, ListPrice
INTO #NewTempTable
FROM AdventureWorks.Production.Product
ORDER BY Color, ListPrice desc
SELECT * from #NewTempTable
It will work but it won't guarantee the order of records when you run
SELECT * FROM #NewTempTable
Especially if some data processing done on #NewTempTable.
Anyway on the final SELECT you need to specify order of records explicitly:
SELECT *
FROM #NewTempTable
ORDER BY ID
BTW, "SELECT .. INTO ... " is one of practices listed as "the worst".
Don't use it much.
_____________
Code for TallyGenerator
August 3, 2008 at 7:22 pm
hi Sergiy,
i know order by. but this is not i want. one of the staging table has the data like this, see below
C1 C2 C3
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
i want to insert this data into another temptable like this. see below
C1 C2 C3
41 te cc
42 te cc
51 te cc
41 te cc
42 te cc
51 te cc
41 te cc
42 te cc
51 te cc
appreciate your help on this
Thx
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply