Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sql Server column vakue insert
Posted on 2011-10-01
Hi,
In my sql server table these are the columns:
ID CategoryID Description code
Q101 cat1 VLC - 001 Example1 VLC - 001
Q102 cat1 VLC - 002 Example1 VLC - 002
Q103 cat2 PAY - 001 Example1 PAY - 001
When the user input data into this table:
(1) I wanted to generate this code depending on the CategoryID i.e. For Cat1 VLC - 001, VLC -002 and so forth
(2) For each row for the description I need to prefix the respective code for each row they insert into table.
This data input not through a application or anything like that but they will do it in the management studio so I wanted to do this in table level in the sql server. Can I create a function and attach to this column so every time the user add a new row and will use this function to generate next available code for that row and also prefix this description as well.
Any help would very greatly appreciated....
In my sql server table these are the columns:
ID CategoryID Description code
Q101 cat1 VLC - 001 Example1 VLC - 001
Q102 cat1 VLC - 002 Example1 VLC - 002
Q103 cat2 PAY - 001 Example1 PAY - 001
When the user input data into this table:
(1) I wanted to generate this code depending on the CategoryID i.e. For Cat1 VLC - 001, VLC -002 and so forth
(2) For each row for the description I need to prefix the respective code for each row they insert into table.
This data input not through a application or anything like that but they will do it in the management studio so I wanted to do this in table level in the sql server. Can I create a function and attach to this column so every time the user add a new row and will use this function to generate next available code for that row and also prefix this description as well.
Any help would very greatly appreciated....
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
4
10 Comments
The easiest would be to only allow the categoryID to be entered by the user then use calculated values to insert into the rest of the columns. Your best bet is to use a stored procedure that you pass it the ID and category id. The procedure then sets up the proper strings either based on a lookup table, if you have many category type, or hard coded in the procedure. The procedure then insert the proper values in the table. The stored procedure also need to lookup the max number of records already in a table to find the correct value to append.
Calculate the Code first, then use the code to enter into the description.
Calculate the Code first, then use the code to enter into the description.
This is not from a application but this is rather from a data entry by an admin staff using sql server management studio so can I still be able to call the stored procedure? If yes then how?
In that case triggers may be more appropriate. You can find a short description about creating triggers here:
http://msdn.microsoft.com/
http://msdn.microsoft.com/
A better introduction about triggers can be found here:
http://www.sqlteam.com/art
http://www.sqlteam.com/art
These are the two tables in question:
CREATE TABLE [sysdba].[CATEGORY](
[CATEGORYID] [char](12) NOT NULL,
[CREATEUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYUSER] [char](12) NULL,
[MODIFYDATE] [datetime] NULL,
[CATEGORYNAME] [varchar](55) NULL
) ON [PRIMARY]
CREATE TABLE [sysdba].[CALLOUTCOMES](
[CALLOUTCOMESID] [char](12) NOT NULL,
[CATEGORYID] [char](12) NOT NULL,
[CREATEUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYUSER] [char](12) NULL,
[MODIFYDATE] [datetime] NULL,
[OUTCOMES] [varchar](80) NULL,
[CALLOUTCOMECODE] [varchar](32) NULL
) ON [PRIMARY]
When they adding a new row to CALLOUTCOMES table they will use the categoryid from the category table and depending on the category we need to insert a appropriate code for CALLOUTCOMECODE column and then append that code as a prefix for the description which user entered. i.e. VLC - 001 Example1 VLC - 001
If we have to do this via a trigger then how we go about it please?
CREATE TABLE [sysdba].[CATEGORY](
[CATEGORYID] [char](12) NOT NULL,
[CREATEUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYUSER] [char](12) NULL,
[MODIFYDATE] [datetime] NULL,
[CATEGORYNAME] [varchar](55) NULL
) ON [PRIMARY]
CREATE TABLE [sysdba].[CALLOUTCOMES](
[CALLOUTCOMESID] [char](12) NOT NULL,
[CATEGORYID] [char](12) NOT NULL,
[CREATEUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYUSER] [char](12) NULL,
[MODIFYDATE] [datetime] NULL,
[OUTCOMES] [varchar](80) NULL,
[CALLOUTCOMECODE] [varchar](32) NULL
) ON [PRIMARY]
When they adding a new row to CALLOUTCOMES table they will use the categoryid from the category table and depending on the category we need to insert a appropriate code for CALLOUTCOMECODE column and then append that code as a prefix for the description which user entered. i.e. VLC - 001 Example1 VLC - 001
If we have to do this via a trigger then how we go about it please?
Can you list some row from Category, and what the user will enter in calloutcome? is the value VLC-001 retrieved from category? Or is it part of that code that's retrieved from category?
Category:
Q6UJ9A0RCBTK VLC Inquires
Q6UJ9A0RCBTL Payments
Calloutcomes:
Q6UJ9A0RCBU9 Q6UJ9A0RCBTK U6UJ9A0000AF 2011-09-12 12:07:17.473 U6UJ9A0000AF 2011-09-12 12:07:17.473 VLC - 001 Navigation of te website VLC - 001
Q6UJ9A0RCBUR Q6UJ9A0RCBTL U6UJ9A0000AF 2011-09-12 12:38:21.020 U6UJ9A0000AF 2011-09-12 12:38:21.020 PAY - 001 Monthly instalment paid PAY - 001
VLC will represent VLC Inquiry category and anything come under that category will be VLC -001 for the first one and VLC - 002 for the 2nd one and so forth.
Q6UJ9A0RCBTK VLC Inquires
Q6UJ9A0RCBTL Payments
Calloutcomes:
Q6UJ9A0RCBU9 Q6UJ9A0RCBTK U6UJ9A0000AF 2011-09-12 12:07:17.473 U6UJ9A0000AF 2011-09-12 12:07:17.473 VLC - 001 Navigation of te website VLC - 001
Q6UJ9A0RCBUR Q6UJ9A0RCBTL U6UJ9A0000AF 2011-09-12 12:38:21.020 U6UJ9A0000AF 2011-09-12 12:38:21.020 PAY - 001 Monthly instalment paid PAY - 001
VLC will represent VLC Inquiry category and anything come under that category will be VLC -001 for the first one and VLC - 002 for the 2nd one and so forth.
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article will describe one method to parse a delimited string into a table of data.
Why would I do that you ask? Let's say that you need to pass multiple parameters into a stored procedure to search for. For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data.
I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 12 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.