Bulk/batch update/upsert in PostgreSQL

Bulk insert You can modify the bulk insert of three columns by @Ketema: INSERT INTO “table” (col1, col2, col3) VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33); It becomes: INSERT INTO “table” (col1, col2, col3) VALUES (unnest(array[11,21,31]), unnest(array[12,22,32]), unnest(array[13,23,33])) Replacing the values with placeholders: INSERT INTO “table” (col1, col2, col3) VALUES … Read more

Multiple Ranks in one table

This is a bit tricky. You may want to use variables, such as in the following example: SELECT ( CASE cust_type WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := cust_type END ) + 1 AS rank, cust_type, cust_name, revenue FROM sales, (SELECT @curRow := 0, @curType := ”) … Read more

How to find column names for all tables in all databases in SQL Server

Try this: select o.name,c.name from sys.columns c inner join sys.objects o on c.object_id=o.object_id order by o.name,c.column_id With resulting column names this would be: select o.name as [Table], c.name as [Column] from sys.columns c inner join sys.objects o on c.object_id=o.object_id –where c.name=”column you want to find” order by o.name,c.name Or for more detail: SELECT s.name as … Read more