Procedure Help

The purpose of this proc is to update a table with certain fields from another table.  The logic is that first, the previous business day is determined, and then second, the records for the previous business day are copied into the target table, only if they do not already exist there.  I am getting the following error though:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

I've been trying to troubleshoot this thing for too long now, and hope someone here can add some insight.  

Thanks!



DECLARE @TABLENAME VARCHAR(50)
DECLARE @PKCOLUMNS VARCHAR(255)
DECLARE @SQLSTMT NVARCHAR(1024)

DECLARE @PREV_BUSINESS_DAY SMALLDATETIME
DECLARE @PREV_BUSINESS_DAY_STR VARCHAR(10)
DECLARE @PREV_DAY SMALLDATETIME
DECLARE @TOTAL DECIMAL

-- DETERMINE THE PREVIOUS BUSINESS DAY
SELECT @PREV_DAY = GETDATE() - 1

-- IF PREVIOUS DAY IS A BUSINESS DAY
IF (SELECT COUNT(NON_WORK_DAY)
      FROM TBLNWORKDAY
         WHERE NON_WORK_DAY = @PREV_DAY ) = 0
BEGIN
      SELECT @PREV_BUSINESS_DAY=  @PREV_DAY
END
ELSE            -- PREVIOUS DAY IS A NON WORKING DAY
BEGIN
      -- LOOP UNTIL A NO NON WORKING DAY IS FOUND      
      WHILE (SELECT COUNT(NON_WORK_DAY)
                  FROM TBLNWORKDAY
                      WHERE NON_WORK_DAY = @PREV_DAY ) >= 1
      BEGIN
            SELECT @PREV_DAY = @PREV_DAY - 1
      END
      SELECT @PREV_BUSINESS_DAY = @PREV_DAY
END
SELECT @PREV_BUSINESS_DAY_STR = RTRIM(LTRIM(STR(CONVERT( VARCHAR, @PREV_BUSINESS_DAY, 112))))


---- TABLE 2:
SET @TABLENAME = 'TABLENAME'
SET @PKCOLUMNS = ''
SELECT @PKCOLUMNS = CASE
      WHEN @PKCOLUMNS = '' THEN @PKCOLUMNS
      WHEN LEN(@PKCOLUMNS) > 0 THEN  @PKCOLUMNS + ',  '
      END + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS P
      INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
      ON P.CONSTRAINT_NAME = C.CONSTRAINT_NAME
             AND P.TABLE_NAME = C.TABLE_NAME
WHERE P.CONSTRAINT_TYPE = 'PRIMARY KEY'
 AND P.TABLE_NAME = @TABLENAME

--- START : CHECK FOR DUPLICATES AND INSERT ONLY NEW RECORDS
IF ( SELECT COUNT(*)
FROM (       SELECT *  
            FROM  TABLENAME
            WHERE CONVERT(SMALLDATETIME, CONVERT(VARCHAR,DDTXDT)) = @PREV_BUSINESS_DAY ) AS ST
                        INNER JOIN T_ABX_TRANSMAP TT ON
                  ST.DDACCT = TT.[TABLENAME.DDACCT] AND
                  ST.DDTXDT = TT.[TABLENAME.DDTXDT] AND
                  ST.DDTAMT = TT.[TABLENAME.DDTAMT] AND
                        ST.DDBSEQ = TT.[TABLENAME.DDBSEQ] AND
                        ST.DDTIME=TT.[TABLENAME.DDTIME] ) = 0
BEGIN
      SET @SQLSTMT =       'INSERT INTO T_ABX_TRANSMAP([TABLENAME.DDACCT], [TABLENAME.DDBSEQ], [TABLENAME.DDTAMT], [TABLENAME.DDTIME], [TABLENAME.DDTXDT])      SELECT ' +  @PKCOLUMNS  +
                              ' FROM ' + @TABLENAME +
                              ' WHERE  DDTXDT = ' + @PREV_BUSINESS_DAY_STR
      --PRINT @SQLSTMT
      EXEC SP_EXECUTESQL @SQLSTMT
END
ELSE
BEGIN
 RAISERROR ('Package POP_TABLENAME has failed', 16, 1)
   ROLLBACK TRANSACTION
END
--- END: TILL HERE
LVL 1
superfly18Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
TT.[TABLENAME.DDACCT] ??? It should probably be TT.DDACCT and similarly for others.
superfly18Author Commented:
In this case, (as this is only one section of the code) TABLENAME.DDACCT refers to the actual name of the field.  In this case, the fieldname is the sourcetablename.sourcefieldname
amit_gCommented:
That is fine but why TT.TABLENAME.DAACCT? It should either be TABLENAME.DAACCT or TT.DAACCT
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

superfly18Author Commented:
INNER JOIN T_ABX_TRANSMAP TT ON


The Target Table (TT) has many fields which are populated by many source tables.  Say there is 100 source tables (ST).  Each field in the target table indicates the name of the sourcetable, and the name ouf the source field. In this case the name of the target table is T_ABX_TRANSMAP and the name of the field (actually is) TABLENAME.DAACCT, so that's why it is referenced as TT.TABLENAME.DAACCT  Tablename is the actual name of the ST, and DAACCT is actual name of the field.
amit_gCommented:
For that you would need dynamic query the way you are doing it for insert

SET @SQLSTMT =      '...
superfly18Author Commented:
Where would I need the dynamic query?
amit_gCommented:
The tablename and columnames in the query can't be variables. So you can't use a columnname given in a table field in a select. To do so you need to construct the select dynamiaclly and then execute it. You are doing it in the next insert statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.