Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
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
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
by Mark Wills
PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.