asked
Stored Procedure / Trigger
I hope someone can assist me with the following question: I have a table called _rtblProspect (this is where I enter in prospective customers with a whole lot of user defined fields for additional information). My next table is Client (this is where all my customer information is stored). In the program that I am running, when I convert from a prospective customer to a customer, it does not pull through all the user defined information. Also, once I click on OK and commit, then the prospective customer is no longer in the _rtblProspect table. Is there some sort of stored procedure or trigger that I can setup that when I click on OK, it snapshots that prospective customer and populates the user defined fields in the Client table? Thanks in advance for the assistance.
are the tables structures the same between _rtblProspect and Client?
They have different field names, this is the problem. there is automatically also a prefix in front of the UDF's. In the client table, it will be ucARTEST and in the _rtblProspect table it will be ucPROTEST. I am not worried about the other information between the two tables, just the UDF's. I hope this helps and makes sense?
Unless I'm completely missing the point, wouldn't this simply be an INSERT ... SELECT operation. INSERT defines the columns in the Client table and SELECT defines the columns being pulled from in the _rtblProspect table. There's a sample of this code [here][1] and [here][2]. That the column names are different won't matter at all. All that matters is that the data types are the same or are compatible. [1]:
https://technet.microsoft.com/en-us/library/ms188263(v=sql.105).aspx [2]:
https://msdn.microsoft.com/en-us/library/ms174335.aspx
The problem comes in, the customer can stay a prospective customer for yonks. when you select the convert to customer in the program, and click on ok, that is when I want the insert to run. I have found the current stored procedure for this, but just my luck, it is encrypted, so trying to run decryption on it at the moment. I could possibly just modify that.
You either have to modify that, or supply a different one. A trigger won't work because it's based on modifications made to the data, which is not what you're looking for.
16 People are following this question.
