How to insert the date

Dear all,
I got a fieldA as datetime (length 8).
When I select in query analyzer, it shows the following value
: 2003-10-27 11:36:27.640
now I get fieldA into a ADODB.recordset in VB with fields
rs_A("fieldA")
And I try to update another table with Field (FieldB) Field B
is also datetime with length 8)
with rs_A ("fieldA") i.e. update tableA set FieldB =
rs_A("fieldA")
The final update statement is query analyzer is :
update tableA set FieldB = 2003-10-27 11:36:27.640
The query analyzer complains incorrect syntax near 11:36 . It
cannot be updated.
How can I insert to FieldB with the complete date and time from
FieldA. Is that something like
update tableA set FieldB = '2003-10-27 11:36:27.640' or
update tableA set FieldB = format(2003-10-27 11:36:27.640)
Please help.
Thanks.hon123456 a écrit :
> Dear all,
> I got a fieldA as datetime (length 8).
> When I select in query analyzer, it shows the following value
> : 2003-10-27 11:36:27.640
> now I get fieldA into a ADODB.recordset in VB with fields
> rs_A("fieldA")
> And I try to update another table with Field (FieldB) Field B
> is also datetime with length 8)
> with rs_A ("fieldA") i.e. update tableA set FieldB =
> rs_A("fieldA")
> The final update statement is query analyzer is :
> update tableA set FieldB = 2003-10-27 11:36:27.640
> The query analyzer complains incorrect syntax near 11:36 . It
> cannot be updated.
> How can I insert to FieldB with the complete date and time from
> FieldA. Is that something like
> update tableA set FieldB = '2003-10-27 11:36:27.640' or
> update tableA set FieldB = format(2003-10-27 11:36:27.640)
1) there si no format about DATETIME SQL type storage it is just
DATETIME (in fact two integers)
2) when casting a string to a DATETIME the only format that is universal
is the ISO short DATETIME format which is :
YYYYMMDD HH:MM:SS.nnn
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose after
4) if you want to use exotic formats about DATETIME as string, set your
session dateformat parameter as above :
SET DATEFORMAT { ymd | mdy | dmy | ydm | myd | ymd }
5) prefer always use a explicit CAST like :
SET DATEFORMAT myd
SELECT CAST('03199722 00:11:22.333' AS DATETIME)
...
A +

> Please help.
> Thanks.
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Thanks SQLpro,
For my query : update tableA set FieldB = 2003-10-27
11:36:27.640
Query Analyzer complains incorrect syntax near 11. What can I do to
make this query work?
Thanks.|||Thanks SQLpro,
Sorry to post again. I do not understand your point 3
suggestion
3) your query : "update tableA set FieldB = 2003-10-27 11:36:27.640"
try to make a substract of 2003 minus 10 minus 27 ... and is loose
after
Is that mean I need to change the query as follows:
update tableA set FieldB = 2003-10-27-11:36:27.640
Thanks.|||On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
wrote:

>Is that mean I need to change the query as follows:
>update tableA set FieldB = 2003-10-27-11:36:27.640
Put quotes around the date string, and remove the dash between the
date and time:
update tableA set FieldB = '2003-10-27 11:36:27.640'
Roy Harvey
Beacon Falls, CT|||> Put quotes around the date string, and remove the dash between the
> date and time:
... and better yet, put a T between the date and a time part, making int th
e ISO 8601 format which
isn't dependent on any language setting for the login, etc.
(Hi, Roy! I'm happy to see you here :-) .)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:h3lt42hna2q4odv454dpujnrp1hib49od6@.
4ax.com...
> On 25 Apr 2006 18:06:51 -0700, "hon123456" <peterhon321@.yahoo.com.hk>
> wrote:
>
> Put quotes around the date string, and remove the dash between the
> date and time:
> update tableA set FieldB = '2003-10-27 11:36:27.640'
> Roy Harvey
> Beacon Falls, CT

How to insert resultset from SP into a table? or use Select * From

I tried this (and other variations) in Query Analyzer - which did not work.
select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
'3/13/06', '3/13/06')
Is there a way to Select/insert the resultset from an SP? How to do this?
Thanks,
RichRich,
Create a temporary or permanent table with the same structure as the result
of the sp. Use:
insert into #t1
exec dbo.usp_p1 ...
AMB
"Rich" wrote:

> I tried this (and other variations) in Query Analyzer - which did not work
.
> select * from (exec stp_Report_MonthlyCountHistory_Summary '24', '%',
> '3/13/06', '3/13/06')
> Is there a way to Select/insert the resultset from an SP? How to do this?
> Thanks,
> Rich
>

How to insert resultset from SP into a table? or use Select *

Thank you. Yes, I did see that this is the only way to do it. My objective
is that an SP which I did not create and don't want to modify returns a
dataset that I need to order. So I want to read/write that data into a temp
table so that I can order it.
"Alejandro Mesa" wrote:
> Rich,
> Create a temporary or permanent table with the same structure as the resul
t
> of the sp. Use:
> insert into #t1
> exec dbo.usp_p1 ...
>
> AMB
> "Rich" wrote:
>Rich,
If you can order the resultset at the client side, then do it and you will
be liberating some load from your sql server.
AMB
"Rich" wrote:
> Thank you. Yes, I did see that this is the only way to do it. My objecti
ve
> is that an SP which I did not create and don't want to modify returns a
> dataset that I need to order. So I want to read/write that data into a te
mp
> table so that I can order it.
> "Alejandro Mesa" wrote:
>sql

how to insert records where PK is being violated on some recor

How about this
Insert Into tbl2(ID, fld1, fld2)
Select Id,fld1, fld2 from
tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
Left Join ttbl2 t2 on t1.id = t2.id
where t2.id Is Null
Would this work also? I forgot that I left out another table.
"JT" wrote:

> Begin by writing a query that only selects those rows from [tbl1] that do
> not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
> you may then insert this result into [tbl2]. This may perform better than
> the NOT IN or NOT EXISTS method.
> Insert Into
> tbl2 (ID, fld2, fld3)
> Select
> T1.ID,
> T1.fld2,
> T1.fld3
> from tbl1
> left join tbl2 as T2
> on T2.ID = T1.ID
> where
> T2.ID is null
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
>
>It looks like it would.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00FBAEDE-5940-48DA-806D-5EB176AD10D7@.microsoft.com...
> How about this
> Insert Into tbl2(ID, fld1, fld2)
> Select Id,fld1, fld2 from
> tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
> Left Join ttbl2 t2 on t1.id = t2.id
> where t2.id Is Null
> Would this work also? I forgot that I left out another table.
> "JT" wrote:
>

how to insert into a table using a select from xml

Good Day,

I am passing some XML into a stored procedure:

<answers>
<answer id="60" text="" />
<answer id="65" text="A moderate form of learning disability" />
<answer id="68" text="We will keep ASD checked" />
<answer id="70" text="" />
</answers>

Along with a memberid and questionid.

I was wondering how I can get this into a table

CREATE TABLE [dbo].[Answers]([PrimaryKeyID] [int]NOT NULL,[MemberID] [int]NOT NULL,[QuestionID] [int]NOT NULL,[AnswerID] [int]NOT NULL,[FreText] [varchar](255) COLLATE Latin1_General_CI_ASNULL)ON [PRIMARY]

What I would also like to do is if the text attribute is empty then put a NULL in the FreText field.

I think I am looking for

 
Insert into MyTable (Select @.MemID, @.QuesID,'somexpathforanswer','somexpathfortext'-- if empty then NULLFrom @.MyXML )
  

Any ideas - places to to look - thoughts Aprreciated

Kal

you can try to use OPENXML see example from T_SQL help below

DECLARE @.idoc intDECLARE @.doc varchar(1000)SET @.doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@.idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10), ContactName varchar(20))

how to INSERT from SELECT

why is this syntax incorrect?

INSERT INTO visits (visit_file_no, direct_or_insurance, insurance_company_id, visit_total_amount) VALUES (@.visit_file_no, @.direct_or_insurance, @.insurance_company_id, SELECT item_price FROM price_list WHERE item_code = 'FIRST_VISIT')

I haved all values passed by the windows form but i only need to retrieve the amount from item price table

Jassim:

Try this and see if it works better:

INSERT INTO visits
( visit_file_no,
direct_or_insurance,
insurance_company_id,
visit_total_amount
)
select @.visit_file_no,
@.direct_or_insurance,
@.insurance_company_id,
item_price
from price_list
where item_code = 'FIRST_VISIT'

How to insert data from another table

I want to Insert data from another table, I use the statement:
insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
St)
but it fail.
How can I do?
insert into YearsClass (Years,Class)
Select distinct Years, ClassID from
St
http://sqlservercode.blogspot.com/
"ad" wrote:

> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?
>
>
|||Do this
insert into YearsClass (Years,Class) Select distinct Years, ClassID
from
St
ad wrote:

> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?

How to insert data from another table

I want to Insert data from another table, I use the statement:
insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
St)
but it fail.
How can I do?insert into YearsClass (Years,Class)
Select distinct Years, ClassID from
St
http://sqlservercode.blogspot.com/
"ad" wrote:

> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?
>
>|||Do this
insert into YearsClass (Years,Class) Select distinct Years, ClassID
from
St
ad wrote:

> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?

How to insert data from another table

I want to Insert data from another table, I use the statement:
insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
St)
but it fail.
How can I do?insert into YearsClass (Years,Class)
Select distinct Years, ClassID from
St
http://sqlservercode.blogspot.com/
"ad" wrote:
> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?
>
>|||Do this
insert into YearsClass (Years,Class) Select distinct Years, ClassID
from
St
ad wrote:
> I want to Insert data from another table, I use the statement:
> insert into YearsClass (Years,Class) (Select distinct Years, ClassID from
> St)
> but it fail.
> How can I do?

How to insert a new record (and get it's identity), if a record is not found

What I'm trying to do is write a query (actually it will be a sub-query) that gets the ID of a record using a standard SELECT/WHERE, but if the record is not found then I want to insert a record and return the ID of the inserted record.

So far I've got this (which doesn't work!)

SELECT ISNULL((Select ContactID AS ID FROM [TileManager].[dbo].[Contact] WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs')), (INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs'); SELECT @.@.IDENTITY AS ID))

Any help would be greatly appreciated.

Rob:

Be careful using the @.@.identity function; try SCOPE_IDENTITY() instead. Look it up in the books.

Dave

|||

I don't believe you can embedded the insert statement like that - why aren't you trying something like this

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')
SELECT ID = @.@.IDENTITY

|||

SELECT ID = @.@.identity will almost always work; it can experience problems such as:

-- -
-- Illustrate the difference between @.@.identity and scopy_identity()
--
-- In this example, @.@.identity returns the value of the identity
-- that was inserted by the trigger
-- -
set nocount on

create table dbo.whatA ( what1 integer identity)
go
create table dbo.whatB ( what2 integer identity)
go

insert into whatA default values insert into WhatA default values
insert into whatB default values
insert into whatA default values insert into WhatA default values
insert into whatA default values insert into WhatA default values

--select * from whatA
--select * from whatB
go

create trigger dbo.trI_whatB on whatB
for insert
as
begin
insert into whatA default values
end

go

insert into whatB default values
select @.@.identity as [Incorrect @.@.Identity]
select scope_identity() as [Correct scope_identity()]
select max (what1) as [WhatA Identity] from whatA
select max (what2) as [WhatB Identity] from whatB
go

drop trigger dbo.trI_whatB
go
drop table dbo.whatA
go
drop table dbo.whatB
go

--
-- O U T P U T :
--

-- Incorrect @.@.Identity
-- -
-- 7

-- Correct scope_identity()
-- -
-- 2

-- WhatA Identity
-- --
-- 7

-- WhatB Identity
-- --
-- 2

|||

That's close to what I'm trying to get, but, what I want is the ID of the record if it is found or the ID of the added record if it is not found. The code you've given returns NULL if the record already exists.

Thanks

|||

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')

SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs'

is it acceptable for you to do it as two statements like this?

AWAL

|||

This will do the existance check first and only insert if no record is found...

DECLARE @.ID int

--Get value if existing

SELECT @.ID

FROM [TileManager].[dbo].[Contact]

WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs');

--No value found so insert

IF @.ID IS NULL

BEGIN

INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs');

SELECT @.ID = SCOPE_IDENTITY();

END

--Use ID for something

SELECT @.ID

sql

How to insert 4 records for every one found?

This SQL will insert one record into #Pivot1 for every one
record returned by the SELECT statement:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours,
wr.HoursAvailable
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID = hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
---
What I need to do, however, is to split each of the incoming records up
into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
for each record.
I guess the brute force method would be to just run that snippet of SQL
four times... but is there a "right" way?
--
PeteCresswellOops! I think I may have something:
---
INSERT INTO #Pivot1
(
PersonID,
ProjectID,
Estimate5,
Available5
)
SELECT
wr.PersonID,
ln.ProjectID,
hr.Hours/4,
wr.HoursAvailable/4
FROM (tblWeekReported wr
INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID = ln.WeekReportedID)
INNER JOIN tblHour hr ON ln.WeekReportedLineID =hr.WeekReportedLineID
WHERE
(
(wr.BeginDate=@.BeginDate5) AND
(wr.EndDate=@.EndDate5) AND
(wr.WeekType=2) -- 2=Monthly
);
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
---
Am I on the right track?
--
PeteCresswell|||RE/
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
>INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
"Oops" again: strike that last INSERT - it would
have given us 8 copies of each rec instead of 4...
--
PeteCresswell|||That wouldn't work, you'd end up with far too many records.
--
HTH
Ryan Waight, MCDBA, MCSE
"(Pete Cresswell)" <x@.y.z> wrote in message
news:0k3vqv0fr0s2763bmgfma27re6e4lbglhc@.4ax.com...
> Oops! I think I may have something:
> ---
> INSERT INTO #Pivot1
> (
> PersonID,
> ProjectID,
> Estimate5,
> Available5
> )
> SELECT
> wr.PersonID,
> ln.ProjectID,
> hr.Hours/4,
> wr.HoursAvailable/4
> FROM (tblWeekReported wr
> INNER JOIN tblWeekReportedLine ln ON wr.WeekReportedID =ln.WeekReportedID)
> INNER JOIN tblHour hr ON ln.WeekReportedLineID => hr.WeekReportedLineID
> WHERE
> (
> (wr.BeginDate=@.BeginDate5) AND
> (wr.EndDate=@.EndDate5) AND
> (wr.WeekType=2) -- 2=Monthly
> );
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> INSERT INTO #Pivot1 SELECT * FROM #Pivot1;
> ---
> Am I on the right track?
> --
> PeteCresswell|||On Mon, 10 Nov 2003 13:05:03 GMT, "(Pete Cresswell)" <x@.y.z> wrote:
>This SQL will insert one record into #Pivot1 for every one
>record returned by the SELECT statement:
>...<snip>...
>What I need to do, however, is to split each of the incoming records up
>into 4 inserts to #Pivot1, dividing 'Hours' and 'HoursAvailable' by 4
>for each record.
>I guess the brute force method would be to just run that snippet of SQL
>four times... but is there a "right" way?
I don't know about right way, but here's a couple of ways:
a) after inserting these rows, select them and insert again (thus
doubling them). Then select them and insert them once more (thus
doubling the double, i.e. four rows for each original one)
b) create a temp table, populate with four records, and join to it in
the select part of your statement. A cartesian join (i.e. no criteria
for the join so that all rows from your temp table are selected and
joined with all rows from the rest of your query) will mean that each
row will appear four times in your result set. This would be my
preference, as it involves a single insert (except for the temp table :)
Of course, if you did this regularly enough, you might want to leave the
table of four rows around permanently, ready to join with. Kind of like
the DUAL table in Oracle these days, that provides a hack method for
returning results from functions without referencing a "real" table.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

How to incorporate exec sql describe select list

Hi,

What is the "EXEC SQL DESCRIBE SELECT LIST" equavilent in .NET using MS SQL Server?

Thanks in advance.If this gets the metadata of the result then you can just look at the SqlDataReader object returned by calling ExecuteReader for example. There are many ways to do this depending on your requirements. Check out the .NET SDK on SqlCommand object for some examples.

How to include parameter in WHERE statment of a stored procedure?

I have a stored procedure like:

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000)
AS
BEGIN
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable)

END

My problem is, the locationId will be null or length equals 0 some time, how can I make the statement "table.LocationId in (SELECT id FROM tempTable)" included into the WHERE condition dynamically depends on locationId is null or length equals 0?

Is that possible to do it at Database side? Or I have to do it at code side?

Thank you.

Perhaps something like this:

SELECT t.id

FROM Table t

WHERE ( t.id = @.Id
AND ( t.LocationID in (SELECT ID FROM TempTable)

OR t.LocationID IS NULL

)

)
END

|||

Looks like you want dynamic search capabilities based on the parameters passed. You can take a look at the link below for various options:

http://www.sommarskog.se/dyn-search.html

You can do below in your case:

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and @.LocationId is not null and len(@.LocationId) > 0

UNION ALL

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

Query optimizer will use startup expression filter to evaluate the expression containing @.LocationId at run-time. This will result in only one of the UNION ALL branches being executed. You could use this approach. You can also put this query in a inline TVF and use it.

|||

Thank you for your advise.

For your code, my understanding is you list both situations, either parameter has value or not has value, run both and combine the results. Is that correct?

My question is, if @.LocationId is not null, the condition "(@.LocationId is null or len(@.LocationId) = 0)" will be false, and it will AND with condition "table.LocationId in (SELECT id FROM tempTable)", the result will also be false right? Finally, it will AND with "table.id = @.Id", which will be false again. That will make the result set of

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

be null?

Cause I have about 8-9 parameters need to be passed in, follow your code, I guess the stored procedure will be very long, right?

The link you gave provides a coding way to do the if statement to build the query at database.

Thank you.

|||Try the following:

Code Snippet

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000),
@.Parameter2 INT,
@.Parameter3 NUMERIC(18, 2)
AS
SELECT
table.id
FROM
table
WHERE
table.id = @.Id AND
(table.LocationId in (SELECT id FROM tempTable) OR LEN(ISNULL(@.LocationId, '')) = 0) AND
(table.Value2 = @.Parameter2 OR @.Parameter2 IS NULL) AND
(table.Value3 = @.Parameter3 OR @.Parameter3 IS NULL) etc...

If you're checking VARCHARs, NVARCHARs, CHARs, to see if they're either NULL or have length = 0, put an ISNULL around the parameter and compare the length to 0.

For every other parameter you want to check, compare it to the table value and OR it with a check to see if it's null.

Each of those OR'd NULL checks needs to be in brackets with AND clauses otherwise you'll get strange results.

How to improve this SQL statement?

I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
follow. Thanks.

------------------------
SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
WHERE
(D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
AND
(B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
AND
(A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
AND pLTV >= A.percentage))
AND C.MONTH = D.MONTH
AND C.LOAN_SIZE= B.loan_size
AND C.percentage = A.percentagetotal shot in the dark, please let me know if this works:SELECT DECISION_ID
, DECISION_NAME_ID
, MONTH
, LOAN_SIZE
, percentage
, ADMIN_FEE_HL_ID
, PNTY_ID
, OFFER_RATE_ID
FROM DECISION AS A
WHERE MONTH = (
SELECT max(MONTH)
FROM DECISION
)
AND LOAN_SIZE = (
SELECT max(loan_size)
FROM DECISION
WHERE MONTH = A.MONTH
)
AND percentage = (
SELECT max(percentage)
FROM DECISION
WHERE MONTH = A.MONTH
and loan_size = a.LOAN_SIZE
)
rudy
http://r937.com/|||Hi r937,

Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

Month, Loan_size, Percentage
8, 100, 70
4, 200, 50
6, 150, 80|||did you try it?

i tried it on your sample of 3 and it gave the row with

8, 100, 70

if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

i thought you wanted row integrity

for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

my query will always return a row, as long as there is at least one row in the table

perhaps you did not explain your problem correctly?

i tried to see what your query was attempting to do, but it's seriously messed up

or maybe i totally misunderstood you

rudy|||Hi Rudy,

Sorry it was my mistake. The query is working now since I missed
out something. Thank you very much.

How to improve the performance from joining to an openquery?

I got the query:

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where B = 21

This query is to slow; even the following query is faster.

SELECT *

FROM OpenQuery(ObjName, 'SELECT * FROM A')

The performance from the following is better:

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''Val'')')) as D

FROM [Test]

where B = 21

but how can I do make it dinamic? I mean this does not work!

SELECT A, B, C, (select * from OpenQuery(MD_AS400, 'SELECT * FROM A WHERE ID in (''’ + ID + ’'')')) as D

FROM [Test]

where B = 21

Has someone experience with this?

How does this perform?

Code Snippet

SELECT *

FROM [Test]

left join ( SELECT top 100 PERCENT* FROM OpenQuery(ObjName, 'SELECT * FROM A') as subA ) AS A

on ID = A.ID

where B = 21

|||Can you please tell which table the column B belongs? Please use aliases in your query for readability. There are many ways to optimize the query. For example, retrieve only the columns you need from the remote table. Why are you using SELECT * ? Is it necessary. If column B belongs to A then move that inside the pass-through query. Depending on the remote data source (looks like AS400 here) and the driver, SQL Server may not be able to push predicates to the remote server. There are many interfaces in the OLEDB provider for AS400 that needs to be implemented for SQL Server to do this. Also, if B belongs to table A then why do you need to the LEFT JOIN. You should doing inner join which is what will happen. If these suggestions doesn't work then simply dump the openquery results into a temporary table first and then join with the temporary table instead.|||

B belongs to test and I will end up with a teporal table thanks!

SELECT *

FROM [Test]

left join OpenQuery(ObjName, 'SELECT * FROM A') as A

on ID = A.ID

where Test.B = 21

|||That will work but I can not restrict just the first records!

How to improve the efficience of Sql query ?

now i want to get results from server tables, but i found it is very slow, for example :

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

Thks

You're trying to full outer join three tables and on the join condition is based on computed columns... well that's a query not build for performance...
If you want to improve performance you need to rewrite the query in a way that doesn't uses coalesce on the join condition, you should use tables columns directly and then filter on the where or on the having

Just a final thought: The
Coalesce(T1.Name, NULL) = T2.Name
Coalesce(T1.Name, T2.Name) = T3.Name
conditions may be rewritten as
T2.Name = Coalesce(T1.Name, NULL)
T3.Name = Coalesce(T1.Name, T2.Name)
take a look if this changes the execution plan.
|||

Liu:

Do you filter based on name or are you wanting to process all rows of all tables?

Dave

|||

every one:

i have solved this problem with hash join such as belows:

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer hash join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer hash join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

the time to query changes from 33m to 4s

thks

How to improve performance of the report's display while updating the table?

Hello, I need help to improve the performance of select statments in my repo
rt. My report is selecting (lots of data) from a attendance table, while a t
rigger is inserting more than 4000 rows of data into the table.
Can I use nolock table hints in my report? How can I use it?
For example,
select * from table a, table b, table c, table d
Should I write like this for the nolock when I want only the table b use nol
ock table hints:-
select * from table a, table b with (nolock), table c, table dAriel
Are you sure that these tables have no JOIN's condition?
This is a mistake which most users make when using the old-style JOIN syntax
is not specifying the join condition. The result set of your query is
Cartesian Product of four tables. it would generate every possible
combination of rows between them.
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||As Uri says, make sure you have the correct join syntax. (The example you
posted is incorrect.) But the direct answer to your question is yes
select * From table with (nolock)
You WILL see data in the process of being changed in a transaction for which
the transaction has NOT yet committed ( dirty data)... Also look up in books
on line (readpast).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||Thanks Uri and Wayne.
Yes, my table got join statements within.
select * from table a, table b with (nolock), table c and table d
where a.id = b.id and
b.id = c.id and
c.id = d.id;
// the b table I assume is the attendance table.
uri, did you mean that my result will be wrong after I use the nolock syntax
?
Or you mean it will be wrong in terms of some of the data it linked will be
deleted or updated?
Thanks Again|||Uri was saying that your select has an error in it, but it does not. ( He
probably was just looking quickly.) Each one of the tables is in the where
clause, but you are using an OLD style syntax which is VERY prone to the
kinds of errors that Uri mentioned... Why don't you spend some time getting
used to and using the ANSI style join syntax - you'll be better off ... IE
select * from a inner join b with (nolock) on a.id = b.id
inner join c on b.id = c.id
inner join d on c.id = d.id;
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:8647D87B-C082-485D-8BFF-1B822C33FA8D@.microsoft.com...
> Thanks Uri and Wayne.
> Yes, my table got join statements within.
> select * from table a, table b with (nolock), table c and table d
> where a.id = b.id and
> b.id = c.id and
> c.id = d.id;
> // the b table I assume is the attendance table.
> uri, did you mean that my result will be wrong after I use the nolock
syntax?
> Or you mean it will be wrong in terms of some of the data it linked will
be deleted or updated?
> Thanks Again
>

How to implement this in SSIS

Greetings SQL friends!

I have the following transact SQL code which I want to change to a set of SSIS components.

SELECT blah, blah

FROM PSTAGE..[stage_OFFER_PRICE_DIVIDEND] AS SOPD
LEFT OUTER JOIN PSTAGE..[stage_PRICE_GRP] AS SPG
ON SOPD.PRICE_GRP_ID = SPG.PRICE_GRP_ID
LEFT OUTER JOIN PSTAGE..[stage_type] AS TYP
ON TYP.TYPE_CD=SPG.PRICE_TYPE_TYPE4_CD
and TYP.TYPE_CL_CD = '0017'

I know I can join two data sets using a merge join (left join) but how do I combine a third merge join? Should I be doing this or should I just stick my code in a SQL Task instead?

Your help would be appreciated.

You can do this in SSIS by adding another data source for your third table and adding a second MergeJoin downstream of the first - joining the output of the first merge join to the output of the third data source.

It may be more efficient to push this on to your server using the query you give in a source component, especially as you are performing outer joins.

When doing an outer join in a client such as SSIS, you will pull all the data from the server just to throw some away as unjoined - that's somewhat inefficient use of the server-client transport. It may be more efficient to perform the join in SQL and only pull onto the client data that you will processing further.

Donald

How to implement this case?

Hi,

I want the select result 1 record show 1 image but not use table or list, how could I do?

More...

The select result has 2 columns, "Org" and "Image".

"Org" is orgainzation and "Image" save the image, its data type is image.

After select, the record like this

Org Image

2211 000C10000BB0000B30000A..
2212 1FFFC2AFFFF39FFFF3FFFF..
2213 000C10000BB0000B30000A..
2214 ...
...

Every record show its own image, how can I implement it?

Thanks!

can you give more information!!

i think that you can use the table report items in reporting service

|||

Dear ,

Drop the image control on the report desinger and check how to get he image from the database.

In the image wizard u will get the option to get image from the database.

HTH

from

sufian

How to implement a DropDownList w/o a Table

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

If you want a static set of items in the dropdown list you can define them in the property panel under the property Items you will find a collection, click the "..." and add items with value and text to appear in the dropdown list.

Best regards,
Per Salmi

|||

If you're trying to get the values from the database, you can do something like this:

Sub Page_Load(ByVal senderAs Object,ByVal eAs EventArgs)
If Not Page.IsPostBackThen GetData()End If
 End Sub
 Sub GetData()
Dim connStringAs String
 Dim conAs SqlConnection
Try connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
 con =New SqlConnection(connString)
Dim cmdAs SqlCommand =New SqlCommand()
Dim readerAs SqlDataReader
 cmd.Connection = con
 cmd.CommandText ="EXECUTE dbo.GetDepartmentCode" reader = cmd.ExecuteReaderIf reader.HasRowsThen
 While (reader.Read)
Dim newItemAs New ListItem
 newItem.Text = (reader.Item("DeptCode")).ToString
 newItem.Value = (reader.Item("DeptCode")).ToString
 deptCodeDDL.Items.Add(newItem)
End While
 End If reader.Close()Catch exAs Exception
 Response.Write(ex)
Finally con.Close() con.Dispose()End Try
 Catch exAs ApplicationException
 Response.Write("Could not load the database")
End Try
 End Sub

The ASP.Net side would like this:

<asp:DropDownList ID="deptCodeDDL" runat="server"></asp:DropDownList>

A few things to note though.

In the code I've posted, I use a stored procedure (sproc) to retrieve the data from the database. If you don't what sprocs are, you can read about themhere. Using sprocs is generally considered a better way of accessing the db.

Also, you will of course, have to replace DeptCode with the column name from where you will be getting the values.

Finally, I use a ConnectionString, which is being retrieved from the Web.config file. You should have that in you application folder.

If you don't, given below is a sample:

<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
--><configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString1" connectionString="Data Source=SERVERNAME\SQLSERVER;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 --><compilation debug="false"
</compilation>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 --><authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace.
 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 --></system.web>
</configuration>

You will have to replace SERVERNAME with you server name and DBNAME with your db name.