Visibility Warning: Don’t the other answer. It will give incorrect values. Read on for why it’s wrong. Given the kludge needed to make UPDATE with OUTPUT work in SQL Server 2008 R2, I changed my query from: UPDATE BatchReports SET IsProcessed = 1 OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate WHERE BatchReports.BatchReportGUID = @someGuid to: SELECT BatchFileXml, ResponseFileXml, … Read more
You can specify the connection string as one long string that uses semi-colons (;) as the argument separator. Working example: import pyodbc cnxn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;’) cursor = cnxn.cursor() cursor.execute(“SELECT LastName FROM myContacts”) while 1: row = cursor.fetchone() if not row: break print(row.LastName) cnxn.close() For connection strings with lots of parameters, the following will accomplish … Read more
I’ve been operating under the assumption that a single statement in SQL Server is consistent That assumption is wrong. The following two transactions have identical locking semantics: STATEMENT BEGIN TRAN; STATEMENT; COMMIT No difference at all. Single statements and auto-commits do not change anything. So merging all logic into one statement does not help (if … Read more
I came here before looking for a solution for bulk inserting special characters. Didn’t like the workaround with UTF-16 (that would double the size of csv file). I found out that you definitely CAN and it’s very easy, you don’t need a format file. This answer is for other people who are looking for the … Read more
if not exists (select * from sysobjects where name=”cars” and xtype=”U”) create table cars ( Name varchar(64) not null ) go The above will create a table called cars if the table does not already exist.
One more option UPDATE x SET x.CODE_DEST = x.New_CODE_DEST FROM ( SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST FROM DESTINATAIRE_TEMP ) x
There is a very simple solution using dense_rank() dense_rank() over (partition by [Mth] order by [UserAccountKey]) + dense_rank() over (partition by [Mth] order by [UserAccountKey] desc) – 1 This will give you exactly what you were asking for: The number of distinct UserAccountKeys within each month.
. and (local) and YourMachineName are all equivalent, referring to your own machine. (LocalDB)\instance is SQL Server 2012 Express only. The other parts are depending on how you install – if you install with an instance name – then you need to spell that instance name out (SQL Server Express by default uses the SQLEXPRESS … Read more
Just be careful of the difference with outer joins. A query where a filter of b.IsApproved (on the right table, Bar) is added to the ON condition of the JOIN: SELECT * FROM Foo f LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); Is NOT the same as placing the … Read more
May be this will help you. SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX(“https://stackoverflow.com/”, myColumn) WHEN 0 THEN LEN(myColumn) ELSE CHARINDEX(“https://stackoverflow.com/”, myColumn) – 1 END) AS FirstName ,SUBSTRING(myColumn, CASE CHARINDEX(“https://stackoverflow.com/”, myColumn) WHEN 0 THEN LEN(myColumn) + 1 ELSE CHARINDEX(“https://stackoverflow.com/”, myColumn) + 1 END, 1000) AS LastName FROM MyTable