Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I had similar problem with much more tables involved. We can indeed avoid making cursor for each rows to be copied. The only cursor is for looping the list of the table names involved. We are also going to need dynamic SQL for this. The whole operation is extremely fast, compared to the traditional cursor loop solution.</p> <p>The trick is to insert the relevant rows into the same tables; and then update its FK column to its parent. How we can collect the mass @@identity is by making use of 'output' keyword during the insertion, and save them into a temporary table #refTrack. Later we join #refTrack with the tables involved for updating the their FKs.</p> <p>We know that:</p> <pre><code> create table #refTrack ( tbl sysname, id int, refId int ) insert InvoiceDetail (refId, invoiceNum, sequence, description, price) output 'InvoiceDetail', inserted.id, inserted.refId into #refTrack select invoiceNum, invoiceNum, sequence, description, price from InvoiceDetail where custID = 808 -- denormalized original Bob^s custID </code></pre> <p>will populate temporary #refTrack table with a list of newly created auto running numbers. Our job is just to make this insert query as dynamic.</p> <p>The only drawback of this method is that we need consistencies, on each table we must have:</p> <ol> <li>Primary key of its own by the name of 'id'. In this case we need to rename: Customer.custID to become Customer.id; Invoice.invoiceNum to become Invoice.id; and a new column 'id int identity(1, 1) primary key' in InvoiceDetail.</li> <li>A denormalized 'custID' column. For tables listed with 'depth' > 1, the table will require the current front end application to populate this new helper column. An 'insert trigger' will make our work a bit more complicated.</li> <li>A column called 'refId', defined as: int null. This column is for making the relationship of rows belong to 'Bob2' as a copy of 'Bob'.</li> </ol> <p>Steps taken:</p> <p>A. List all table names into @tList table variable</p> <pre><code> declare @tList table ( tbl sysname primary key, fkTbl sysname, fkCol sysname, depth int ) insert @tList select 'Customer', null, null, 0 insert @tList select 'Invoice', 'Customer', 'custID', 1 insert @tList select 'InvoiceDetail', 'Invoice', 'invoiceNum', 2 </code></pre> <p>I'd love to go abstract as to just populating 'tbl' column during insertion above; and dynamically populate the rest of the columns by updating them with the result of recursive CTE of information_schema's views. However that could be beside the point. Let's assume we have a table with the list of table names involved, ordered by the way it should be populated.</p> <p>B. Loop the @tList table in a cursor.</p> <pre><code> declare @depth int, @tbl sysname, @fkTbl sysname, @fkCol sysname, @exec nvarchar(max), @insCols nvarchar(max), @selCols nvarchar(max), @where nvarchar(max), @newId int, @mainTbl sysname, @custId int select @custId = 808 -- original Bob^s custID to copy from select @mainTbl = tbl from @tList where fkTbl is null declare dbCursor cursor local forward_only read_only for select tbl, fkTbl, fkCol, depth from @tlist order by depth open dbCursor fetch next from dbCursor into @tbl, @fkTbl, @fkCol, @depth while @@fetch_status = 0 begin set @where = case when @depth = 0 then 'Id' else 'custId' end + ' = ' + cast(@custId as nvarchar(20)) set @insCols = dbo.FnGetColumns(@tbl) set @selCols = replace ( @insCols, 'refId', 'Id' ) set @exec = 'insert ' + @tbl + ' (' + @insCols + ') ' + 'output ''' + @tbl + ''', inserted.id, inserted.refId into #refTrack ' + 'select ' + @selCols + ' from ' + @tbl + ' where ' + @where print @exec exec(@exec) -- remap parent if isnull(@fkTbl, @mainTbl) != @mainTbl -- third level onwards begin set @exec = 'update ' + @tbl + ' set ' + @tbl + '.' + @fkCol + ' = rf.Id from ' + @tbl + ' join #refTrack as rf on ' + @tbl + '.' + @fkCol + ' = rf.refId and rf.tbl = ''' + @fkTbl + ''' where ' + @tbl + '.custId = ' + cast(@newId as nvarchar(20)) print @exec exec(@exec) end if @depth = 0 select @newId = Id from #refTrack fetch next from dbCursor into @tbl, @fkTbl, @fkCol, @depth end close dbCursor deallocate dbCursor select * from @tList order by depth select * from #refTrack drop table #refTrack </code></pre> <p>C. The content of FnGetColumns():</p> <pre><code> create function FnGetColumns(@tableName sysname) returns nvarchar(max) as begin declare @cols nvarchar(max) set @cols = '' select @cols = @cols + ', ' + column_name from information_schema.columns where table_name = @tableName and column_name &lt;&gt; 'id' -- non PK return substring(@cols, 3, len(@cols)) end </code></pre> <p>I am sure we can further improve these scripts to be far more dynamic. But for the sake of solving the problem, this would be the minimum requirement.</p> <p>Cheers,</p> <p>Ari.</p>
    singulars
    1. This table or related slice is empty.
    1. POCopying a set of records with constraints, with common ancestor
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USIrawan Soetomo
    1. USIrawan Soetomo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCopying a set of records with constraints, with common ancestor
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload