Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2010-03-05 : 17:48:51
|
| I am populating the table ToTable by reading rows from the table FromTable. The table ToTable has a field called TransID, which is a unique key, which gets loaded sequentially as, for example:000000010000000200000003There is a SQL procedure, called sp_NextTrans, which gets run by an EXEC. It goes out to a control table and finds the next sequential TransID to be assigned in the ToTable table, and returns that value. It also bumps up the value in the control table for the next person or process.Here is a representation of FromTable:CUSTID|AMOUNT=============AAAAAA|100.00DDDDDD|200.00EEEEEE|300.00Here is a representation of the resulting ToTable:TRANSID-|CUSTID|AMOUNT00000008|AAAAAA|100.0000000009|DDDDDD|200.0000000010|EEEEEE|300.00My SQL procedure is below. It passes 3 parameters to sp_NextTrans, and sp_NextTrans returns the next transaction ID to be assigned in ToTable.---------------------------------------------- Declare @NextId varchar(8)EXEC sp_NextTrans 'AR', '0', @NextId OUTInsert into ToTable (TransID, CustID, Amount) Select @NextId, CustID, Amount From FromTable----------------------------------------------It works fine if there is only one row in FromTable (TRANSID 00000008) - because sp_NextTrans is executed once prior to the INSERT, which then may process more than one row). But, of course, I need to know how to incorporate the EXEC'ed procedure into the INSERT statement, so that, with every row, it goes out and gets the next ID to be assigned from the control file, and bumps it up for the next time. Is it possible to do this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-05 : 20:20:01
|
| To further Tara's sound advice, you can always display the value anyway you please. So if the actual value in the table is 2, no big deal to let your applications or reports display 00000002.Also, you're going to find that your TRANSIDs don't sort properly being varchar without casting them. Making them integers solves this too. |
 |
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2010-03-05 : 20:51:26
|
| will uniqueidentifier work instead of the sequential tran id if you just want it to be unique. Check GUID. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-06 : 01:44:18
|
| for uniqueidentifier to be sequential you need to use NEWSEQUENTIALID() function and make a default on that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2010-03-06 : 11:48:46
|
| The problem I see with the angle of your responses is that you assume I have some control over any of this - I don't. I'm working with existing tables and an existing mechanism for writing rows out with a unique key. In other words, I'm dealing with "packaged software". But your responses are very helpful in that they tell me there isn't some easy and obvious way to just place the EXEC within the INSERT statement. That's exactly what I wanted to know. Now, I can pursue an alternative for accomplishing the same thing. |
|
|
|
|
|
|
|
|
Question About EXEC Within an INSERT