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 |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-03-05 : 10:13:37
|
| I'll try not to make this more complicated, but here it is:"my way" of doing data conversion (and i know it's not the "best" way), is, when i recieve an excel sheet from a client, that they've ostensibly exported from some other db app in most cases, i drop that data into an excel table and begin massaging the data according to the corresponding tables in our db model that the data will fall. Because i'm not yet skilled enought to completely script all of the 'massaging' in a query, what i do is build Views based on the design of the destination tables pulling data from my source table and aliasing it accordingly. I can then do a select * into the destination table from the View, thereby getting the data from the source table split off according to the constructs of each View. If i have a conversion that is destined for only ONE table in ONE db, i will typically not do this. In those cases, i will simply build an insert query using the data from the excel sheet and insert it directly into the destination table in sql. One field that is unique to each of our tables is the Uniquekey which is generated through a function we use that creates a 22 character unique identifier based off of a bunch of voodoo calculations incorporating the MAC id of the PC and the GUID, etc., etc. Bottom line is the function never generates a duplicate key.In the latter conversion scenario, how i generate this uniquekey from the function via the insert line is simple:INSERT INTO GCSOCONV.dbo.Arrest ( LNAME, FNAME, MNAME, UNIQUEKEY )VALUES ( 'JOHN', 'DOE', 'J', SCADMIN.dbo.fnsc_GetUniqueID(NEWID()), );Simply by including the function in the insert without quotes.When i am going to do a select * into from my views into the destination tables, the problem is that the uniquekey is already generated in the source table. I cannot pull that uniquekey in to the destination tables because it will result in duplicate keys which are not allowed in the structure of our db's (i.e., FNAME appears in many tables across many db's, but each instance has a different uniquekey value)If, in my source table, i have the uniquekey column hold the text of the function (SCADMIN.dbo.fnsc_GetUniqueID(NEWID()), as opposed to it's generated value, how do i write my select * into statement to pull that value from the Uniquekey field in the view as the function command and not as a string?note that, in my source table, i do not need the unique key VALUE to reside in that column, i can instead insert the text of the function. It's just an issue of how i pull the function from that column instead of the string value during a select * into line.hopefully i've made something resembling 'sense'. |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-05 : 11:06:08
|
| Currently, my workaround is to make the uniquekey value either null or an empty string in the source table then run an update for the respective tables to set the value of uniquekey according to the function, i.e.,:UPDATE mnitest SET uniquekey=SCADMIN.dbo.fnsc_GetUniqueID(NEWID()) |
 |
|
|
|
|
|
|
|
Fire off a function within an insert