Cursor With Dyncamic SQL (1 Viewer)

[WayneRyan]

I've got "one last hurdle" on a project.

Background:

1) User creates/modifies a few rows of data in "TableA" in Database A.

2) I round up the data, use BCP and a Format file to export it.

3) Database B imports the subset of data into a temp table "t_TableA"

4) Since Database B has a different schema, I modify the structure of
the temp table to match the target table and ensure the data conforms
to rules for nulls, minimum values, etc.

That part is all done.

Problem:

I can't just delete/insert the rows in the target table (cascading deletes
will cause some real damage. I want to iterate through the temp table "t_TableA".
I know its Primary Key(s).

If the row doesn't exist, it is easy to do a
Insert Into TableA Select(F1, F2, ...)
Select F1, F2, F3, ... From t_TableA

If the row does exist, I can make an Update statement enumerating
the columns to update.

The problem is how does one define a Cursor in T-SQL when none of
the info is known until run-time (table name, PKs, etc.)?

How do I iterate through the temp table?

Thanks,
Wayne

 

[JGT]

Hi Wayne,

I am afraid I didn’t understood exactly all details of your problem, but it should be somewhat similar to the procedure I use to transfer data from one db to another.
First I do not export/import, just link tables, so org. and dest. tables stay side by side in the same db. One can do this in an existing db or use a new 3rd db with links both to org and dest db.
You can automate this, setting up a ref.table specifying the tables with data you want to exchange. Then loop thru the ref.table content, linking each of the tables.
Next, for each of the tables in ref.table, loop through the fields collection, setting up a string with each of the fields. With this string it is easy to construct the Xfer query needed to update or append.
This works perfectly for Access dbs, quickly passing the data from one db to another - should also work with SQL, nevertheless I am facing the problem that auto nbr random PK do not copy. SQL creates its own PK, screwing up reg. import of joined secondary tables. I am looking for help on the best way around this - see thread Data transfer between tables with auto numbering PKI.
Because the table structure of both of your dbs is not the same, it will cause you some extra programming, may be using an extra step with sandwich tables, but once defined the computer does the job.

JGT

 

[WayneRyan]

JGT,

Thanks for the response. Most of the times I ask for help I don't get a response
because thay are really, really tough questions.

I do have a workaround though.

This is actually a case where the "migrating data" resides in any one/more of
hundreds of tables.

The Export software does define the "root node" for each family of data. As a
work-around, I can "reuse" that table with the Table/PK values populating it with:

TableName, PK1, PK2, PK3, PK4, PK5

for each table that has data.

Insert Into tblMap (TableName, PK1, PK2, Pk3, PK4, PK5)
Select Distinct @TableName, @PK1, @PK2, @PK3, @PK4, @PK5, 
From   SomeRandomTable

This will give me a KNOWN and static table that I can use in a T-SQL cursor.

Then, I can define a "real" cursor and iterate through it:

Set @sql =  If Exists (Select *
                       From   SomeRandomTable
                       Where  A.' + ' + @PK1Name = ' + @PK1 + ' And ' +
                              A.' + ' + @Pk2Name = ' + @PK2 ...)
               Update SomeRandomTable As A  -- 
               Set    A.F1 = B.F1,          --  Obviously I have to build a real
                     A.F2 = B.F2, ...       --  string here, but now I can at loop
               Where  A.PK1 = B.PK1 And     --  through a static table and T-SQL will
                      A.PK2 = B.PK2 ...     --  be happy.
            Else
               Insert Into SomeRandomTable
               Select *
               From   SomeTempRandomTable

Thanks for responding, this was an atypical request. Easy In VB, very weird in T-SQL.

Jeepers, Isn't everyone doing this?

Thanks again,
Wayne

 

[SQL_Hell]

Hiya,

You could add an idenitity field to the temp table and then use a while loop to interate through it, then you could have everything dynamic if you wanted to.

Example loop code (assuming you have already declared your temp table)

declare @count int
declare @max int

set @count = 1
set @max = select count(id) from #temp

while @count < = @max
begin
       select * from #temp where id = @count
       set @count = @count +1
end
print 'Time for beer!!!'