Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more
You can try it this way: /* Get the data into a temp table */ SELECT * INTO #TempTable FROM YourTable /* Drop the columns that are not needed */ ALTER TABLE #TempTable DROP COLUMN ColumnToDrop /* Get results and drop temp table */ SELECT * FROM #TempTable DROP TABLE #TempTable
SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’ To show only tables from a particular database SELECT TABLE_NAME FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ Or, SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_CATALOG=’dbName’ –(for MySql, use: TABLE_SCHEMA=’dbName’ ) PS: For SQL … Read more
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well? See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 … Read more
Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008: DECLARE @my_table TABLE ( [value] VARCHAR(200) ) INSERT INTO @my_table VALUES (‘hi, my name”s tim.’) SELECT * FROM @my_table Results value ================== hi, my name’s tim.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it’s across scopes. You could get a value from a trigger, instead of your current statement. SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and … Read more
First, make all current NULL values disappear: UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL Then, update the table definition to disallow “NULLs”: ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL
For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘TheSchema’ AND TABLE_NAME = ‘TheTable’)) BEGIN –Do Stuff END
Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn’t have a row id: DELETE FROM MyTable LEFT OUTER JOIN ( SELECT MIN(RowId) as RowId, Col1, Col2, Col3 FROM MyTable GROUP BY Col1, Col2, Col3 ) as KeepRows … Read more
Syntax strictly depends on which SQL DBMS you’re using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you’re using a SQL DBMS other … Read more