[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

Insert SQL Query into SQL Table

Quick question,


Say I have a SQL query like this:

Select A as 'WOO', B, Z as 'BLAH' 
   FROM TESTDB_TABLE
WHERE A = 'ABCDEFGHIJK..£$' 

Open in new window



How would I insert that into a SQL Table Field, i.e

UPDATE TESTDB_TABLE set SQLquery = 'Select A as....'  

Open in new window


Is there anyway in SQL to bracket up my sql string so it ignores special characters and will just update my table?

Thanks,
Mike
0
mikeyd234
Asked:
mikeyd234
1 Solution
 
Barry CunneyCommented:
INSERT
TESTDB_TABLE
(WooTargetField, BTargetField, BlahTargetField)
Select A as 'WOO', B, Z as 'BLAH'
   FROM TESTDB_TABLE
WHERE A = 'ABCDEFGHIJK..£$'
0
 
Barry CunneyCommented:
If you wish to update a single table field your select cannot have multiple fields - it would have to return a single value or concatentate the several source fields into a single expression

UPDATE target
SET target.Field = (SELECT SingleValue FROM Source)
0
 
mikeyd234Author Commented:
I think you both misunderstand my question, sorry,

I actually want to insert/update sql query text into a sql field, not the value the query returns but the actual sql query string. I'm building a reporting tool for myself and basically i want to to save generated sql statements into a table for future use, but some special characters in the sql query string cause the insert/update to fail.


I.E this will fail due to single quotes:  
SELECT 'SELECT VPXV_VMS.NAME AS VMNAME, 
       VPXV_SNAPSHOT.SNAPSHOT_NAME 
FROM VPXV_SNAPSHOT,VPXV_VMS 
WHERE VPXV_VMS.VMID = VPXV_SNAPSHOT.VM_ID 
 AND(VPXV_SNAPSHOT.POWER_STATE = 'dw')' as SQLQuery

Open in new window

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

Open in new window

0
 
ralmadaCommented:
Use two consecutive single quotes to escape quotes

UPDATE TESTDB_TABLE set SQLquery = 'SELECT VPXV_VMS.NAME AS VMNAME,
       VPXV_SNAPSHOT.SNAPSHOT_NAME
FROM VPXV_SNAPSHOT,VPXV_VMS
WHERE VPXV_VMS.VMID = VPXV_SNAPSHOT.VM_ID
 AND(VPXV_SNAPSHOT.POWER_STATE = ''dw'')'

or like this in your select

SELECT 'SELECT VPXV_VMS.NAME AS VMNAME,
       VPXV_SNAPSHOT.SNAPSHOT_NAME
FROM VPXV_SNAPSHOT,VPXV_VMS
WHERE VPXV_VMS.VMID = VPXV_SNAPSHOT.VM_ID
 AND(VPXV_SNAPSHOT.POWER_STATE = ''dw'')' as SQLQuery
0
 
ThomasMcA2Commented:
Replace every embedded single quote with 2 single quotes:

UPDATE TESTDB_TABLE set SQLquery = 'Select A as ''WOO'', B, Z as ''BLAH''
   FROM TESTDB_TABLE
WHERE A = ''ABCDEFGHIJK..£$'''

Note that the ending ''' is 3 single quotes - 2 to create the quote after the $ in your embedded string, and one to end the SQLquery string.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now