For others who need to do this with just stock .NET and PowerShell (no additional SQL tools installed) here is the function that I use: function Invoke-SQL { param( [string] $dataSource = “.\SQLEXPRESS”, [string] $database = “MasterData”, [string] $sqlCommand = $(throw “Please specify a query.”) ) $connectionString = “Data Source=$dataSource; ” + “Integrated Security=SSPI; ” … Read more
Select non-persisted values with the VALUES keyword. Then use JOINs to generate lots and lots of combinations (can be extended to create hundreds of thousands of rows and beyond). Short and fast version (not that easy to read): WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM … Read more
If you are using SQL Server 2005 or above use this: SELECT * FROM ( SELECT ID, Email, ProductName, ProductModel, ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn FROM Products ) a WHERE rn = 1 EDIT: Example using a where clause: SELECT * FROM ( SELECT ID, Email, ProductName, ProductModel, ROW_NUMBER() OVER(PARTITION BY … Read more
What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists: — subquery SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y); — predefined list SELECT a FROM x WHERE x.b NOT IN (1, 2, … Read more
Restrictions You can ask the system catalog pg_database – accessible from any database in the same database cluster. The tricky part is that CREATE DATABASE can only be executed as a single statement. The manual: CREATE DATABASE cannot be executed inside a transaction block. So it cannot be run directly inside a function or DO … Read more
The working command I’m using to execute custom SQL statements is: results = ActiveRecord::Base.connection.execute(“foo”) with “foo” being the sql statement( i.e. “SELECT * FROM table”). This command will return a set of values as a hash and put them into the results variable. So on my rails application_controller.rb I added this: def execute_statement(sql) results = … Read more
Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema). However, this snippet does have its place.. — Declare & init (2008 syntax) DECLARE @CustomerID INT = 0 — Iterate over all customers WHILE (1 = 1) BEGIN — Get next customerId SELECT TOP 1 @CustomerID = … Read more
select * from all_constraints where owner=”<NAME>” and constraint_name=”SYS_C00381400″ / Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users. The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a … Read more
A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table. A stored procedure uses parameters to do a function… whether it is updating and inserting data, or returning single values or data sets. … Read more
A table contains data, a view is just a SELECT statement which has been saved in the database (more or less, depending on your database). The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you … Read more