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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-09-10 : 08:05:43
|
| Christina writes "I am not sure the best method of assigning a batchid to set of records that are inserted into a table. What I am trying to accomplish is to have a stored procedure execute on a daily basis and associte with those records a batchid. That way I can determine what batch the records were loaded. Thanks for any and all comment and answers." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-10 : 08:48:16
|
| Create a table of "Batches".Have the primary key be BatchID, an IDENTITY.Then, include other columns as needed to identify this batch -- who submitted, the date/time, any other info you can get. Some SQL Server variable like USER() and GetDAte() might be useful for this part.Then, when a batch of rows is added to the transaction table, first add a row to the "Batches" table.When you add a row, return the @@IDENTITY from the insert.Then, stamp the added rows with the BatchID from the main Batches table.You can do this in a trigger as well: after rows are inserted, add the row to the "Batches" table, and then perform an UPDATE on the inserted data to update the BatchID of each row to be equal to the newely added BatchID to the batches table.this gives you the traditional header/detail relationship between the "Batches" table and the rows that make up each batch.- Jeff |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-10 : 08:50:31
|
| insert into table1 (batchid, datacol1, datacol2, datacol3, etc)select @batchidpassedin, sourcecol1, sourcecol2, sourcecol3 ,etc from sourcedatatableshould be close to what you want....@batchidpassedin is a variable passed into (or calculated in ) your stored-procedure.sourcedatatable is a temporary staging table for your data pending inserting into your true table.you can also use DTS or BSP...both of which products (i think) can accept (and use) a parameter structure like @batchidpassedin |
|
|
|
|
|
|
|
|
BatchID