|
|
Hi Arun thnx for being interested.
As i told u i want the SQL insert query syntax to insert data into two linked tables.
lets say this is the syntax to insert data into one table
INSERT INTO TABLE1(column1, column2)
VAlUES (x, y)
whats is the syntax to insert data into two linked tables or more.
thanx for ur help
yasso
|
|
|
|
|
Why can't you use 2 separate insert statements?
Regards,
Arun Kumar.A
|
|
|
|
|
i used them but its gave a syntax error. Look this is what am going to do:
I am inserting data from one form into two linked tables "Student" and "Grade" linked together by the Grade_ID column which is a primary key in the Grade table and a forgien key in the Student table. After i insert the data i go to the database to see that the primary key
in the Grade table is inserted and its ok but the forgien key in the student table is still null. How can i link them together so that the forgien key in the Student table can take the same value as that in the grade table.
yasso
|
|
|
|
|
How can you say that the foreign key column will be updated automatically
when the primary key column is updated?
I think you have to use two insert statements like:
insert into Student(GradeID,OthercolumnName) values(GradeValue,OthercolumnValue)
insert into Grade(GradeID,OthercolumnName) values(GradeValue,OthercolumnValue).
Regards,
Arun Kumar.A
|
|
|
|
|
I execute the following:
EXEC sp_fulltext_catalog 'DescriptionsOfProducts', 'create'
EXEC sp_fulltext_table 'tblProducts', 'create', 'DescriptionsOfProducts', 'PK__tblProducts__50FB042B'
EXEC sp_fulltext_column 'tblProducts','ProductName','add'
EXEC sp_fulltext_column 'tblProducts','ProductDescription','add'
EXEC sp_fulltext_table 'tblProducts','activate'
EXEC sp_fulltext_catalog 'DescriptionsOfProducts', 'start_full'
On my local pc it works fine... populates the fulltext catalog just great.
however on my web server, it creates everything correctly, however has an item count of 0???
i cannot find whats going on! the databases are identical, both in structure, indexes and data.
any ideas?
|
|
|
|
|
what SQL server is this??
L.W.C. Nirosh.
Colombo,
Sri Lanka.
|
|
|
|
|
Hi All,
I am planing to use SQL server fulltext search and xml to store data of a
ecommerce site (mainly its products).. the site is having about 20 million
products.. I am going to store all the product in xml type.. and I need to
search them extensively.... The site going to have lots of users .. I am
assuming about 500000 page views per hour.. but all these will not hit the
search button .. (we use cache to reduce the database hits as much as
possible)
In a scenario like this .. is it a good approach to use xml ?
Is the SQL fulltext search can perform well here, in simple term is the SQL
server the choice here??
Please give me your honest  answers I need to make a choice here..
Also if you suggest me to go with SQL please attempt to assist me with some links where
I can find tips to improve the SQL server performances to hold on to this heavy load..
L.W.C. Nirosh.
Colombo,
Sri Lanka.
|
|
|
|
|
i m inserting unicode character from c# to sql server 2005 but sql server is not supporting the unicode format.
|
|
|
|
|
Please do not cross post.
I replied you in the C# forum
|
|
|
|
|
Really, is the column an NVARCHAR? are all the parameters and variable types that hold the character an NVARCHAR?
|
|
|
|
|
and is your database collation is set properly?
|
|
|
|
|
Hello Subai,
Datatype should be Nvarchar or Ntext. While inserting from C# or VB.Net prefix N with variables.
i.e. sp name is like - 'usp_add_customer_details' this sp accepts company id and customer name(this is multilanguage).
While calling that SP you should write code like
dim strSql as string
strSql = 'usp_add_customer_details '"& strCompId &"', N'"& strCustName &"''
Note : here N is prefixed with strcustName.
I hope this will work for you.
Thanks & Regards,
Kumar
|
|
|
|
|
Hi, I have 3 tables below. How can I query total payment and prod_principal group by date?
This is what I have, but it results incorrect, why? Help please, anyone? Thanks.
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1,
SUM(invoice_details.prod_principal) AS sum2
FROM shift INNER JOIN
invoice ON shift.id = invoice.shift_id INNER JOIN
invoice_details ON invoice.id = invoice_details.invoice_id
GROUP BY DAY(shift.date), MONTH(shift.date), YEAR(shift.date)
ORDER BY year1, month1, day1
shift invoice invoice_details
----- -------- ---------------
id id invoice_id
date shift_id prod_id
payment prod_principal
|
|
|
|
|
Try this:
select DAY(A.date) AS day1,
MONTH(A.date) AS month1,
YEAR(A.date) AS year1,
SUM(A.payment) AS sum1,
SUM(A.prod_principal) AS sum2 From
(select * from shift,invoice,invoice_details
where shift.id=invoice.shift_id and invoice.id=invoice_details.invoice_id
)
A Group by DAY(A.date), MONTH(A.date), YEAR(A.date)
ORDER BY year1, month1, day1
Regards,
Arun Kumar.A
|
|
|
|
|
I tried it, but the result is exactly the same as my original query.
the reason i know it's wrong is because sum1 is not the same as queried from the following:
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
SUM(invoice.payment) AS sum1
FROM shift, invoice
WHERE shift.id = invoice.shift_id
GROUP BY DAY(shift.date),
MONTH(shift.date),
YEAR(shift.date)
ORDER BY year1, month1, day1
maybe i should specify these:
- there are many invoices in one shift (having the same shift_id)
- there are many invoice_details in one invoice (having the same invoice_id)
Please help, thanks.
|
|
|
|
|
I do not know much about invoice.
Can U provide 4(or minumum) rows of data for each table
and the output U exactly need.
May be some other person, who know the solution but cannot understand Ur need
will help U.
Regards,
Arun Kumar.A
|
|
|
|
|
here are some examples. i hope it's enough. thanks.
shift: id date
-- ----------
1 05/01/2007
2 05/02/2007
invoice: id shift_id payment
-- -------- -------
1 1 100
2 1 250
3 2 375
4 2 210
5 2 333
invoice_details: invoice_id prod_id prod_principal
---------- ------- --------------
1 101 30
1 156 55
2 258 180
3 147 100
3 268 225
4 251 60
4 369 35
4 158 88
5 125 100
5 395 50
5 158 65
5 228 33
5 358 20
the query i want:
day1 month1 year1 sum1 sum2
---- ------ ----- ---- ----
1 5 2007 350 265
2 5 2007 918 776
note: 350 = 100+250 (total payment on 05/01/2007)
918 = 375+210+333 (total payment on 05/02/2007)
265 = 30+55+180 (total prod_principal on 05/01/2007)
776 = 100+...+20 (total prod_principal on 05/02/2007)
|
|
|
|
|
Use this for the moment, untill you find better solution.
SELECT DAY(shift.date) AS day1,
MONTH(shift.date) AS month1,
YEAR(shift.date) AS year1,
C.SumPay AS sum1,
C.SumPri AS Sum2
FROM shift ,
(select shift_id,sum(payment) "SumPay" ,sum(A.sumPrincipal) "SumPri" from invoice B,
(select invoice_id,sum(prod_principal) "sumPrincipal" from invoice_details group by invoice_id) A
where B.id=A.invoice_id group by B.shift_id) C
where shift.id=C.shift_id
Regards,
Arun Kumar.A
|
|
|
|
|
thanks a lot, you've saved me hours of stressing out
|
|
|
|
|
I want to develop an application that use SQL statment. I am currently learning SQL. I want to know which one is better to use, XPO or SQL. DevExpress does provide a Persistent Object component. I am not an experienced database programmer. I am just learning SQL. Which one will you recommend for me to use between xpo and sql statement.
|
|
|
|
|
XPO is an object/relational mapper. The purpose of these is to abstract away the details of loading data into your object and persisting it, so you can concentrate on OO business logic rather than database plumbing.
But if you're fairly unfamiliar with SQL I would learn that first. Then later on you will gain an appreciation of XPO (or similar object/relational mappers).
Kevin
|
|
|
|
|
I ve written a stored procedure to fetch data from a table. Here i m getting an error "Syntax error converting datetime from character string.". Please guide me.
//////////////////////////////////////////////////////////////////////////
CREATE PROCEDURE ic_get_processeddata
(
@frmDate DateTime,
@toDate DateTime,
@empType varchar(20),
@ein varchar(15)
)
as
declare @sqlStr as varchar(255)
set @sqlStr =''
set @sqlStr='select EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus from ic_process where frmDate='+@frmDate+' and toDate='+@toDate
if(@empType !='')
begin
set @sqlStr = @sqlStr + ' and empType=' + @empType
end
if(@ein !='')
begin
set @sqlStr = @sqlStr + ' and EIN='+@ein
end
set @sqlStr = @sqlStr + (' order by empName,empType')
print(@sqlStr)
exec @sqlStr
GO
thanx in advance
|
|
|
|
|
Use CAST or CONVERT to convert @frmDate and @toDate to string, before you try to concatenate them.
|
|
|
|
|
Wouldn't this be a better solution:
CREATE PROCEDURE ic_get_processeddata
(
@frmDate DateTime,
@toDate DateTime,
@empType varchar(20),
@ein varchar(15)
)
AS
SELECT EIN,empName,empType,I2C,callAnswered,I2CPer,USLeaves,CBF,SOS,SOI,FCR,Bonus
FROM ic_process
WHERE frmDate = @frmDate AND toDate = @toDate
AND (empType = @empType OR @empType = '')
AND (EIN = @ein OR @ein='')
ORDER by empName,empType
This way the @toDate remains a datetime type.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
