mikeyd234
asked on
Insert SQL Query into SQL Table
Quick question,
Say I have a SQL query like this:
How would I insert that into a SQL Table Field, i.e
Is there anyway in SQL to bracket up my sql string so it ignores special characters and will just update my table?
Thanks,
Mike
Say I have a SQL query like this:
Select A as 'WOO', B, Z as 'BLAH'
FROM TESTDB_TABLE
WHERE A = 'ABCDEFGHIJK..£$'
How would I insert that into a SQL Table Field, i.e
UPDATE TESTDB_TABLE set SQLquery = 'Select A as....'
Is there anyway in SQL to bracket up my sql string so it ignores special characters and will just update my table?
Thanks,
Mike
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)
UPDATE target
SET target.Field = (SELECT SingleValue FROM Source)
ASKER
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:
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
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
TESTDB_TABLE
(WooTargetField, BTargetField, BlahTargetField)
Select A as 'WOO', B, Z as 'BLAH'
FROM TESTDB_TABLE
WHERE A = 'ABCDEFGHIJK..£$'