Last post Feb 09, 2017 07:12 PM by asp.ambur
Feb 09, 2017 01:07 PM|asp.ambur|LINK
This is my table Data
ID ItemName Qty RefNo
1 A 2 R1
2 B 3 R1
3 C 4 R1
4 D 5 R1
5 AA 1 R2
6 BB 2 R2
7 CC 3 R2
Now i want to Add Z in 3rd row of RefNo: R1
Then my Table Data Will Look Like This
ID ItemName Qty RefNo
1 A 2 R1
2 B 3 R1
3 Z 1 R1
4 C 4 R1
5 D 5 R1
6 AA 1 R2
7 BB 2 R2
8 CC 3 R2
How to do so I need sql insert query
Feb 09, 2017 05:34 PM|kaushalparik27|LINK
Feb 09, 2017 05:41 PM|asp.ambur|LINK
From your post, it looks like you are updating record. Can you provide some more description on what you are facing issue?
Let us consider from the above table RefNo R1 & R2 hold the ID number that should come in sequence, Now client made changes in 3rd row of R1 RefNo he want to put Z item in 3rd row and next C & D should come in 4 and 5 row
How To Insert Z Item in Row number 3..?
I need sql insert query
Feb 09, 2017 05:43 PM|PatriceSc|LINK
Shouldn't ID be a unique ID for your each row? It seems you are trying to use a single column as both a row ID and a ordering column? You could perhaps use a separate ordering column if you need to explicitely have a particular ordering that is not based
for now on any existing column.
Another option might be to use the hiearchyid type:
https://msdn.microsoft.com/en-us/library/bb677290.aspx. For now my understanding is that you handle manually sorted row and want to keep them sorted as done by the user?
Feb 09, 2017 05:46 PM|asp.ambur|LINK
can you give me small insert sql code same like my table foramt
Feb 09, 2017 06:21 PM|jimmy69|LINK
if you want to keep the same result as you mention, i will do this but i'm not an expert
- make a copy of your data in a table B with a INSERT SELECT
- Execute this line TRUNCATE TABLE table name => will be delete all rows and reset the primary key => next record will begin at 1 at primary key
- last action, insert your data and at the row three at your insert Z
hope this help
Feb 09, 2017 06:30 PM|PatriceSc|LINK
You changed your data? If ID is the row ID, I would strongly suggest to never change this value and use a separate column. If the table is quite short my first move would be :
DECLARE @At INT
DECLARE @Ordering INT
SET @At=3 -- Will insert at this "ID"
SELECT @Ordering=Ordering FROM t WHERE ID=@At -- Ordering value for this ID
-- Make room (push all rows after the insertion point forward)
UPDATE t SET Ordering=Ordering+1 WHERE Ordering>=@Ordering
-- INSERT at this position
INSERT INTO t VALUES ('Z',@Ordering)
SELECT * FROM t ORDER BY Ordering
that is using an explicit ordering column and so when inserting a new row I would make some "room" for it before inserting the row. It shows :
ID Name Ordering
1 A 1
2 B 2
5 Z 3
3 C 4
4 D 5
If I have too many rows, I would move to a more complex scheme to avoid having to renum other rows (for example using a real and computing (2+3)/2=2.5 to insert the row with an ordering value which is between the two rows "Inside" which I want to insert
my new row.
SQL tables are not ordered so if you want to handle a manual sort order, it needs to be an additional column. Then it is likely a complexity/need tradeoff ie use something as simple as possible depending on how many rows you expect to finally have in this
Feb 09, 2017 06:53 PM|limno|LINK
From your question, you are missing some knowledge about a relational database table.
Your table need a key and data in table don't have predefined order.
When you insert data into a table there is no guarantee the order inside a table.
You need an ORDER BY to control the order when you retrieve your data from your table with a SELECT statement.
You can start some basic learning on SQL and table design.
Here is a good resource from w3schools.com.
Hope you can learn SQL basics while you are working on your queries.
Feb 09, 2017 07:12 PM|asp.ambur|LINK
I'm Really Thanks To You For Your Query.. This What I Need..
Thanks Again.. Thanks Lot