SQL update fields of one table from fields of another one
You can use the non-standard FROM clause. UPDATE b SET column1 = a.column1, column2 = a.column2, column3 = a.column3 FROM a WHERE a.id = b.id AND b.id = 1
dynamic-sql
How to select column names dynamically in mySQL
Try this SQLFiddle: CREATE TABLE atable ( prefix1 VARCHAR(10) ,prefix2 VARCHAR(10) ,notprefix3 INT ,notprefix4 INT ); INSERT INTO atable VALUES (‘qwer qwer’, ‘qwerqwer’, 1, 1); INSERT INTO atable VALUES (‘qwer qwer’, ‘asdfaasd’, 1, 1); INSERT INTO atable VALUES (‘qwer qwer’, ‘qrt vbb’, 1, 1); INSERT INTO atable VALUES (‘qwer qwer’, ‘sdfg sdg’, 1, 1); SELECT … Read more
Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
I’m not sure if you’re still looking into this, but the DbCommandBuilder class provides a method QuoteIdentifier for this purpose. The main benefits of this are that it’s database-independent and doesn’t involve any RegEx mess. As of .NET 4.5, you have everything you need to sanitize table and column names just using your DbConnection object: … Read more
Dynamic SQL to generate column names?
Having answered a lot of these over the years by generating dynamic pivot SQL from the metadata, have a look at these examples: SQL Dynamic Pivot – how to order columns SQL Server 2005 Pivot on Unknown Number of Columns What SQL query or view will show “dynamic columns” How do I Pivot on an … Read more
User defined variables in PostgreSQL
Postgres does not normally use variables in plain SQL. But you can do that, too: SET foo.test=”SELECT bar FROM baz”; SELECT current_setting(‘foo.test’); Read about Customized Options in the manual. In PostgreSQL 9.1 or earlier you needed to declare custom_variable_classes before you could use that. However, You cannot EXECUTE dynamic SQL without a PL (procedural language). … Read more
Dynamic SQL (EXECUTE) as condition for IF statement
This construct is not possible: IF EXECUTE ‘EXISTS (SELECT 1 FROM mytable)’ THEN … You can simplify to: IF EXISTS (SELECT 1 FROM mytable) THEN … But your example is probably simplified. For dynamic SQL executed with EXECUTE, read the manual here. You can check the special variable FOUND immediately after executing any DML command … Read more
Dynamic SQL results into temp table in SQL Stored procedure
Try: SELECT into #T1 execute (‘execute ‘ + @SQLString ) And this smells real bad like an sql injection vulnerability. correction (per @CarpeDiem’s comment): INSERT into #T1 execute (‘execute ‘ + @SQLString ) also, omit the ‘execute’ if the sql string is something other than a procedure
Generate Delete Statement From Foreign Key Relationships in SQL 2008?
Here is a script for cascading delete by Aasim Abdullah, works for me on MS SQL Server 2008: IF OBJECT_ID(‘dbo.udfGetFullQualName’) IS NOT NULL DROP FUNCTION dbo.udfGetFullQualName; GO CREATE FUNCTION dbo.udfGetFullQualName (@ObjectId INT) RETURNS VARCHAR (300) AS BEGIN DECLARE @schema_id AS BIGINT; SELECT @schema_id = schema_id FROM sys.tables WHERE object_id = @ObjectId; RETURN ‘[‘ + SCHEMA_NAME(@schema_id) … Read more
T-SQL Dynamic SQL and Temp Tables
You first need to create your table first then it will be available in the dynamic SQL. This works: CREATE TABLE #temp3 (id INT) EXEC (‘insert #temp3 values(1)’) SELECT * FROM #temp3 This will not work: EXEC ( ‘create table #temp2 (id int) insert #temp2 values(1)’ ) SELECT * FROM #temp2 In other words: Create … Read more
Execute a dynamic crosstab query
What you ask for is impossible. SQL is a strictly typed language. PostgreSQL functions need to declare a return type (RETURNS ..) at the time of creation. A limited way around this is with polymorphic functions. If you can provide the return type at the time of the function call. But that’s not evident from … Read more