It truly all depends on what it is that you're wanting to see.

For example, you might be able to work with the union command.

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)

Start here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

This is what DTS was designed for.  Sure, there are other ways (such as generating your own SQL script) but since you're new, stick with DTS.  With that you should...

  IF object_id('tempDB..#test') IS NOT NULL  

    drop table #test

If all you want is the table structures, there's a way to do that through the right-click menu in Enterprise Manager (All tasks | generate SQL Script). 

Also take a...

Here's a starting point for you:

create table #tmpfolder_items

(folderid int, nodeid int, documentid int)

create table #tmpdn

(documentnumber int, docdeleted int)

create table #tmpdkw

(documentnumber int,keyword_value varchar(20), docwhere int)    

create table #tmptablevar

(str varchar(20))

declare @folderid...

Without expected output, I have to guess.

Your query should look something like:

select t.col1, m.col2

from

TBL_ACH t

INNER JOIN

MTH_YR m

ON

t.EFF_DTE = m.MTH_YR

Some notes:

Having the linkage be a varchar column is a risky...

Well. I'm trying but this isn't making any sense to me:

  select distinct dn.documentnumber from docnumbers dn (nolock)

  inner join  DocKeywords dk (nolock) on dk.documentnumber = dn.documentnumber 

  inner join...

What kind of errors are you getting?

Guessing, I'd say it's your order of insert / update which is at fault.  Examine the order to ensure you aren't inserting data for...

Hmmm, that cursor complicates things.  Not sure of the error you're getting or why (whould have to see your code).

However, what you might want to do is run the select...

Have a look at this:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1225

So what's the linkage?  RPC_ID to ID or Mth_YR to Eff_DTE or what?

What's the expected output?

Try using a temp table instead of a table variable.

I just did a real simple test and it worked.

Here's my quickie code:

select *

into #tmptest

from test_table

declare @sqlstr varchar(200)

set @sqlstr =...

Two other ideas for future reference:

1) You can edit the transformation step to define the data types

2) Make the transformation step itself a VB script.  Doing so, you can check...

What data do you have? (What are the table structures?)  What data do you want back?

Show us a sample expected output.