Use SCOPE_IDENTITY: — do insert SELECT SCOPE_IDENTITY(); Which will give you: The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
You can’t declare variables in a view. Could you make it into a function or stored procedure? Edit – you might also be able to put something into a CTE (Common Table Expression) and keep it as a view. e.g. WITH conditions as ( … do the STUFF here ) SELECT blah FROM blah INNER … Read more
Try checking for NULL explicitly: SELECT TOP 30 col1, col2, …, coln FROM Mails WHERE (assignedByTeam <> ‘team01’ OR assignedByTeam IS NULL)
You can use a ranking function and a common table expression. WITH e AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ApplicationId ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC ) AS Recency FROM [Event] ) SELECT * FROM e WHERE Recency = 1
declare @D as datetime=”2012-01-02T12:04:11.443″ select dateadd(hour, datediff(hour, 0, @D), 0) select dateadd(minute, datediff(minute, 0, @D), 0)
Something like this should work: INSERT INTO #IMEIS (imei) VALUES (‘val1’), (‘val2’), … UPDATE: Apparently this syntax is only available starting on SQL Server 2008.
The [name] field in sys.objects will contain only the actual name (i.e. trg), not including the schema (i.e. dbo in this case) or any text qualifiers (i.e. [ and ] in this case). AND, you don’t specify the table name for DROP TRIGGER since the trigger is an object by itself (unlike indexes). So you … Read more
To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function’s results in the where clause. WITH MyCte AS ( select employee_id, RowNum = row_number() OVER ( order by employee_id ) from V_EMPLOYEE ORDER BY Employee_ID ) SELECT employee_id FROM MyCte … Read more
Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation: SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);
Search Tables: SELECT c.name AS ‘ColumnName’ ,(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name) AS ‘TableName’ FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName; Search Tables and Views: SELECT COLUMN_NAME AS ‘ColumnName’ ,TABLE_NAME AS ‘TableName’ FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName;