Using a join in dynamic sql

Posted on 2016-09-16
Medium Priority
Last Modified: 2016-09-16

Every time I think that I might be starting to understand this, it seems to put me in my place!

I have the following code:

SET @maxcounter = 0;
WHILE @maxcounter < @maxcnt
    SET @crtcnt = @maxcounter + 1;
      SET @sqlstr = 'UPDATE A SET old_SSN' + CAST(@crtcnt AS VARCHAR) + ' = B.old_SSN' + CAST(@crtcnt AS VARCHAR) + 'FROM LFRD_Summary A LEFT OUTER JOIN LFRD B ON A.SSN = B.SSN';
      EXEC (@sqlstr);
    SET @maxcounter = @maxcounter + 1;

I get an error which doesn't refer to any of these lines but it ran fine before I put these in.

I have varying numbers of columns for old_ssn in both LFRD and LFRD_Summary.  I used SELECT DISTINCT to get the unique SSNs into LFRD_Summary from LFRD.  Now, I need to get the other column data into LFRD_Summary.

I've tried putting a join together using dynamic SQL to do this, using what I've learned on here, but it doesn't seem to like it.

The variables are declared elsewhere.

Can anybody help me please?

Question by:ScuzzyJo
LVL 56

Accepted Solution

Ryan Chong earned 2000 total points
ID: 41801738
do you need to add a space before the "FROM" clause?

try this:

SET @sqlstr = 'UPDATE A SET old_SSN' + CAST(@crtcnt AS VARCHAR) + ' = B.old_SSN' + CAST(@crtcnt AS VARCHAR) + ' FROM LFRD_Summary A LEFT OUTER JOIN LFRD B ON A.SSN = B.SSN';

Author Comment

ID: 41801754
Hi Ryan

Thanks.  I think that works.

It's heartening as maybe I do understand it a bit now if I only left a space out!

LVL 16

Expert Comment

by:Megan Brooks
ID: 41801769
It should become clearer within a year or two. :-) To actually understand it might take a bit longer.

Some basic questions:
What's the error message? (I don't see a space in front of FROM)
How big are the tables?
How often do you need to do this? Once?

The reason I ask the last two questions is that ordinarily I wouldn't use a WHILE loop (if I HAVE to loop, I usually use a well-configured cursor), but if it's a one-time thing or the amount of data is small then perhaps any old solution will do. Dealing with denormalized fields of this sort can be messy and possibly not worth the effort to do it 'right.'

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question