SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL insert auto insert with stop value


SQL insert auto insert with stop value

Author
Message
ShawnBryan
ShawnBryan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 300
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

sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75716 Visits: 6361
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)
Smile Smile Smile
Health & Nutrition
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)SSC Guru (195K reputation)

Group: General Forum Members
Points: 195501 Visits: 23766
ShawnBryan - Tuesday, February 27, 2018 1:40 PM
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

Why are you using the NVARCHAR() datatype for numeric columns?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
curtw
curtw
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 19
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.
ShawnBryan
ShawnBryan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 300
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

sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75716 Visits: 6361
ShawnBryan - Wednesday, February 28, 2018 3:48 AM
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

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)
Smile Smile Smile
Health & Nutrition
ShawnBryan
ShawnBryan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 300
Hi sgmunson...thanks for your feedback. ..is it possible to share any codings which is PARTITION BY OVER clause would be great...
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75716 Visits: 6361
ShawnBryan - Wednesday, February 28, 2018 11:47 AM
Hi sgmunson...thanks for your feedback. ..is it possible to share any codings which is PARTITION BY OVER clause would be great...

See the syntax for that function here: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
ShawnBryan
ShawnBryan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 300
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]) n

Thanks
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75716 Visits: 6361
ShawnBryan - Wednesday, February 28, 2018 12:10 PM
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]) n

Thanks

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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search