New answers tagged

8

This works too: SELECT salesid, SUM(num) FROM #BE GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END; The assumption is that id starts at 1, hence it can use THEN 0. salesid ELSE salesid+id+1 would work as well. It must be tested with real data and indexes, but with only 1 table scan, performances may be a little better in some cases. The ...


9

Try this: select salesid, sum(num) as num from #BE where num > 0 group by salesid UNION ALL select salesid, num from #BE where num < 0 ; If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in http://stackoverflow....


1

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


-2

WITH Reorg AS (SELECT * FROM DepartmentReorg WHERE GroupName = 'Executive General and Administration') MERGE INTO HumanResources.Department Dep USING Reorg ON Dep.DepartmentID = Reorg.DepartmentID WHEN MATCHED THEN UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate() WHEN NOT MATCHED BY TARGET THEN ...


0

Assuming you have the .dtsx files, they are just XML so you could use a simple text search of the files, something like this http://stackoverflow.com/questions/8153750/how-to-search-a-string-in-multiple-files-and-return-the-names-of-files-in-powers


2

System databases cannot be mirrored or logshipping. The best way is to script and sync up desired objects e.g. logins, jobs, etc. The best tool that I have used so far for migrating databases, jobs, agent schedules, operators, logins, etc is powershell based dbatools.io


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


-1

Before you vote this down look at the question before the major edits This is valid (and good) answer to the original stated question What you have is just messed up Why use a merge for an update only? Why use a CTE when it is the whole table update firms set firms.Total = results.Total from frims join results on firms.firm = results.firm ...


0

I believe you cannot do that. But, I came across something similar answered on the forum already. You can achieve a similar effect by following the steps in the link below. Please go through the below link and check if it might be useful to you. http://stackoverflow.com/a/9503789/2818351 Credits to the original author who answered the question.


4

Use cross apply to unpivot columns into rows SELECT --DISTINCT most probably JobRunId, ut.Name, ut.[Type], ut.Granularity FROM dbo.TestUpload CROSS APPLY ( SELECT ThingBName AS Name, ThingBType AS [Type], ThingBGranularity AS Granularity UNION ALL SELECT ThingAName AS Name, ThingAType AS [Type], ThingAGranularity AS ...


5

I'd try this but I have no idea if it will be more efficient. You need the DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations: SELECT DISTINCT JobRunId = @JobRunID, d.* FROM dbo.TestUpload CROSS APPLY ( SELECT ThingAName AS Name, ThingAType AS [Type], ...


1

I have tried this on SQL Server 2012. If I run your query as is it completes successfully but does not update anything. But if I do SET xact_abort ON it completes successfully. You can read more about xact_abort on https://msdn.microsoft.com/en-us/library/ms188792.aspx. SET xact_abort ON BEGIN try BEGIN TRANSACTION trf update [SERVER].[testdb]....


0

While there isn't any option that I am aware of to turn off this feature, a simple ctrl+h and then replace both [ and ] with an empty space will quickly remove the brackets from the generated t-sql scripts.


3

The context of this question is strikingly similar to this question: LOB_DATA, slow table scans, and some I/O questions. But the direction is a bit different. In order to answer whether or not there would be much benefit in moving the XML column into its own table requires answering the following questions first (and I will explain why in a moment): How ...


3

The database engine is pretty smart about this and does not read LOB pages unless it has to as far as I can tell, but I created a test-rig to double-check. Test Rig USE master GO SET NOCOUNT ON GO CREATE DATABASE bigXMLTest GO ALTER DATABASE bigXMLTest SET RECOVERY SIMPLE GO ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest', SIZE = 20GB , ...


2

Yes. You can write your own snippets code in a .snippet file and then import it into SSMS. To import the folder containing the .snippet file: Open SSMS Click on Tools ->Code Snippets Manager Click on Add and select the folder where your custom snippets are written. I believe this answers a part of your question. You can further follow the below links to ...


1

SSMS has useful editing option, column editing. Hold ALT/SHIFT and select column with a mouse (left button pressed), a column selection appears. Type the text. Click anywhere to exit.


1

If you are confident that there can only ever be at most two rows for any given customer, then you can do this: ;WITH x AS ( SELECT id,customer,os,ms,st, previous_os = LAG(os,1,NULL) OVER (PARTITION BY customer ORDER BY id), previous_ms = LAG(ms,1,NULL) OVER (PARTITION BY customer ORDER BY id), previous_st = LAG(st,1,NULL) OVER (PARTITION BY ...


9

Do I need to create a store procedure first that way the inputs are recognized? You are missing @ symbols at the beginning of each parameter. Find out more about writing functions here Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not ...


0

This one uses a CTE, but does not rely on row_number (just in case you find the syntax overly complicated) with MaxIDs as ( select max(id) as 'MaxID', bbid from #One group by bbid ) select o.id, o.bbid, t.indate, t.st, o.val from MaxIDs m join #Two t on m.bbid = t.bbid join #One o on o.id = m.maxid order by 1 desc


0

Here you go! select t1.id, t1.mmbid, t3.indate, t3.st, t1.val from #one t1 (select bbid, MAX(id) mbbid from #one ) t2 on t1.id=t2.id and t1.bbid=t2.mbbid join #two t3 on t1.mbbid=t3.bbid


1

select * from ( select t1.id , t3.bbid , t3.indate , t3.st , t1.val , row_number() over (partition by bbid order by id desc) as rn FROM #One t1 INNER JOIN #Two t3 ON t1.bbid = t3.bbid ) tt where tt.rn = 1 or just put this in your query as t2 Select MAX(ID) As "Maxid" FROM #One GROUP BY bbid


2

You can use a common table expression (cte). ;with cte as ( select row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one ) select t1.id ,t3.bbid ,t3.indate ,t3.st ,t1.val from #One as t1 join #Two as t3 on t1.bbid = t3.bbid join cte on cte.id = t1.id and cte.rn = 1 order by t1.id desc


2

update dbo.B set b.[ID] = a.orig_id FROM dbo.A a join dbo.B b on b.[NUMBER] = a.number and b.[ID] <> a.orig_id the <> is so it can avoid taking a lock if the are equal


0

You are missing the error handling piece (i.e. TRY...CATCH). Please see the Stored Procedure template that I posted in the following DBA.StackExchange answer: Are we required to handle Transaction in C# Code as well as in Store procedure


3

If I understand the Question correctly, you need to use the XML_SCHEMA_NAMESPACE built-in function to extract the Schema Collection, and you can find the list of those in the sys.xml_schema_collections system catalog view. You can use something along the lines of: SELECT CONVERT(NVARCHAR(MAX), XML_SCHEMA_NAMESPACE(sch.[name], xsc.[name])) AS [XSD] FROM ...


2

Probably you need tree nodes in depth-first order with tag as ( select * from (values (1,'.Net',cast(null as int)), (2,'EF',1), (3,'NHib',1), (4,'CF',2), (5,'Java',null), (6,'JRE',3)) t([Id],[Name],[ParentTagId]) ), cte (ParentID, ID, Name, lev, shift)AS( SELECT ParentTagID, ID, Name, ...


10

I find the simple syntax the more readable and less confusing. Why use functions (that can throw these weird errors) when you can check whether all variables (one by one) are NULL?: CASE WHEN (@a IS NULL AND @b iS NULL .... AND @x iS NULL) THEN 'yes' ELSE 'no' END Alternative conditions - although less readable in my opinion would be: ...


11

As long as all the variables involved are of datatypes compatible with sql_variant (they are in this case - basically no LOB datatypes, CLR types, or user defined datatypes) then you can use SELECT CASE WHEN COALESCE( CAST(@StartDate as sql_variant), @EndDate, @StateCode, @CountyCode, @ProducerName, @TaxID, @Farm) IS NULL ...


4

COALESCE must return a valid datatype. I think your problem is related to the way it works to determine datatype to return. A quick workaround can be: IF (@StartDate IS NULL AND @StateCode IS NULL AND ....) BEGIN SELECT 'Yes' END ELSE SELECT 'No'


3

You are doing a cartesian product here: ... FROM dbo.[DataSource], @ids i you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it: INSERT INTO dbo.[Users] ( first_name, surname , address_id ) SELECT first_name, surname, ...


6

What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table? I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, ...


4

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation: SELECT UserID, Year, M1 = SUM(CASE Mth WHEN 1 THEN Value END), M2 = SUM(CASE Mth WHEN 2 THEN Value END), M3 = SUM(CASE Mth WHEN 3 THEN Value END), M4 = SUM(CASE Mth WHEN 4 ...


7

As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article. There is a quick and easy change you can make to your existing code though. Instead of SELECT DISTINCT email, STUFF((...


5

UPDATE dbo.[User] SET username_column = N'ACCOUNTS' + LOWER(SUBSTRING(username_column, 9, 4000)) WHERE username_column LIKE 'ACCOUNTS\%';


1

Replace and Lower should do the job here. UPDATE dbo.User SET username = 'ACCOUNTS\' + LOWER(REPLACE(username, 'ACCOUNTS\', ''))


6

That STUFF FOR XML PATH string concatenation technique sure is cute, but it does not scale very well and across millions of rows it is probably not a very good idea. For larger tables, you may have to write some good old-fashioned procedural SQL with a loop, something like this: -- Create the working table ... IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP ...


1

This will be faster than setting by hand if you have many companies. If there were more fields than 3 or 4 I would consider building a dynamic loop. Update R1 Set R1.power1 = R2.power1 From #Rentarious R1 Left Join (Select * From #Rentarious Where power1 Is Not Null) R2 On R2.C1 = R1.C1 Where R1.power1 Is Null Update R1 Set R1....


2

s is not valid at that point you only have inserted if you want the fields then try OUTPUT inserted.ID, inserted.[Field1], inserted.[Field2] instead of line 2 in your query. You can find more here: OUTPUT Clause (Transact-SQL)


1

You can SET multiple fields by delimiting each with a comma. You'll just need to specify the record based on some unique pattern, in this case c1 and yryryryr. UPDATE #Rentarious SET power1 = 'Red' , power2 = 'Blue' , power3 = 'Green' WHERE c1 = 'Build Blocks' AND yryryryr = '2012';


2

is there a statement to check if a specific index's auto stats is disabled? You can use sys.sp_autostats or sys.stats to get this information. sys.sp_autostats can also be used to enable or disable automatic updates for a particular statistics object. For example, using the AdventureWorks sample database: EXECUTE sys.sp_autostats @tblname = N'[...


0

Not sure about this but I think it will work If they both have values then I think it is different from your case statement isnull(@Parameter1, Field2 ) = @Parameter2 or isnull(@Parameter2, Field1 ) = @Parameter1 isnull


6

If I understood you, this should do the job: SELECT ... WHERE (Field2 = @Parameter2 AND @Parameter1 IS NULL) OR (Field1 = @Parameter1 AND @Parameter2 IS NULL) However, I'd rather not do such things because of potential performance issues. Execution plan for this query depends to a great extent on parameter values. For instance, passing NULL as ...


9

The simplest answer to your question involves telling SQL Server what to look for by placing brackets around grouped requirements. So, for instance, if we only want to check Field2 when @parameter1 IS NULL, we could do this: WHERE (@parameter1 IS NULL AND Field2 = @parameter2) OR (Field1 = @parameter1) SQL Server will evaluate Field2 = @parameter2 ...


2

Ms Sql recursive CTE syntax has no RECURSIVE keyword See https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Ie. CTE of the form WITH cte_name ( column_name [,...n] ) AS ( –- Anchor member UNION ALL –- Recursive member ) is recursive when Recursive member references cte_name. For example WITH node_rec AS ( SELECT TOP(10) 1 AS ...


1

Not quite. It's automatic and would be like this: WITH node_rec AS ( SELECT TOP 10 1 AS depth, [node] AS path, * FROM nodes WHERE parent IS NULL UNION ALL SELECT r.depth + 1, path, n.* FROM node_rec r JOIN nodes n ON n.parent = r.node WHERE r.depth < 4 ) SELECT * FROM node_rec I'm not sure what the ...


1

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there: SQL print line number in comment of dynamically created stored procedure? The relevant portion of that Answer is copied below: You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE(...


0

Update this new column with dense_rank() -- test data select * into #t from (values ('A','UT',0), ('A','UT',0), ('B','UT',0), ('B','UT',0), ('A','TX',0), ('C ','TX',0) ) x([variable1], [state], tkey) ; -- update with tdr as ( select * , r=dense_rank() over( order by [variable1], [state]) from #t ) update ...


0

Possible duplicate http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio It sounds like what you are wanting is a composite key. There is a T-SQL example in the link provided.


0

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data: select * from [ForgeDB].[Snapshot].[SnapshotPersonEmployee] where charindex(0xA0, cast(Address1 as varbinary(8000))) > 0; Check this answer: http://dba.stackexchange.com/a/37355/55596



Top 50 recent answers are included