Copy and Past — To update your table data in SSMS (SQL Server 2008/+)

Posted: February 23, 2012 | Author: Jingyang Li | Filed under: SQL Server 2008 | Leave a comment

You can import your data to your local or remote database for development to refresh your table data.
We have done this through SQL Server Import/Export wizard for many years. Here comes an easy way now. You can copy your data from different sources with the same structure to past them to your data table to refresh the content.
In your table, you choose the edit mode and highlight a new row and paste the content and the data table will be updated with the new data.
You can copy the dataset from query resultset, excel spreedsheet or another same structure table.
You can copy the identity column with the dataset but it will be recreated with new values.
Hope this tip will help.

Adding Time in HH:MM Format – Useful SQL Query (Alternate solution)

I came across this post “Adding Time in HH:MM Format – Useful SQL Query” from codeproject website http://www.codeproject.com/Tips/207655/Adding-Time-in-HH-MM-Format-Useful-SQL-Query.
Here is a simple version I would like to recommend:

SELECT CONVERT(VARCHAR(5),
CAST(DATEADD(s,SUM(DATEDIFF(s,0,CAST(timeduration as DATETIME))),0) as DateTIME)
–Format to hh:mi:ss:mmm(24h)
, 114) AS timeSum
FROM mytable
The table and sample data (loaded for SQL Server 2008)
–CREATE TABLE mytable
–(
–id int identity(1,1),
–timeduration varchar(5),
–mytime time(0)
–)
If you are using SQL Server 2008 or above, you can use time data type directly.
SELECT CAST(dateadd(minute,sum(datediff(minute,0,mytime)),
Cast(CONVERT(VARCHAR(10), Getdate(), 101) AS DATETIME)) as Time(0))
FROM mytable