Assuming you want an actual SQL Server MERGE statement: MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target USING dbo.temp_energydata AS source ON target.webmeterID = source.webmeterID AND target.DateTime = source.DateTime WHEN MATCHED THEN UPDATE SET target.kWh = source.kWh WHEN NOT MATCHED BY TARGET THEN INSERT (webmeterID, DateTime, kWh) VALUES (source.webmeterID, source.DateTime, source.kWh); If you also want to … Read more
From Save (Not Permitted) Dialog Box on MSDN : The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created. The following actions might require a table to be re-created: Adding a new column to the middle … Read more
You need to enable it. Check out the Permission section of the xp_cmdshell MSDN docs: http://msdn.microsoft.com/en-us/library/ms190693.aspx: — To allow advanced options to be changed. EXEC sp_configure ‘show advanced options’, 1 GO — To update the currently configured value for advanced options. RECONFIGURE GO — To enable the feature. EXEC sp_configure ‘xp_cmdshell’, 1 GO — To … Read more
This is by far the best post for exporting to excel from SQL: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 To quote from user madhivanan, Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel Create an Excel file named testing having the headers same as that of table columns … Read more
By using VARCHAR(MAX) you are basically telling SQL Server “store the values in this field how you see best”, SQL Server will then choose whether to store values as a regular VARCHAR or as a LOB (Large object). In general if the values stored are less than 8,000 bytes SQL Server will treat values as … Read more
Open SQL Server Management Studio Go to Tools > Options > Query Results > SQL Server > Results To Text On the far right, there is a drop down box called Output Format Choose Comma Delimited and click OK Here’s a full screen version of that image, below This will show your query results as … Read more
Please check the ServerName which you provided. It should match with the below shown Name in the UserName textbox, and that name should followed with \SQLEXPRESS:
Yes you can perform a dynamic pivot. Sometimes it is easier to work up the PIVOT query using a static version first so you can see how the query and results will appear. Then transform the query into a dynamic version. Here is an example of a static vs. dynamic version of a query: Static … Read more
FOR XML PATH trick and article CLR User defined aggregate for sql server prior version 2005 – temporary tables An example of #1 DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100)) INSERT @t VALUES (1, ‘Mary’),(1, ‘John’),(1, ‘Sam’),(2, ‘Alaina’),(2, ‘Edward’) SELECT distinct EmpId, ( SELECT EmpName+’,’ FROM @t t2 WHERE t2.EmpId = t1.EmpId FOR XML PATH(”) … Read more