Trigger to sum in values in an SQL database column

I have a database with 2 tables in SQL Server 2008 Express. My problem is the following: I would like to create a trigger to sum some values in first table and copy the sum into second one. For example the first table (Head) has 5 columns : ID Transaction Acount Date Total_sum —————————————————– 1 […]

Distinct and Group By – query performance

Query A SELECT Distinct ord_no FROM Orders ORDER BY ord_no Query B SELECT ord_no FROM Orders GROUP BY ord_no ORDER BY ord_no In Orders tabel, ord_no is varchar type and has duplicate. Here it’s a composite key with a identity column. May I know which query is better? How could we check the query performance […]

bulk insert works in ssms but not in other applications

Context: SQL Server 2005 I have a simple proc, which does a bulk load from an external file. ALTER proc [dbo].[usp_test] AS IF OBJECT_ID(‘tempdb..#promo’) is not null BEGIN DROP TABLE #promo END CREATE TABLE #promo (promo VARCHAR(1000)) BULK INSERT #promo FROM ‘\\server\c$\file.txt’ WITH ( –FIELDTERMINATOR = ”, ROWTERMINATOR = ‘\n’ ) select * from #promo […]

How To Retun Output Paramater From Stored Procedure

I would like to have the total row count as the output parameter, which will later be accessed from C#. This query gives an error – Invalid object name ‘TBL’ ALTER PROCEDURE [dbo].[GetOrderDetails] @SortOrder nchar(10), @ColName nvarchar(20), @StartIndex int, @PageSize int, @RecordCount int output AS BEGIN SET NOCOUNT ON; DECLARE @Query nvarchar(MAX), @Params nvarchar(MAX) SET […]

Desceding ordering dosn't work

This stored procedure doesn’t perform descending order of data I don’t know why ! BEGIN SET nocount ON; WITH ordereddata AS (SELECT *, rn = Row_number() OVER ( ORDER BY articleid ) FROM articles) SELECT * FROM ordereddata WHERE rn <= @UpperBound AND rn >= @LowerBound ORDER BY articleid DESC END this is the result […]

replace getdate with column name

select convert(date,GETDATE()) as [date], convert(varchar(8), convert(time, ‘2013-06-03 00:00:00.000′)) as [Time] I want to get date and time seperately and I use above method. I’m trying to replace getdate() with my table column name: select convert(date,dbo.tbltimetable.AppDate) as [date], convert(varchar(8), convert(time, dbo.tbltimetable.AppDate)) as [Time] this doesn’t seem to help: I get an error saying’multipart identifier could not […]

Update SQL LocalDB without overwriting data

I’m developing a site, with a LocalDB via Visual Studio 2013. I have a number of stored procedures and these may change in the future, but there will definitely be some new additions as the project develops. The live site/server will be collecting new date on a regular basis and therefore will become out […]

get only the max create date from the same table

I want to get data from a table, but only select the latest or the max CreateDate here’s my query : select Username, Password,Status,FirstLogin,CreateDate,UpdateDate,MasterID,FullName,Address,tm.Email,TempEmail,VerificationCode,PhoneNo,TempPhonoNo,TempPassword,LastSignIn,TempAccount,bod,ktp,Gender,CustType,VIP from UserData t inner join ( select Email, max(CreateDate) as MaxDate from userdata group by Email ) tm on t.Email = tm.Email and t.CreateDate = tm.MaxDate but unfortunately I just get […]

AFTER INSERT, UPDATE Trigger won't update when importing data

I ideally want this trigger for a table where the clients’ data get imported from an Excel spreadsheet. In some cases the names are all CAPITALIZED and I have the Function that updates them to lower case except first letter, first letter after apostrophe and etc. which I’m invoking it in the trigger. Following is […]

How to make foreign key as NULLABLE in SQL?

CREATE TABLE [M].[SocialInfo] ( [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(), [MemberId] UNIQUEIDENTIFIER DEFAULT newid(), [GroupId] UNIQUEIDENTIFIER DEFAULT newid(), [NewsURL] VARCHAR(200) NULL, CONSTRAINT [FK_SocialInfo_Member] FOREIGN KEY ([MemberId]) REFERENCES [M].[Member]([Id]), CONSTRAINT [FK_SocialInfo_Group] FOREIGN KEY ([GroupId]) REFERENCES [M].[Group]([Id]) ) How can I make both FK nullable? I’m unable to get the syntax?

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.