How to get first character of a string in SQL?
LEFT(colName, 1) will also do this, also. It’s equivalent to SUBSTRING(colName, 1, 1). I like LEFT, since I find it a bit cleaner, but really, there’s no difference either way.
sql
Copy tables from one database to another in SQL Server
SQL Server Management Studio’s “Import Data” task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into. If the tables don’t exist it will create them for you, but you’ll probably have to recreate any indexes and such. If … Read more
how do I query sql for a latest record date for each user
select t.username, t.date, t.value from MyTable t inner join ( select username, max(date) as MaxDate from MyTable group by username ) tm on t.username = tm.username and t.date = tm.MaxDate
Entity Framework. Delete all rows in table
For those that are googling this and ended up here like me, this is how you currently do it in EF5 and EF6: context.Database.ExecuteSqlCommand(“TRUNCATE TABLE [TableName]”); Assuming context is a System.Data.Entity.DbContext Edit: Currently in net6.0 (dotnet 6 core) you can do the following: context.Database.ExecuteSqlRaw(“TRUNCATE TABLE [TableName]”); Or use the Async overload: await context.Database.ExecuteSqlRawAsync(“TRUNCATE TABLE [TableName]”); … Read more
Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
You can cast your timestamp to a date by suffixing it with ::date. Here, in psql, is a timestamp: # select ‘2010-01-01 12:00:00′::timestamp; timestamp ——————— 2010-01-01 12:00:00 Now we’ll cast it to a date: wconrad=# select ‘2010-01-01 12:00:00’::timestamp::date; date ———— 2010-01-01 On the other hand you can use date_trunc function. The difference between them is … Read more
Best practices for SQL varchar column length [closed]
No DBMS I know of has any “optimization” that will make a VARCHAR with a 2^n length perform better than one with a max length that is not a power of 2. I think early SQL Server versions actually treated a VARCHAR with length 255 differently than one with a higher maximum length. I don’t … Read more
Validation failed for one or more entities while saving changes to SQL Server Database using Entity Framework
You can extract all the information from the DbEntityValidationException with the following code (you need to add the namespaces: System.Data.Entity.Validation and System.Diagnostics to your using list): catch (DbEntityValidationException dbEx) { foreach (var validationErrors in dbEx.EntityValidationErrors) { foreach (var validationError in validationErrors.ValidationErrors) { Trace.TraceInformation(“Property: {0} Error: {1}”, validationError.PropertyName, validationError.ErrorMessage); } } }
INSERT with SELECT
Yes, absolutely, but check your syntax. INSERT INTO courses (name, location, gid) SELECT name, location, 1 FROM courses WHERE cid = 2 You can put a constant of the same type as gid in its place, not just 1, of course. And, I just made up the cid value.
Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
Suppose I have the following table T: a b ——– 1 abc 1 def 1 ghi 2 jkl 2 mno 2 pqr And I do the following query: SELECT a, b FROM T GROUP BY a The output should have two rows, one row where a=1 and a second row where a=2. But what should … Read more
What does SQL clause “GROUP BY 1” mean?
It means to group by the first column of your result set regardless of what it’s called. You can do the same with ORDER BY.