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

I generally do backups/restores of data and log files
using Enterprise Manager. I am just learning.
What does the following statement do?
BACKUP LOG YourDatabase WITH Truncate_Only
Like if take the backup from Enterprise it creates .bak
files whether the above statement will also create
some .bak files.That command truncates the inactive portion of the transaction log without
taking a transaction log backup. Good from reducing the size of the
transaction log, but since no backup was taken you will not be able to
restore any data that was added, deleted, or updated if those data
modifications where contained in the inactive portion of the transaction
that is truncated.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:3fa501c49ffd$1dc02350$a301280a@.phx.gbl...
> I generally do backups/restores of data and log files
> using Enterprise Manager. I am just learning.
> What does the following statement do?
> BACKUP LOG YourDatabase WITH Truncate_Only
> Like if take the backup from Enterprise it creates .bak
> files whether the above statement will also create
> some .bak files.

Easy question

I generally do backups/restores of data and log files
using Enterprise Manager. I am just learning.
What does the following statement do?
BACKUP LOG YourDatabase WITH Truncate_Only
Like if take the backup from Enterprise it creates .bak
files whether the above statement will also create
some .bak files.
That command truncates the inactive portion of the transaction log without
taking a transaction log backup. Good from reducing the size of the
transaction log, but since no backup was taken you will not be able to
restore any data that was added, deleted, or updated if those data
modifications where contained in the inactive portion of the transaction
that is truncated.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:3fa501c49ffd$1dc02350$a301280a@.phx.gbl...
> I generally do backups/restores of data and log files
> using Enterprise Manager. I am just learning.
> What does the following statement do?
> BACKUP LOG YourDatabase WITH Truncate_Only
> Like if take the backup from Enterprise it creates .bak
> files whether the above statement will also create
> some .bak files.

Easy Insert Statement ... Hopefully!

In Oracle I have an insert statement where Im selecting from one table and isnerting into another. One of the columns Im inserting is the current date/time

Ex.

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',SYSDATE,'1'

from REGION;

In SQL Server 2005 I cannot seem to find the right fit for the SYSDATE command: Please help

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',?,'1'

from REGION;

use GETDATE()

If you can use the same datetime for every row I would do this because it is much faster:

DECLARE @.curdate DATETIME
SET @.curdate = GETDATE()

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',@.curdate,'1'

from REGION;

|||AND not subject ot time variations.|||

Great!!! Exactly what I was looking for

Thanks

Easy IF statement (Beginner)

Im not really keen on If statements, so im trying to figure out how to write an if statement in a matrix. I want it to list the values that begin with 4's as 2004 the values that start with 5's as 2005 and the values that begin with 6's as 2006. How do i do this? Whats the IF statement code, i know how to put one option. but now all.

=IIF(Fields!ICNNo.Value=Left(1),2004,) ? I know thats wrong..

Please Help.

One way you could do this is using nested IIF statements. Like this:

=iif(left(Fields!ICNNo.Value, 1) = "4", "2004",
iif(left(Fields!ICNNo.Value, 1) = "5", "2005",
iif(left(Fields!ICNNo.Value, 1) = "6", "2006", "OtherValue")))

Hope this helps.

Jarret

|||It worked!!! THANKS ALOT!!!! It worked perfectly!

Dynamicly setting events

I have the following code and I'm trying to set an event dynamicly in the foreach statement...

SqlConnection

conn =newSqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);SqlCommand select =newSqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);SqlDataAdapter da =newSqlDataAdapter(select);DataSet ds =newDataSet();

conn.Open();

da.Fill(ds);

conn.Close();

Table table =newTable();

foreach (DataRow drin ds.Tables[0].Rows)

{

TableRow tr =newTableRow();for (int i = 0; i < dr.ItemArray.Length; i++)

{

TableCell tc =newTableCell();

tc.Text = dr.ItemArray.GetValue(i).ToString();

tc.BackColor =

Color.White;if (dr.ItemArray.GetValue(8).ToString() =="COMPLETE")

{

tc.BackColor =

Color.BlueViolet;

}

tr.Cells.Add(tc);

}

table.Rows.Add(tr);

}

foreach (TableRow tablerowin table.Rows)

{

TableCell newtc =newTableCell();Button mybutton =newButton();

newtc.Controls.Add(mybutton);

mybutton.Text =

"details";

//set the event here

tablerow.Cells.Add(newtc);

}

Panel1.Controls.Add(table);

DataBind();

I am assuming you have added a button dynamically, and you want to create a handler for this button.

First you need to create a function to do what you want, e.g.

Sub myButtonClicked (sender, args)

....

End sub

Then you need to tell asp.net that this is the function to run when the button is clicked. In your code, before you add the button to the tablecell, write:

AddHandler myButton.click, AddressOg myButtonClicked

A little warning though - this may not work. This is because you have to create any dynamic controls everytime the page load. Have a read ofthis page which should help you.

|||

thanks for the response.

The solution that I have found is to add the eventhandler and specify what you want to do init. In this case redirect towww.google.com :

protected

void Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);SqlCommand select =newSqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);SqlDataAdapter da =newSqlDataAdapter(select);DataSet ds =newDataSet();

conn.Open();

da.Fill(ds);

conn.Close();

Table table =newTable();

foreach (DataRow drin ds.Tables[0].Rows)

{

TableRow tr =newTableRow();for (int i = 0; i < dr.ItemArray.Length; i++)

{

TableCell tc =newTableCell();

tc.Text = dr.ItemArray.GetValue(i).ToString();

tc.BackColor =

Color.White;if (dr.ItemArray.GetValue(8).ToString() =="COMPLETE")

{

tc.BackColor =

Color.BlueViolet;

}

tr.Cells.Add(tc);

}

table.Rows.Add(tr);

}

foreach (TableRow tablerowin table.Rows)

{

TableCell newtc =newTableCell();Button mybutton =newButton();

newtc.Controls.Add(mybutton);

mybutton.Text =

"details";

mybutton.Click +=

newEventHandler(mybutton_Click);

tablerow.Cells.Add(newtc);

}

Panel1.Controls.Add(table);

DataBind();

}

void mybutton_Click(object sender,EventArgs e)

{

Response.Redirect(

"http://www.google.com");

}

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 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 generate where condition

If we have a pre-defined SQL statement without "where conditions", how do I dynamically insert the where conditions into it (by user input or whatever)? Is there any free tool available for carrying out this kind of work?

Regards,

Ricky.

Ricky,

If the Where condition must be added to a Sql statement say within a Stored Procedure,

then you could use dynamic SQL statements.

declare @.sql varchar(255),

Select @.sql ="Predefined SQL statement" + "The where condition which can be input to the SP"

Exec @.sql

Sandoty

|||

Be cautious when you use the dynamic sql queries. It may cause two preaches,

1. SQL Injection

2. Permission issues

SQL Injection,

Your code is allowing the end user to inject the SQL command on your own code. So they can vulnerable your database be cautious here. Try to eliminate the comments (-- or /**/) as your input. You can control it on the UI itself. There are lots more practices are there. Google it for SQL Injection. You will get better idea.

Permission Issues,

Dynamic sql will expect the required permission on the source tables, if current user doesn’t have the permission on the table level (but on the SP), the dynamic SQL won’t work.

You can achieve the filter conditions from the SPs or prepared query itself. If the intention is reducing the code & time to achieve this task Pls take care the above issues.

|||

One more point about dynamic queries is the performance issues.

Each time you want to run them the sql command has to be recompiled in order to get an execution plan.

This may be positive in some situations. But most of the time the best way is to set your application to run with specific queries.|||

You can find a lot of information about this theme in the following articles.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

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

AMB

Dynamically determining the SQL statement?

Hello all,

I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.

Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.

With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).

Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).

BTW...currently using SSRS 2005 + ASP.NET 2.0.

Thanks for your help!!

-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>

Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...

I guess the *best* way to set the command text and datasource from the front end is the use parameters?

Are there any other possible solutions?

Thanks!

-Brian

Dynamically Creating Columns

I have an .aspx page where users can select columns from a table. I want to pass the Select statement to a SQL Reporting Services report, probably as a stored procedure, and open the report with the records for the columns selected as an Excel file. My problem is that I am not sure how to dynamically create the column headings and values in a table. Any thoughts on how to achieve this? Thanks!

HI,amiejoye:

When do you want to dynamically create the column heading? In report data retrieving or other period?

|||

if you want to dynamically add headers then you can use code section in reporting service.

Else you can use drag and drop the fileds(clumn name ) that is comin from the dataset

|||

I want to create the column headings and retrieve the records when I click Submit from the .aspx page. Basically, when I click Submit, that's how I want the age to open, with the data from the Select statement I created.

|||

I think that should not be aproblem because the vb code section in the reporting service depends on the select query only

dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Dynamic WHERE statement

If I pass a variable to @.Cost_Center that is 'SMS', 'SMP' OR 'ALL'
'ALL' will return records that have either 'SMS' or 'SMP' in them.
Now I am using an if statement that has the entire SQL statement in the body
of the
conditional.
I tried this
Where
IF (@.Cost_Center = 'All')
Begin
code...
and i.cost_center in ('SMS','SMP')
End
ELSE
Begin
code...
and i.cost_center = @.Cost_Center
End
Thanks
JimYou might want to check out the following article for some ideas:
http://www.sommarskog.se/dyn-search.html
Anith|||Thank you
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23glqcpZBFHA.4072@.TK2MSFTNGP10.phx.gbl...
> You might want to check out the following article for some ideas:
> http://www.sommarskog.se/dyn-search.html
> --
> Anith
>|||SELECT ...
FROM Foobar
WHERE cost_center = @.my_cost_center
OR cost_center
IN (CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMP' ELSE '' END,
CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMS' ELSE '' END));

Dynamic views

Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>

Dynamic use of "inserted" and "deleted" in a trigger

Hello,
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:

> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:

> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:

> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>

Dynamic T-SQL steatment

How can i build a T-SQL statement dynamically?

Vivek S

Do you mean within SSIS? You should use an expression: http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=

If you mean to be used in (e.g.) a sproc then you're on the wrong forum. Try the T-SQL forum.

-Jamie

|||i have declared a variable of type string and initialised it with a T-SQL statement. The sql statement size is around 5000 characters. To have some run time modifications, i have used a script task and initialised the SQL to the variable so that it can take the changes at run time. i have used a DataflowTask within which accessing the variable for the SQL. But when i execute the ssis pkg it throws an error at the dft validation level as VS_ISBroken.|||

What type of component is using the SQL statement?

What component is failing?

What is the error?

Please supply more pertinent information otherwise its a bit dificult to be of assistance.

As an aside, you really should consider using an expression rather than a script task to dynamically build your SQL statement.

-Jamie