SQL insert auto insert with stop value
-
Experts,
Sql need to add automatically the numbers with sequence .
DDL is,
USE [d]
GO
/****** Object: Table [dbo].[t4] Script Date: 2/27/2018 11:25:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t4](
[id] [int] IDENTITY(1,1) NOT NULL,
[twenty] [nvarchar](50) NULL,
[ten] [nvarchar](50) NULL,
[five] [nvarchar](50) NULL,
[one] [nvarchar](50) NULL,
[five_hundred_fils] [nvarchar](50) NULL,
[hundred_fils] [nvarchar](50) NULL,
[fifty_fils] [nvarchar](50) NULL,
[twenty_five_fils] [nvarchar](50) NULL,
[ten_fils] [nvarchar](50) NULL,
[five_fils] [nvarchar](50) NULL,
[time] [datetime] NULL,
[total] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[five_hundred_fils2] [nvarchar](50) NULL,
[sar] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]DML is.
GO
SET IDENTITY_INSERT [dbo].[t4] ON
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (1, N'240', N'0', N'0', N'0', N'0.000', N'0.000', N'0.000', N'0.000', N'0.000', N'0.000', CAST(N'2018-02-27T23:13:33.900' AS DateTime), N'252.000', N'Maria', N'0.000', N'1')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (2, N'240', N'120', N'0', N'0', N'0', N'0', N'0', N'0', N'0', N'0', CAST(N'2018-02-27T23:14:06.633' AS DateTime), N'360.000', N'Maria', N'0', N'2')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (3, N'240', N'0', N'', N'', N'', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:10.643' AS DateTime), N'240.000', N'Mark', N'', N'1')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (4, N'240', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:17.890' AS DateTime), N'240.000', N'Mark', N'', N'2')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (5, N'0', N'', N'', N'', N'6.000', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:24.617' AS DateTime), N'6.000', N'Maria', N'', N'3')
GO
SET IDENTITY_INSERT [dbo].[t4] OFF
GO
expected output .i would like to add auto number sequence upon each insert. example first name Maria sar is 1 and second name maria sar is 2 and third name mark sar is 1 and fourth name mark sar is 2 and fifth name maria sar is 3. this should happen automatically. can you help
-
1.) What do you plan to do if you ever hire a second person with the same first name?
2.) This isn't typical of how you design databases. There should be a separate table for the names of the people involved, and a foreign key relationship should exist between the base table and the table of names and other related values. As to getting a sequence of numbers for the various rows, it's usually best to use a datetime column to represent the order in which the values were inserted, and then use the ROW_NUMBER() function to derive a numeric ordering whenever it's needed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - ShawnBryan - Tuesday, February 27, 2018 1:40 PMExperts,
Sql need to add automatically the numbers with sequence .
DDL is,
USE [d]
GO
/****** Object: Table [dbo].[t4] Script Date: 2/27/2018 11:25:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t4](
[id] [int] IDENTITY(1,1) NOT NULL,
[twenty] [nvarchar](50) NULL,
[ten] [nvarchar](50) NULL,
[five] [nvarchar](50) NULL,
[one] [nvarchar](50) NULL,
[five_hundred_fils] [nvarchar](50) NULL,
[hundred_fils] [nvarchar](50) NULL,
[fifty_fils] [nvarchar](50) NULL,
[twenty_five_fils] [nvarchar](50) NULL,
[ten_fils] [nvarchar](50) NULL,
[five_fils] [nvarchar](50) NULL,
[time] [datetime] NULL,
[total] [nvarchar](50) NULL,
[name] [nvarchar](50) NULL,
[five_hundred_fils2] [nvarchar](50) NULL,
[sar] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]DML is.
GO
SET IDENTITY_INSERT [dbo].[t4] ON
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (1, N'240', N'0', N'0', N'0', N'0.000', N'0.000', N'0.000', N'0.000', N'0.000', N'0.000', CAST(N'2018-02-27T23:13:33.900' AS DateTime), N'252.000', N'Maria', N'0.000', N'1')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (2, N'240', N'120', N'0', N'0', N'0', N'0', N'0', N'0', N'0', N'0', CAST(N'2018-02-27T23:14:06.633' AS DateTime), N'360.000', N'Maria', N'0', N'2')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (3, N'240', N'0', N'', N'', N'', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:10.643' AS DateTime), N'240.000', N'Mark', N'', N'1')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (4, N'240', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:17.890' AS DateTime), N'240.000', N'Mark', N'', N'2')
GO
INSERT [dbo].[t4] ([id], [twenty], [ten], [five], [one], [five_hundred_fils], [hundred_fils], [fifty_fils], [twenty_five_fils], [ten_fils], [five_fils], [time], [total], [name], [five_hundred_fils2], [sar]) VALUES (5, N'0', N'', N'', N'', N'6.000', N'', N'', N'', N'', N'', CAST(N'2018-02-27T23:15:24.617' AS DateTime), N'6.000', N'Maria', N'', N'3')
GO
SET IDENTITY_INSERT [dbo].[t4] OFF
GO
expected output .i would like to add auto number sequence upon each insert. example first name Maria sar is 1 and second name maria sar is 2 and third name mark sar is 1 and fourth name mark sar is 2 and fifth name maria sar is 3. this should happen automatically. can you helpWhy are you using the NVARCHAR() datatype for numeric columns?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I concur with other comments, the database design is questionable from several angles.
As far as auto-numbering key values for the names, I feel this business rule might be better served outside the data layer. If you must do this in the database, you could use a stored procedure to insert the rows and apply the logic there. If you will be writing INSERT statements for this data, you could integrate a function to calculate this number. A trigger could calculate the value after insert. But regular "Auto Number" functionality is not designed to apply manipulative logic.
Here is a reason to NOT pursue this logic. What if a user changes their name, and now it matches an existing name. Your "auto number" is no longer unique. If you've used this as a key in any way, now you have an integrity issue. I'd stick with the identity value which will guarantee uniqueness across the entire solution.
-
experts, Thanks for your feedback.
Actually, I am trying to develop DML for cashier with drivers shipment cash entry. Each day driver might have multiple shipments i mean delivery loads. Let say driver maria is having 3 shipments for a day . So when first time maria data inserted in to table then its should auto insert with value (1)and then same driver maria data inserted second time then its should auto insert with value (2) and same driver maria data inserted third time then its should auto insert with value (3.)
Here key identifier for auto insert is name.
And this will keep rotating everyday since same driver will have shipment every day. I mean sunday might have 1 shipment and monday might have 2 shipment its goes on.I am using .Net framework for the same but meanwhile trying to see if anything can be done via SQL too.
A
- ShawnBryan - Wednesday, February 28, 2018 3:48 AM
None of that changes the fact that the design is bad. You would be far better off deriving those numbers based on the specific driver (again, a foreign key value from a separate table of the drivers), and the date/time of the cash entry. You can always easily derive those numbers using the ROW_NUMBER() function using the PARTITION BY clause within the OVER clause, so you can easily run a query that gets you that information any time you want it. It does NOT need to be stored in the database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hi sgmunson...thanks for your feedback. ..is it possible to share any codings which is PARTITION BY OVER clause would be great...
- ShawnBryan - Wednesday, February 28, 2018 11:47 AM
See the syntax for that function here: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hello Steve,
Seems yours code is ok .
SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY name
ORDER BY name DESC) AS StRank, *
FROM [d].[dbo].[t4]) nThanks
- ShawnBryan - Wednesday, February 28, 2018 12:10 PM
You still might benefit from adding an InsertedDate column to that table, so that you can use ORDER BY InsertedDate ASC as the ORDER BY clause in your ROW_NUMBER() function, as that would be more likely to result in the correct order. Using the same column as the partition by will NOT guarantee that you get the order correct.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply