Easy Transact SQL Question

I'm a novice with SQL Querying, and can't figure out an update command.
My SELECT statement is as follows:
SELECT * FROM table1, table2
WHERE table1.keyfield = table2.keyfield and table2.field is not null
How do I convert this to an update statement on a field in table1 while
maintaining the restriction based on the field in table2?
Thanks for the help!Cindy Mikeworth wrote:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
> Thanks for the help!
For example:
UPDATE table1
SET col1 = 1234
WHERE EXISTS
(SELECT *
FROM table2
WHERE table2.keycol = table1.keycol
AND table2.col IS NOT NULL) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First learn that rows are NOT anythign like records, nor are columns
anything like a field. it is VITAL to have the right mindset in SQL
SELECT *
FROM Table1, Table2
WHERE table1.keyfield = table2.keyfield
AND table2.field IS NOT NULL;
You don't do it at all!! One of the MANY differences between a field
and column is that a column can have constraints on it. An SQL
programmer woudl have done this in the DDL (do you know what DDL is? If
not, you are sooooo screwed).
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Could you program from what you posted? HOW?!
If you have a key, in ANY table is BY DEFINITION NOT NULL, so your|||Cindy Mikeworth (CindyMikeworth@.newsgroups.nospam) writes:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
UPDATE table1
SET field = ...
FROM table1, table2
WHERE table1.keyfield = table2.keyfield
and table2.field is not null
This uses a non-standard extension of the UPDATE statement that is
proprietary to SQL Server and Sybase. As long as one is careful that
the join produces a unique value for the row to update, this is a very
practical method, not the least because it's so easy to transform a
SELECT into an UPDATE.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||UPDATE t1
SET t1.column1 = t2.value
FROM table1 t1
INNER JOIN table2 t2
ON t1.keyfield = t2.keyfield
WHERE t2.field IS NOT NULL

easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp
Hi
"C" wrote:

> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John
|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dp"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi
"C" wrote:

> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
>
>
>
> But this won't give you any particular row. It will be a "frankenstein" r
ow
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-

Easy select distinct question.....

Dear Reader and Posters,
I have a quick question about the use of a select statement.
I have this big db tbl and I would like to be able to select rows of
data using (i think) distinct. Here is the tricky part... I would only
like to apply the distinct to one column in the table not the all the
columns. I have been reading around and people have been reluctant to
elaborate on other peoples questions that are alike mine because they
say that it makes no sense to do what I want to do because if there
are two rows of data and everything except one column is the same then
what says which one gets selected. My answer is that I don't care. Is
this possible and if so can any one point me in the right direction?
Thank,
dpHi
"C" wrote:
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
> Thank,
> dp
If you don't care what the other values are then just take then why don't
you just take the maximum of the other values and group by the column you
want to be distinct?
If this has to be a specific row then use the maximum of the Primary Key and
get the row that corresponds to that e.g.
SELECT m.PK, m.Col1, m.COl2
FROM ( SELECT MAX(PK) AS PK, Col1 FROM MyTable GROUP BY Col1 ) s
JOIN MyTable m ON m.PK = s.PK
John|||"C" <devprog@.newfound.biz> wrote in message
news:1171701419.221382.33560@.k78g2000cwa.googlegroups.com...
> Dear Reader and Posters,
> I have a quick question about the use of a select statement.
>
> I have this big db tbl and I would like to be able to select rows of
> data using (i think) distinct. Here is the tricky part... I would only
> like to apply the distinct to one column in the table not the all the
> columns. I have been reading around and people have been reluctant to
> elaborate on other peoples questions that are alike mine because they
> say that it makes no sense to do what I want to do because if there
> are two rows of data and everything except one column is the same then
> what says which one gets selected. My answer is that I don't care. Is
> this possible and if so can any one point me in the right direction?
>
If your table t has a single-column key, say 'id', and you want one row for
each distinct value of a column c, then something like:
select *
from t
where t.id
(select min(id) from t group by c)
David|||Hi dp,
how should that look like, onyl distincing to one column ? If you want
one row per Distinct value, you will have to aggregate the other
columns with aggregate expressions like:
SELECT A,MON(B),MIN(C)
FROM SoneTable
Group BY A --This is the Distinct Column
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> Hi dp,
> how should that look like, onyl distincing to one column ? If you want
> one row per Distinct value, you will have to aggregate the other
> columns with aggregate expressions like:
> SELECT A,MON(B),MIN(C)
> FROM SoneTable
> Group BY A --This is the Distinct Column
>
But this won't give you any particular row. It will be a "frankenstein" row
made up of bits of many rows.
David|||On Feb 17, 1:09 pm, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> "Jens" <J...@.sqlserver2005.de> wrote in message
> news:1171733568.343342.33660@.q2g2000cwa.googlegroups.com...
> > Hi dp,
> > how should that look like, onyl distincing to one column ? If you want
> > one row per Distinct value, you will have to aggregate the other
> > columns with aggregate expressions like:
> > SELECT A,MON(B),MIN(C)
> > FROM SoneTable
> > Group BY A --This is the Distinct Column
> But this won't give you any particular row. It will be a "frankenstein" row
> made up of bits of many rows.
> David
Thanks all I will try things out... cdp|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uBvFpcsUHHA.5100@.TK2MSFTNGP06.phx.gbl...
>.
> It will be a "frankenstein" row made up of bits of many rows.
Every once in a while there is real insight in this forum!
Now is it a 'Young Frankenstein', the mature 'Frankenstein' or
perhaps just the 'Ghost of Frankenstein'?
-:)

Easy Question Hopefully

This happens in both SQL Server 2000 and SQL Server 2005

Database name: STAPLE

Owner: Scott

Table: ScottTab

I could login as Scott and state: Select count(1) from ScottTab; and results would be returned because the table ScottTab was associated to Scott and I did not have to prefix the table

It was backed up and restored to another SQL Server.

I tried the same query and am receiving the error table does not exist. I have tried to run: exec sp_changedbowner 'SCOTT' and exec sp_change_users_login 'Change_One','SCOTT','SCOTT' as well and get errors

Please let me know the proper steps I need to take to re-associate the new sql server engine to the restored database. The user SCOTT was setup the same way on both machines

When you say the user was setup the same way, does that include the SID? If you have SQL logins on two servers and want to be able to move databases between them with backup/restore or detach/attach, you should synchronize the logins by making sure they have the same SID value. The users in a database are matched to logins by SID, not by name.

Both sp_addlogin (2000) and CREATE LOGIN (2005) have an optional SID parameter that will help solve your problem.

|||There are other databases that already exist on the server so the user can not be dropped or re-created at the global level. I am also restoring databases from different servers. Any other ways to get around this problem?|||User exists at the database level and is mapped to a sql login. It's certainly okay to drop a user from a database without affecting other databases.

You're indeed seeing orphan user. Sp_change_users_login 'Update_One' should fix it.
Perhaps, you should run Sp_change_users_login 'Report' to see if there is an orphan.sql

easy question about a select

hi, I think this is an easy question
I have something like this in my DB
Amount1 Amount2 Date
100 100 01/01/2005
100 230 01/02/2005
200 0 01/03/2005
444 555 01/04/2005 <--I wanna get this row
666 0 01/05/2005
I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
example it'd be (444 555 01/04/2005)
So I have made
SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
but I dont know how to specify the last Date that match the conditions befor
e
any sug
thksHello Kenny M.,
Try
SELECT TOP 1
[Amount1],
[Amount2],
[Date]
FROM myTable
WHERE
[A,ount1] <> 0
AND [Amount2] <> 0
ORDER BY
[Date] DESC
Aaron Weiker
http://aaronweiker.com/

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2
> <> 0
> but I dont know how to specify the last Date that match the conditions
> before
> any sug
> thks
>|||select max(Date)
from MyTable
where Amount1 <> 0 and Amount2 <> 0
Adi|||Hi Kenny,
You could use the following :
select amount1,amount2,mydate
from
mytable where mydate = (select max(mydate) as MaxDate from mytable where
amount1 <> 0 and amount2 <> 0)
Cheers,
Andy
"Kenny M." wrote:

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions bef
ore
> any sug
> thks
>
> --
>|||If amount is always 0 or positive I would do..
select max(date) where amount1 >0 and amount2>0
This might give you a better execution plan than using <>
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions
before
> any sug
> thks
>
> --
>|||If one row for each date then you can write like this
Select Amount1,Amount2 from Table1 where [Date] = (Select Max([Date]) from
Table1 where Amount1<> 0 and Amount2 <> 0)
Hth
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions
before
> any sug
> thks
>
> --
>|||For sure you meant:
...
where amount1 > amount2 or amount1 < amount2
AMB
"Wayne Snyder" wrote:

> If amount is always 0 or positive I would do..
> select max(date) where amount1 >0 and amount2>0
> This might give you a better execution plan than using <>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.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
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:465C1809-0618-4F69-9406-09D35664871C@.microsoft.com...
> before
>
>|||thks all of you guys
"Kenny M." wrote:

> hi, I think this is an easy question
> I have something like this in my DB
> Amount1 Amount2 Date
> 100 100 01/01/2005
> 100 230 01/02/2005
> 200 0 01/03/2005
> 444 555 01/04/2005 <--I wanna get this row
> 666 0 01/05/2005
> I want to get only the last Row where Amount1<>0 and Amount2<>0 in my
> example it'd be (444 555 01/04/2005)
> So I have made
> SELECT Amount1 , Amount2 FROM myTable WHERE Amount1 <> 0 AND Amount2 <> 0
> but I dont know how to specify the last Date that match the conditions bef
ore
> any sug
> thks
>
> --
>

Easy query problem

Hello Experts-
I would like a sproc to return a 1 row select with results based on the
results of what it has found. For example say the following table were
created by the sp:
ID Name Dept
23 A 4
38 B 4
117 C 4
if the sproc could tell me which of these columns contained unique values
that would be great:
ID Name Dept
null null 4
In other words if all values in a column are the same, return that value,
otherwise return null.Select (Select Case When Count(*) = 1
Then Min(ID) Else Null End
From Table T
Group By ID) As ID,
(Select Case When Count(*) = 1
Then Min(Name) Else Null End
From Table T
Group By Name) As Name,
(Select Case When Count(*) = 1
Then Min(Dept) Else Null End
From Table T
Group By Dept) As Dept
"Coffee guy" wrote:

> Hello Experts-
> I would like a sproc to return a 1 row select with results based on the
> results of what it has found. For example say the following table were
> created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique values
> that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that value,
> otherwise return null.|||Sorry - messed that u.. Here's the right one...
Select (Select Case When Count(Distinct ID) = 1
Then Min(ID) Else Null End
From Table T) As ID,
(Select Case When Count(Distinct Name) = 1
Then Min(Name) Else Null End
From Table T) As Name,
(Select Case When Count(Distinct Dept) = 1
Then Min(Dept) Else Null End
From Table T) As Dept
"CBretana" wrote:
> Select (Select Case When Count(*) = 1
> Then Min(ID) Else Null End
> From Table T
> Group By ID) As ID,
> (Select Case When Count(*) = 1
> Then Min(Name) Else Null End
> From Table T
> Group By Name) As Name,
> (Select Case When Count(*) = 1
> Then Min(Dept) Else Null End
> From Table T
> Group By Dept) As Dept
>
> "Coffee guy" wrote:
>|||Coffee guy wrote:
> Hello Experts-
> I would like a sproc to return a 1 row select with results based on
> the results of what it has found. For example say the following
> table were created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique
> values that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that
> value, otherwise return null.
<snort>
What makes you think this query is "Easy"?
Try this:
CREATE TABLE #temp (
ID int,
Name varchar(10),
Dept int)
insert into #temp
select 23,'A',4
union all select 38,'B',4
union all select 117,'C',4
SELECT
(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
ID
END FROM #temp) ID
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
Name
END FROM #temp) Name
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
Dept
END FROM #temp) Dept
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks to both, harder than I thought ;)
"Bob Barrows [MVP]" wrote:

> Coffee guy wrote:
> <snort>
> What makes you think this query is "Easy"?
> Try this:
> CREATE TABLE #temp (
> ID int,
> Name varchar(10),
> Dept int)
> insert into #temp
> select 23,'A',4
> union all select 38,'B',4
> union all select 117,'C',4
> SELECT
> (SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
> ID
> END FROM #temp) ID
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
> Name
> END FROM #temp) Name
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
> Dept
> END FROM #temp) Dept
> drop table #temp
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||I think this is a bit simpler than what's been posted so far. Assuming
no NULLs in any of the columns,
select
case when min(ID) = max(ID) then min(ID) else null end as ID,
case when min(Name) = max(Name) then min(Name) else null end as Name,
case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
from #temp
Steve Kass
Drew University
Coffee guy wrote:
>Thanks to both, harder than I thought ;)
>"Bob Barrows [MVP]" wrote:
>
>|||Duh! I definitely did not give this enough thought.
Thanks,
Bob
Steve Kass wrote:
> I think this is a bit simpler than what's been posted so far. Assuming no
> NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Steve,
Yes, Elegant !
"Steve Kass" wrote:

> I think this is a bit simpler than what's been posted so far. Assuming
> no NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
>|||And if your fingers are tired, these are a tiny bit shorter,
but they're basically the same thing:
select
nullif(min(ID),nullif(min(ID), max(ID))) as ID,
nullif(min(Name),nullif(min(Name), max(Name))) as Name,
nullif(min(Dept),nullif(min(Dept), max(Dept))) as Dept
from #temp
select
case min(ID) when max(ID) then min(ID) end as ID,
case min(Name) when max(Name) then min(Name) end as Name,
case min(Dept) when max(Dept) then min(Dept) end as Dept
from #temp
SK
CBretana wrote:
>Steve,
>Yes, Elegant !
>
>"Steve Kass" wrote:
>
>sql

Easy query help

I want to perform the following query but i dont know how :

select count(num) from X

and I want the X to be a table from the following query :

select table from bla bla bla . . .

This cannot be done by :select count(num) from (select table from bla bla bla) !

How can i build it ? I have stuck !which database is this? because the method you described should work

what was the actual error message you got?

perhaps you could also show real table and column names so we could see if there is an obvious syntax error|||I get a incorrect synbtax :

The query is
select count(distinct timestamp) from (select cf.table from general_cfg cf, can c where cf.x1 = c.y1);

This should have worked ?|||no, it shouldn't've

the subquery produces an intermediate table result which is used as the data source for the outer query's FROM clause, yes?

well, that intermediate table result has a single column, called "table"

thus, the outer table cannot count the distinct values of a column called "timestamp" because that column doesn't exist|||Are we trying to sneak up on dynamic SQL here?

-PatP|||And is there a solution to this problem at last ...???|||yes there is!!!!!!!!!!!!|||select count(num) from (select table from bla bla bla)
Add an alias name for the table:select count(num) from (select num from table from bla bla bla) AS tmp

Easy Q.. Query without temp table

How do I rewrite this query so that I do not use a temp table?

SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

Use a derived table:

SELECT *

FROM (SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID ) as TEMP1
WHERE MINR1 = MAXR1

|||Thanks!sql

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

Dynamically selecting table in DTS

Hello,

I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.

Thanks

MV

Are you trying to do this in a bulk insert task or data pump? If you're using the bulk insert task, you can set a connection and the properties of the bulk insert task to be dynamic by using the dynamic properties task. If you're using the data pump, it's slightly more complex. You'll have to use an ActiveX Script task to also set the properties of the destination table to concatenate whatever database you'd like to load to the table name. It's been a while since I looked at the code but it isn't too bad (about 4 lines or so). OR, you can just convert to SSIS :).

Brian

|||

Thanks

i got the way

Dynamically Selecting columns

Hi all,

I need a select statement. In that select statement's column_list, I dont need a column, in such a way that column can be among a list of the available columns in the table. That means suppose there are column names with names A,B,C,D,E,F. I need to write a select statement in such a way,

select column_Name from table_Name

In the above column_name could be any among A,B,C,D,E,F, that could be decided programmatically.

Please help me. Very Urgent.

Chandu

Hi,

Try this.

Code Snippet

DECLARE @.SQL nvarchar(500)

DECLARE @.column nvarchar(100)

DECLARE @.table nvarchar(100)

set @.column = '*'

set @.table = 'table'

SET @.SQL = 'SELECT ' + @.column + ' FROM ' + @.table

EXEC sp_executesql @.SQL

regards,

Janos

|||

Chandu,

Using dynamic SQL, as Janos demonstrated, is the way to accomplish your task.

However, there are significant security cautions about using dynamic SQL. This article by Erland (and a couple from Microsoft) will go into more detail about using dynamic SQL and how to protect against most of the hazards.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

Dynamically selected columns with column switch option

Hi,
my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. This is easier
explained with the following statements one can paste into Query
Analyzer:
declare @.sql nvarchar(4000), @.cols nvarchar (500)
set @.cols = '
v1 = case when sel=1 then
v1a else v1b end,
v2 = case when sel=1 then v2a
else v2b end
'
create table ##temp_test (
v1a int,
v1b int,
v2a varchar(15),
v2b varchar(15),
sel bit
)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (1, 2, 'a1', 'b1', 0)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (3, 4, 'a2', 'b2', 1)
set @.sql = 'select ' + @.cols + 'from
##temp_test'
exec sp_executesql @.sql
drop table ##temp_test
Result:
v1 v2
-- --
2 b1
3 a2
This fits bots requirements: @.cols (which is stored in a table in the
real world application) holds the column names to be selected, and it
holds it in a way which also enables to switch between the a and b
version of the columns via the CASE statements. The performance is OK.
My problem: the real world query is a lot more complex, ##temp_test is
actually a table with about 80 columns and there are a lot of other
tables joined in.
Since I am doomed to not use more than 4000 characters for the dynamic
sql part (not using sp_executesql results in a huge performance
penalty in my scenario) this approach in the end works find without
all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
@.cols variable length will grow by factor 2-3, typically from around
1000 - 2000 chars to about 2000 - 6000 chars.
That's the end of the sp_executesql approach.
Maybe our design is wrong in the first place. We have tried to
alternate between the columns by using UNION to a View that contains
the alternate column, but ended up in quiet a performance hit with
more complex queries plus sometimes the SORT function would not work
anymore and the like.
Maybe I can shorten the
v1 = case when sel=1 then
v1a else v1b end
part somehow? Or in the best case someone knows a similar approach...
just better.
TIA for any comments!
Regards
DChttp://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"DC" <dc@.upsize.de> wrote in message
news:5b7bac12.0502250228.4d94de6c@.posting.google.com...
> Hi,
> my requirement is to dynamically select certain columns from a table
> and depending on a flag some columns must be swapped. This is easier
> explained with the following statements one can paste into Query
> Analyzer:
>
> declare @.sql nvarchar(4000), @.cols nvarchar (500)
> set @.cols = '
> v1 = case when sel=1 then
> v1a else v1b end,
> v2 = case when sel=1 then v2a
> else v2b end
> '
> create table ##temp_test (
> v1a int,
> v1b int,
> v2a varchar(15),
> v2b varchar(15),
> sel bit
> )
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (1, 2, 'a1', 'b1', 0)
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (3, 4, 'a2', 'b2', 1)
> set @.sql = 'select ' + @.cols + 'from
> ##temp_test'
> exec sp_executesql @.sql
> drop table ##temp_test
>
> Result:
> v1 v2
> -- --
> 2 b1
> 3 a2
> This fits bots requirements: @.cols (which is stored in a table in the
> real world application) holds the column names to be selected, and it
> holds it in a way which also enables to switch between the a and b
> version of the columns via the CASE statements. The performance is OK.
> My problem: the real world query is a lot more complex, ##temp_test is
> actually a table with about 80 columns and there are a lot of other
> tables joined in.
> Since I am doomed to not use more than 4000 characters for the dynamic
> sql part (not using sp_executesql results in a huge performance
> penalty in my scenario) this approach in the end works find without
> all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
> @.cols variable length will grow by factor 2-3, typically from around
> 1000 - 2000 chars to about 2000 - 6000 chars.
> That's the end of the sp_executesql approach.
> Maybe our design is wrong in the first place. We have tried to
> alternate between the columns by using UNION to a View that contains
> the alternate column, but ended up in quiet a performance hit with
> more complex queries plus sometimes the SORT function would not work
> anymore and the like.
> Maybe I can shorten the
> v1 = case when sel=1 then
> v1a else v1b end
> part somehow? Or in the best case someone knows a similar approach...
> just better.
> TIA for any comments!
> Regards
> DC|||I don't understand why you want to use dynamic SQL for this. Where is
the string in @.cols generated from? You say it comes from a table but
why put it in a table at all? Why can't you define views for these
different views of the data?
David Portas
SQL Server MVP
--|||>> my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. <<
You might want to get any book on **basic software engineering** and
look up the concept of cohesion in a code module.|||Thank you! Can you recommend a good book on this topic? I probably
focussed too much on the loose coupling aspects? I support your claim
for the art of programming, but then again: it's only T-SQL. Hacky
stuff! Wouldn't a real programmer avoid messing around with SQL anyway?|||Thanks David, views or pre-generated stored procs are an option. We are
in the process of upgrading a life application though, and wanted to
integrate this additional requirement (the CASE req.) with the least
effort possible.|||Thank you Vinod, great article with a wealth of dynamic sql info. Does
not provide an exact solution, but pointed out that using EXEC shoud
not make as much of a difference as I experienced.|||> Wouldn't a real programmer avoid messing around with SQL anyway?
Sure. Why go looking for a new peg when you can just keep bashing that
square one into a round hole? After all, tables are only arrays aren't
they?
David Portas
SQL Server MVP
--|||On 25 Feb 2005 05:52:23 -0800, David Portas wrote:

>Sure. Why go looking for a new peg when you can just keep bashing that
>square one into a round hole? After all, tables are only arrays aren't
>they?
Hi David,
A "real" programmer - isn't that a guy who needs nothing but 8 switches
and 8 LED's to program the 'puter? Who needs all that modern assembler
language mnemonics anyway, huh?
(Hmmm - am I showing my age now <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Can you recommend a good book on this topic? <<
Any of the classic by Yourdon, Constantine, DeMarco or Gane & Sarson
are a good place to start.
They would avoid dynamic SQL and do the order of presentation of the
columns in the front end. I have become a fan of the idea that one
team in the shop handles the database and writes all the SQL for
everyone, and the application developers make requests to that team.

Dynamically select tables

Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:

> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet

Dynamically select column

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+'='+ @.VoteID + ' WHERE (PlayerID = '+ @.PlayerID +')'
EXEC(@.SQLStatement)
GO

I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int

The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
UPDATE
 myTable
SET
 Boot3 = @.VoteID
WHERE
 PlayerID = @.PlayerID
GO

Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!Try this:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber varchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+ ' = ' + CAST(@.VoteID as VARCHAR(10)) + ' WHERE (PlayerID = '+ CAST(@.PlayerID as VARCHAR(10)) +')'
EXEC(@.SQLStatement)
GO

Casting the integers to varchars so they can be concatenated into the larger string.

Hope this helps,
John|||John:

Brilliant! Thanks; works beautifully.

JP

dynamically return rows 1-10, 11-20, 20-30 using select statement

Hi,
i would like to know if it is possible for me to get back the rows i am
interested in rather than all the records matching the query.
Right now my query returns first 20 items, 30 items or the first 500 items.
But i am having a problem with timeout when i query all 5000 items.
Can i get back only 10 items at a time in the order 1-10, 11-20, 20-30, etc.
Thanks
-SreeEssentially you are talking about paging your results into manageable
chunks'
Check out this article:
http://www.fawcette.com/dotnetmag/2...pf
.asp.
It is an implementation in .NET, but pay attention to the SQL portion of it.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"sree" <ss@.aa.com> wrote in message
news:r9b3c.89293$PR3.1286538@.attbi_s03...
> Hi,
> i would like to know if it is possible for me to get back the rows i am
> interested in rather than all the records matching the query.
> Right now my query returns first 20 items, 30 items or the first 500
items.
> But i am having a problem with timeout when i query all 5000 items.
> Can i get back only 10 items at a time in the order 1-10, 11-20, 20-30,
etc.
> Thanks
> -Sree
>

dynamically Hide/show columns

Hi,
I need to display columns of user choice in report.
User has to select columns,those columns only will get displayed in report.
Any idea '
Thanks ManjushaThis is not possible in the current release of the product.
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> Hi,
> I need to display columns of user choice in report.
> User has to select columns,those columns only will get displayed in
report.
> Any idea '
> Thanks Manjusha|||Hmm,
You could define a report parameter (showColumnA) of type boolean and
hide/display that column based on the user selection. Had to create a report
with similar requirement (hide/show columns) .
Email me back if you wish further help with this.
Regards,
--
Noel H
"Ravi Mumulla (Microsoft)" <ravimu@.microsoft.com> wrote in message
news:%23cMyJxIWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> This is not possible in the current release of the product.
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
> news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> > Hi,
> > I need to display columns of user choice in report.
> > User has to select columns,those columns only will get displayed in
> report.
> > Any idea '
> >
> > Thanks Manjusha
>|||You could also dynamically choose the columns/order to display by using this syntax:
=Fields (Parameters!FirstFieldToShow.Value).Value
rather than
=Fields!FieldName.Value
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Noel H" <dot.net@.sliNnOgSsPhAoMt.co.nz> wrote in message
news:Ogq145JWEHA.2952@.TK2MSFTNGP09.phx.gbl...
> Hmm,
> You could define a report parameter (showColumnA) of type boolean and
> hide/display that column based on the user selection. Had to create a report
> with similar requirement (hide/show columns) .
> Email me back if you wish further help with this.
> Regards,
> --
> Noel H
> "Ravi Mumulla (Microsoft)" <ravimu@.microsoft.com> wrote in message
> news:%23cMyJxIWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > This is not possible in the current release of the product.
> >
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Manjusha" <Manjusha@.discussions.microsoft.com> wrote in message
> > news:ADFECD07-3750-439C-A50E-418860C184BE@.microsoft.com...
> > > Hi,
> > > I need to display columns of user choice in report.
> > > User has to select columns,those columns only will get displayed in
> > report.
> > > Any idea '
> > >
> > > Thanks Manjusha
> >
> >
>

Dynamically generate SqlDataSource

Hi, how I do create SqlDataSource in thecode-behind,setting Select SQL along with its parameters and, finallyautomically generating Del, Update, Insert SQL.

Is there a way to achieve this goal as it would in VS designer?

Thanks,

Ricky.

It is possible to create the TSQL code and wrapper code automatically by a variety of means. I use TSQL scripts to do the generation from within Query Analyser.

Which version of SQL Server are you targetting?

|||

Hi, I use SQL SERVER 2005

Thanks,

Ricky.