| Author |
Topic |
|
daheri
Starting Member
9 Posts |
 Posted - 2011-12-15 : 03:12:45
|
| Hi,Im have a case where i basicly have to create a procedure that will take a table name as input, and for that table i will have to iterate row by row. And for each row i will have to iterate column by column and do some updates for each column in each row.The input table to the procedure can have any number of columns and dont neccesarly have a primary key column. What is the best way to approach this case?Dont worry about preformance, as the rows in the input tables will be low.I was thinking about using a cursor to loop through the rows and then an inner cursor to loop through the columns. 1st problem is: I cant really get started because i dont know what column names there will be, and because of that, what the hell do i fetch into in the outer loop? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-15 : 03:24:35
|
| whats the need of this requirement. if would involve lot of dynamic sql involved and also some kind of cursor logic. Can i ask need of this?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
daheri
Starting Member
9 Posts |
Posted - 2011-12-15 : 03:31:20
|
| Yes. You can ask the need. Basicly its because we are building a procedure to import data from a denormalized table we receive from another department.This table we receive is auto-generated from some other software and we will never know for sure exactly what columns there is in this table.So this import-procedure we are building will use a set of extra "Metadata" tables that will hold information on how to map data from the denormalized table and into our database.So basicly we are stuck with looping through this table row by row and then column by column and do our imports on a cell by cell basis |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-15 : 03:34:52
|
quote:
Originally posted by daheri
Yes. You can ask the need. Basicly its because we are building a procedure to import data from a denormalized table we receive from another department.This table we receive is auto-generated from some other software and we will never know for sure exactly what columns there is in this table.So this import-procedure we are building will use a set of extra "Metadata" tables that will hold information on how to map data from the denormalized table and into our database.So basicly we are stuck with looping through this table row by row and then column by column and do our imports on a cell by cell basis
for that why do you need loops?you can make use of catalog view INFORMATION_SCHEMA.COLUMNS in the source db to know what all columns table will contain. i dont know what you're importing but you can generate the columnlist from above view and then use it to generate dynamic import script.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
daheri
Starting Member
9 Posts |
Posted - 2011-12-15 : 03:47:35
|
| Sorry, i might have explained a bit badly.You see, your suggestion is good. Thats the way we did it, until there was added some complex rules.For simplisity lets say we are importing from "Import_Table" to our database table "User_History". PKs: User_FK, ValidFromIn our metadata tables it is described that User_FK is generated by a custom SQL that will join values from the import_table with user-table to find the user_fk. There is also described that if the returned user_fk is null then a procedure should be executed to create a new user.To build a custom dynamic sql based on the information_Schema works fine up until the point where a procedure should be run if the value is missing... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-15 : 05:52:56
|
| thats just a matter of addinf extra control flow check isnt it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
daheri
Starting Member
9 Posts |
Posted - 2011-12-16 : 06:04:01
|
| Im not exactly sure what you mean by that. Would you care to elaborate a bit? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-16 : 06:27:25
|
| can you explain on what condition you want to check before you call the procedure? also what you would be doing inside procedure?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
daheri
Starting Member
9 Posts |
Posted - 2011-12-16 : 07:16:04
|
In its current state, the procedure creates a temp table by joining information_schema and metadata-tables for the source-table and target table. Thus creating a temp table holds all target-columns and the equivilent source-coloumn (or convertSQL) to populate the column.The temp table has the following columns:ID int identity(1,1),COLUMN_NAME nvarchar(254),IS_NULLABLE varchar(3),COLUMN_VALUE nvarchar(1000)Coloumn value is a field that will either point to a colomn in the source table or contain a customSQL to create the value.It then iterates throught the temp table to create an insert-statement that can be executed.The column_value could for instance be (if customSQL):(select customer_PK from customer where accountNumber = aNumber)And then the created insert-statement would be:insert into target.targettable (xxx, xxx, yyy, yyy)select colname1, colname2, (select customer_PK from customer where accountNumber = aNumber), colname4, morcolsfrom source.sourcetablewhere something = somethingSo the trick is that if the customSQL to select customerPK is null, then a procedure should be run to insert the customer and use the returned value as customer_pk.The insertCustomer procedure takes 3 inputs and returns one int (the generated customer_pk) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-16 : 10:27:20
|
| i dont think this is a good approach. why are you storing queries like this as values inside a column? how do you determine this query? is it imported from other department db?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Loop through all rows with inner loop for all cols