Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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....
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
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
- Software
- Query Syntax
- SQL
- Databases
- Microsoft SQL Server 2008
- Microsoft SQL Server, Microsoft SQL Server 2005, *T-SQL
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month11 days, 14 hours left to enroll
606 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.