Use a select within a table field in a 2nd query

Question

Post reply

Use a select within a table field in a 2nd query

jonmarks18

SSC Enthusiast

Points: 108
More actions
August 27, 2015 at 1:58 pm

#311116

I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table
I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
I started writing code to do it but after about 4 hours gave it up as a bad job and made some tea.
I don't need anyone to write the code but any pointers to which direction I should take would be helpful as I just seem to be going around in circles.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Something like this could work if you always return the same columns.

DECLARE @SQL nvarchar(max) ;

SELECT @SQL = ISNULL( @SQL + CHAR(10) + 'UNION ALL ' + CHAR(10), '')

+ SQLStatement

FROM ScriptTable;

CREATE TABLE #Temp( CUSTNO char(10), CUSTNAME varchar(100), CUSTADDRESS varchar(100));

PRINT @SQL;

INSERT INTO #Temp

EXEC sp_executesql @SQL;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jonmarks18 SSC Enthusiast Points: 108 More actions · Answer 2

Thanks I will give that a go.

I think I was trying to be too clever with what I was doing and your short script looks simple but effective.

The SQL statment would always be returning the same fields although there will be different queries to be run.

Again Thanks

jonmarks18 SSC Enthusiast Points: 108 More actions · Answer 3

Other point is I think I will need to do some sort of cursor as I showed 2 lines in my table but there might be up to 20 lines so I need to loop around until they are all read.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

No need to loop, the code that I posted will traverse the entire table and create the complete statement.

Here's a slightly different example:

CREATE TABLE ScriptTable(

groupID Nvarchar(10),

SQLStatement Nvarchar (150)

)

INSERT INTO ScriptTable

VALUES

(1234, 'Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = ''AB123'''),

(9876, 'Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = ''XY*'''),

(8494, 'Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = ''MNO*'''),

(6547, 'Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = ''SCH'''),

(4189, 'Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = ''UO*''');

DECLARE @SQL nvarchar(max) = ''; --Assign an empty value

SELECT @SQL = @SQL + 'INSERT INTO #Temp(CUSTNO,CUSTNAME,CUSTADDRESS) ' + CHAR(10) --Generate an insert for each row

+ SQLStatement + ';' + CHAR(10)

FROM ScriptTable;

CREATE TABLE #Temp( CUSTNO char(10), CUSTNAME varchar(100), CUSTADDRESS varchar(100));

PRINT @SQL;

INSERT INTO #Temp

EXEC sp_executesql @SQL;

GO

DROP TABLE ScriptTable

DROP TABLE #Temp

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

WayneS SSC Guru Points: 95409 More actions · Answer 5

Just be careful. Looking at this, the first thing that pops into my head is

SQL INJECTION

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

I'll also add that, for the example given, I see no need for the script table at all. The only thing that's changing is a value in the WHERE clause and there are likely a half dozen methods to pull this off much more efficiently.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)