|
Even you can go ahead with OUTPUT.INSERTED
e.g.
insert into Activitity
output inserted.ActivitityId into TODo(ActivitityId)
values(1,'somerecord')
select * from TODo
Output:
TODOId ActivitityId
1 1
Niladri Biswas
|
|
|
|
|
Now that's cool, I was not aware INSERTED worked for an ordinary insert statement, I thought it was only for triggers. Now to go play, I wonder is UPDATED works as well
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys,
I have created a trigger for auditing ..
I want to keep track of changes made to each field by using a counter.
can you let me I go about achieving this task....
modified on Wednesday, January 13, 2010 5:34 AM
|
|
|
|
|
If I understand your questions correctly, why dont you create an audit table and always add a record before each update.. so you want an update triger[^]
|
|
|
|
|
I am developing an ecommerce website, aimed at selling electronic products.
So for storing the product details I use a product table in which I have the fields for storing the product specifications. My problem is how to include the specifications of different products like motherboard, mouse, Monitor in a single table (product table).
How should I design the product table to hold the specifications of different products. Suggest me a good database design for this problem.
|
|
|
|
|
You can have two tables like this:
In product table:
ProductId
ProductName
ProductType
and in ProductTypeCodes:
ProductTypeCode
ProductType
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
You need to think about the data - I'd get plenty of examples and see how they look - what attributes define a particular product for example.
A possible suggestion is two tables: one to hold products and the other to hold attributes. This is a simplified example of this approach.
Product Table
-------------
ProductID
ProductName
ProductType
Attribute Table
---------------
AttributeID
ProductID
Attribute
Value
Examples:
Products
1, LG XYZ, TFT Monitor
2, AX-999, Motherboard
Attributes
1, 1, Screen Size, 22
2, 1, Colour, Black
3, 2, CPU Type, AMD
4, 2, PCI Sockets, 3
You need to link the tables. Also this is probably too simplistic - think about the units for attributes. Some will numeric others strings.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
|
DECLARE @Current_SQL varchar(500)
DECLARE @QueryResult int
....
SET @QueryResult = EXEC(@Current_SQL)
I get error: "Incorrect syntax near the keyword 'EXEC'."
My @Current_SQL query like "Select Count(*) From MyTable Where ID = 1"
Haw can I get return value from EXEC metod.
|
|
|
|
|
You could use an output-parameter, somewhat similar to this;
declare @Current_SQL nvarchar(500)
declare @result bigint
set @Current_SQL = 'select @result = count(*) from [MyTable] where [ID] = 1'
execute sp_executesql @Current_SQL, N'@result int output', @result output Good luck 
I are Troll 
|
|
|
|
|
|
Hi to All Forum members
I am stuck in finding duration and Net duration in the following procedure
I also adding image Url for better under standing of my requirement.
Please go to this Url ..
http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^]
Following is my T-Sql Query...
Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number],
IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),'Not Assigned') as [Actual OutShedding Time],
IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,'Not Assigned') as [Actual Inshedding Time],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,'00:00')as [Duration],
IsNull(DT.RestHours, 00) as [ShiftChangeoverTime],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, '00:'+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),'00:00') as [Net Duration]
From Fleet F
Left Outer Join Duties DT on F.DutyID = DT.ID
Inner Join Depot D on DT.DepotId = D.ID
Inner Join Vehicles V on V.ID = F.VehicleID
Welcome for your valuable suggestions.
Vishnu
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
Hi
Actually Duration is coming right in this query and the issue is Net Duration is not coming right.
Vishnu Narayan Mishra
Software Engineer
|
|
|
|
|
hi all,
i want to build a query but not succesful
i have two table named A and B
A table has field id(N), title(Varchar)
B table has field mid(N), id(N)
so i want to id, title from A table and count of id from B table
let me explain with data
Table A data
1 title1
2 title2
3 title3
Table B data
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
so i want to following result
id title count
1 title1 4
2 title2 3
3 title3 2
Please suggest how can build this query?
|
|
|
|
|
Here's the idea how to do this:
SELECT A.id, A.title, COUNT(*) as cnt
FROM A left join B on A.id = B.id
GROUP BY A.id, A.title
|
|
|
|
|
SELECT A.id, A.title, S.total
FROM A
LEFT OUTER JOIN
(
SELECT id, COUNT(id) as total
FROM B
GROUP BY id
) S on S.id = A.id
ORDER BY A.id ASC
|
|
|
|
|
Hi
I have a select query.The test team told me that the query is weak.So how can I make a query stronger in SQL Server.
Thanks
Denny
|
|
|
|
|
Let it lift weights...
Wout Louwers
|
|
|
|
|
They probably refer to strong and weak typing.
Here's[^] an explanation, the rest you can google.
|
|
|
|
|
Go back to the Test team and ask them to explain in detail why they believe that your query is 'weak'.
|
|
|
|
|
Hi,
Can someone please advise me on what to do with this issue.
I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M
My issue is this:
I have 2 lines on the same day.
2010/01/05 1HOUR 30MINUTES 90MINUTES
2010/01/05 2HOURS 20MINUTES 140MINUTES
How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals?
2010/01/05 230MINUTES
My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
|
|
|
|
|
You need to remove GrossHours and GrossMinutes from your select statement and group by.
|
|
|
|
|
Hello,
I'm attempting to query a list of tables from an SQLite3 database and have come across the query PRAGMA table_info("table_name") in the SQLite docs.
Here is my code:
OdbcConnection DbConnection;
OdbcDataReader DbReader;
DbConnection = new OdbcConnection("DSN=" + odbcConnName);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("PRAGMA table_info(bandwidth_records)");
DbReader = DbCommand.ExecuteReader();
while (DbReader.Read())
{
Trace.WriteLine(DbReader["type"].ToString());
}
DbReader.Close();
DbConnection.Close();
However, DbReader.Read() is not returning true. Very weird.
Any ideas?
Thanks,
Matt
|
|
|
|
|
I am not sure what was wrong with your case, but a test on my system returned the correct info.
Are you sure you want to use the PRAGMA commands in your programs? Based on the documentation future versions of SQLite will not guarantee the backward compatability for these PRAGMA commands.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
