Solved

Sql Server column vakue insert

Posted on 2011-10-01
10
183 Views
Last Modified: 2012-08-13
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....
0
Comment
Question by:ube100
  • 6
  • 4
10 Comments
 
LVL 6

Accepted Solution

by:
billfusion earned 250 total points
ID: 36897214
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.
0
 

Author Comment

by:ube100
ID: 36897523
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?
0
 
LVL 6

Expert Comment

by:billfusion
ID: 36897836
In that case triggers may be more appropriate.  You can find a short description about creating  triggers here:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Expert Comment

by:billfusion
ID: 36897839
A better introduction about triggers can be found here:
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
0
 
LVL 6

Expert Comment

by:billfusion
ID: 36897842
Can you include a sample of the code that you're trying to get working?
0
 
LVL 6

Expert Comment

by:billfusion
ID: 36897851
The last comment was an accident.  Please disregard it.
0
 

Author Comment

by:ube100
ID: 36897896
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?
0
 
LVL 6

Expert Comment

by:billfusion
ID: 36898329
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?
0
 

Author Comment

by:ube100
ID: 36900365
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.
 
0
 

Author Closing Comment

by:ube100
ID: 37337670
That didn't help solve my issue.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 59
SHOWPLAN permission denied in database 'AdventureWorks'. 13 111
Help with SQL joins 9 54
get most recent and second most recent date in SQL Server 24 97
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question