|
Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.
|
|
|
|
|
My bad - missed that fact that its a SQlLite question. Nice article by the way.
|
|
|
|
|
Hy to All;
I want to use two insert statement in one stored procedure
I have Two Tables "Activitity" (ActivitityId as a primery key)
and "TODo" Second Table (TODOId as a primery) and (ActivitityId as a foriegn key) ....
So when a value insert into "Activitity" Table ,I want to insert its primery key into "TODO" Table as Foriegn key in a single Stored procedure...
So for this purpose I want to use @@indentity variable
how can I use @@indentity variable in my stored procedure....
|
|
|
|
|
if you have a column set as identity you can use SCOPE_IDENTITY()
DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()
|
|
|
|
|
So store the @@Identity or Scope_Identity() in a variable directly after inserting the activity record and use it in the second insert for todo, there you have your relationship
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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.
|
|
|
|
|