You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the “AS” keyword following the CTE’s name is not optional: WITH tab AS ( bla bla ) INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo, APartyNo, SourceRowID ) SELECT * FROM tab Please note that the code assumes that … Read more
DELETE TableA FROM TableA a INNER JOIN TableB b ON b.Bid = a.Bid AND [my filter condition] should work
You want to multiply out to milliseconds as the fractional part is discarded. SELECT DATEADD(ms, 121.25 * 1000, 0) If you want it without the date portion you can use CONVERT, with style 114 SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)
Note: An incorrect revision of this answer was edited out. Please review all answers. A subselect in the WHERE clause to retrieve the greatest BALANCE aggregated over all rows. If multiple ID values share that balance value, all would be returned. SELECT ID, BALANCE FROM CUSTOMERS WHERE BALANCE = (SELECT MAX(BALANCE) FROM CUSTOMERS)
For multiplication we simply add the number of decimal places in each argument together (using pen and paper) to work out output dec places. But division just blows your head apart. I’m off to lie down now. In SQL terms though, it’s exactly as expected. –Precision = p1 – s1 + s2 + max(6, s1 … Read more
See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example: DECLARE @textval NVARCHAR(30) SET @textval=”AB ABCDE # 123″ SELECT LEFT(SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000), PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000) + ‘X’) -1)
Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use … Read more
If you have OUTPUT parameters you can do DECLARE @retval int DECLARE @sSQL nvarchar(500); DECLARE @ParmDefinition nvarchar(500); DECLARE @tablename nvarchar(50) SELECT @tablename = N’products’ SELECT @sSQL = N’SELECT @retvalOUT = MAX(ID) FROM ‘ + @tablename; SET @ParmDefinition = N’@retvalOUT int OUTPUT’; EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; SELECT @retval; But if you don’t, and can … Read more
select distinct stuff(( select ‘,’ + u.username from users u where u.username = username order by u.username for xml path(”) ),1,1,”) as userlist from users group by username had a typo before, the above works
I implemented the standard Levenshtein edit distance function in TSQL with several optimizations that improves the speed over the other versions I’m aware of. In cases where the two strings have characters in common at their start (shared prefix), characters in common at their end (shared suffix), and when the strings are large and a … Read more