Embedded select query?

What I'd like to do is use the result of one query as the input for
another. eg:

INSERT INTO foo VALUES (
(SELECT id FROM people WHERE name = "bar"),
10,
'foobar'
) WHERE
id = 1;

Problem is, MSSQL wants a scalar value. Is there a way around this?
Can someone tell me the correct syntax for what I want to do, or is this
something that will have to be done outside SQL?

Err. Hope I've been clear. Thanks for any help you folks can give.Don't use the VALUES clause if you are INSERTing values from a query. It's
also good practice always to specify the column names in an INSERT
statement.

INSERT INTO foo (col1, col2, col3)
SELECT id, 10, 'foobar'
FROM people
WHERE name = 'bar';

I'm not sure what the final WHERE clause in your code was meant to be so
I've left it out. You can't have a WHERE clause on an INSERT statement.

--
David Portas
SQL Server MVP
--|||Eek. It's been a long day I guess. There wasn't supposed to be a where
on that :) But you've answered my question none the less. Thanks.

FYI, I didn't bother with the column specifications for the sake of
simplicity.

Thanks all the same!

David Portas wrote:
> Don't use the VALUES clause if you are INSERTing values from a query. It's
> also good practice always to specify the column names in an INSERT
> statement.
> INSERT INTO foo (col1, col2, col3)
> SELECT id, 10, 'foobar'
> FROM people
> WHERE name = 'bar';
> I'm not sure what the final WHERE clause in your code was meant to be so
> I've left it out. You can't have a WHERE clause on an INSERT statement.

EM SQL Scripts

The EM can produce SQL Scripts of tables/indexes/constraints/default values etc from a database. When doing so, the create statement for tables and the alter statements for constraints (including the default values) are separated. In effect, the create table-statement will only create the table; keys, default values, indexes are changes on the table. My question is: when saving the results in separated files as a basis to continue working on, would you take the effort of changing the create-tables statements so that will also fill in the default values or keep it separate?I've never bothered to mangle the generated scripts... I've never seen any need for it.

-PatP|||Pat: Thing is, there's a db here without any of the tables/views/sp's stored on disk making it, imho, an unhealthy basis to continue building on. So I've scripted the db layout, views and all that allowing me to create a dev. I'm just considering whether I should take the effort or not; when changes are made to the db/tables I'll process it into the create-scripts as well.
I wonder though, do you have any create scripts that create all the tables in a database, do you keep it separate from the indexes/constraints? I don't want to start a flame-thing on "who's responsible", just wondering if you'd take the trouble?|||I'm not really clear what you are asking, so this response may seem way off base.

If you are talking about the option that causes Enterprise Mangler to script the PK/FK/Default/Check Constraints as part of the table versus scripting the table "raw" then creating ALTER TABLE statements to add the constraints, I usually don't care how it gets done. If there was some compelling reason to set it one way or the other, then I would. Since the net effect after running the whole script will be the same, I've never bothered to monkey with it... Either way works fine for me, and produces the same results in the end.

-PatP

Eliminating Duplicates

I have a table with a field called PID. This field can have duplicate
values in it. I have 2 other fields called CAU and SUF. I may have 3 rows
with the same PID and same CAU, but the SUF field when concatenated with the
2 aforementioned fields make the row unique.
I want to know how to select 1 of the 3 rows but not the other 2. In
otherwords I need 1 row for each SUF. Hopes this makes sense and I would
appreciate any help.Try the following query. Work from the inner select out.
CREATE TABLE OneSUF
(
PID INT NOT NULL,
CAU INT NOT NULL,
SUF INT NOT NULL,
Description VARCHAR(255) NOT NULL
)
INSERT INTO OneSUF VALUES (1, 2, 3, '1, 2, 3')
INSERT INTO OneSUF VALUES (1, 2, 4, '1, 2, 4')
INSERT INTO OneSUF VALUES (1, 2, 5, '1, 2, 5')
INSERT INTO OneSUF VALUES (1, 3, 1, '1, 3, 1')
INSERT INTO OneSUF VALUES (1, 3, 2, '1, 3, 2')
INSERT INTO OneSUF VALUES (1, 3, 3, '1, 3, 3')
INSERT INTO OneSUF VALUES (1, 4, 3, '1, 4, 3')
INSERT INTO OneSUF VALUES (1, 4, 4, '1, 4, 4')
INSERT INTO OneSUF VALUES (1, 4, 5, '1, 4, 5')
INSERT INTO OneSUF VALUES (1, 4, 6, '1, 4, 6')
SELECT SUF3.SUF, SUF3.CAU, SUF3.PID, SUF3.Description
FROM OneSUF AS SUF3 INNER JOIN
(
SELECT SUF1.SUF, SUF1.CAU, MIN(SUF1.PID) AS PID FROM OneSUF AS SUF1 INNER
JOIN
(SELECT SUF, MIN(CAU) AS CAU FROM OneSUF GROUP BY SUF) AS SUF2
ON SUF1.SUF = SUF2.SUF AND SUF1.CAU = SUF2.CAU
GROUP BY SUF1.SUF, SUF1.CAU) AS SUF4
ON SUF3.SUF = SUF4.SUF AND SUF3.CAU = SUF4.CAU AND SUF3.PID = SUF4.PID
ORDER BY 1,2,3
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:%23OzmAYOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I have a table with a field called PID. This field can have duplicate
> values in it. I have 2 other fields called CAU and SUF. I may have 3
rows
> with the same PID and same CAU, but the SUF field when concatenated with
the
> 2 aforementioned fields make the row unique.
> I want to know how to select 1 of the 3 rows but not the other 2. In
> otherwords I need 1 row for each SUF. Hopes this makes sense and I would
> appreciate any help.
>

Efficiency in inserting Null Values into fields which allow nulls.

Hi,
I have fields in my table which allow nulls. Is it efficient to not insert anything (the field automatically shows up as null in this case) and leave or store some value into it. The field is a smallint field?

Thanksheres some info from BOL :

Allowing Null Values
The nullability of a column determines if the rows in the table can contain a null value for that column. A null value, or NULL, is not the same as zero (0), blank, or a zero-length character string such as ""; NULL means that no entry has been made. The presence NULL usually implies that the value is either unknown or undefined. For example, a null value in the price column of the titles table of the pubs database does not mean that the book has no price; NULL means that the price is unknown or has not been set.In general, avoid permitting null values because they incur more complexity in queries and updates and because there are other column options, such as PRIMARY KEY constraints, that cannot be used with nullable columns.

If a row is inserted but no value is included for a column that allows null values, Microsoft® SQL Server? 2000 supplies the value NULL (unless a DEFAULT definition or object exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it. The value NULL should not be placed within quotation marks because it will be interpreted as the character string 'NULL', rather than the null value.

Specifying a column as not permitting null values can help maintain data integrity by ensuring that a column in a row always contains data. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.

Note Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.
|||To be honest I'm not sure. Once you've made the decision to use NULLs (and there are lots of pros/cons to that) I doubt there is much in it. However, if you have a choice *and* you're really trying to eek out the last drop of performance then I'd avoid NULLs. Having said that I'm sure there are thousands of other places to look for better optimisations before you go here.

Editing the subject line for email subscriptions

Is there a way to get the subject line to report the values of parameters?
For example, "Report Date Range is {start date param} to {end date param}"Hi !
This can easily be achived by adding a reference in the sql string for the
subscription. Example for the parameter Product_Name:
Product_Name AS Subject
In the sample below the YearLastMonth, ForrigeMnd and FileName would be
selectable to the subject field as well..
Sample SQL Select:
SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
ForrigeMnd as Mnd,
'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
VARCHAR) AS FileName
FROM dbo.MAN_RPT_ButikkInfo_V
WHERE (EMail IS NULL)
Regards
Frank
"toolman_2000" wrote:
> Is there a way to get the subject line to report the values of parameters?
> For example, "Report Date Range is {start date param} to {end date param}"|||OK, but how do you get those values into the subject line on the subscription
set up page in Report manager? I want to use a parameter value the same way
the global values are used.
"frankiebody" wrote:
> Hi !
> This can easily be achived by adding a reference in the sql string for the
> subscription. Example for the parameter Product_Name:
> Product_Name AS Subject
> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> selectable to the subject field as well..
> Sample SQL Select:
> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> ForrigeMnd as Mnd,
> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> VARCHAR) AS FileName
> FROM dbo.MAN_RPT_ButikkInfo_V
> WHERE (EMail IS NULL)
> Regards
> Frank
> "toolman_2000" wrote:
> > Is there a way to get the subject line to report the values of parameters?
> > For example, "Report Date Range is {start date param} to {end date param}"|||What Frank refers to can only be done when using Data Driven Subscriptions.
You don't have access to the actual Parameter values, you would need to have
those values in a database table that you read during the execution of the
Data Driven subscription.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> OK, but how do you get those values into the subject line on the
> subscription
> set up page in Report manager? I want to use a parameter value the same
> way
> the global values are used.
> "frankiebody" wrote:
>> Hi !
>> This can easily be achived by adding a reference in the sql string for
>> the
>> subscription. Example for the parameter Product_Name:
>> Product_Name AS Subject
>> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
>> selectable to the subject field as well..
>> Sample SQL Select:
>> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
>> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
>> ForrigeMnd as Mnd,
>> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
>> VARCHAR) AS FileName
>> FROM dbo.MAN_RPT_ButikkInfo_V
>> WHERE (EMail IS NULL)
>> Regards
>> Frank
>> "toolman_2000" wrote:
>> > Is there a way to get the subject line to report the values of
>> > parameters?
>> > For example, "Report Date Range is {start date param} to {end date
>> > param}"|||it is still not clear how to do so.
let's say i have set a data driven subs. that returns all the email
addresses i want to send to something like:
select email,name,accountName from subscribers
where subscribe=1
how do i insert the field accountName into the subject of this mail?
how do i refer to the recordset field in the subject field?
pls show a simple example.
"Daniel Reib [MSFT]" wrote:
> What Frank refers to can only be done when using Data Driven Subscriptions.
> You don't have access to the actual Parameter values, you would need to have
> those values in a database table that you read during the execution of the
> Data Driven subscription.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > OK, but how do you get those values into the subject line on the
> > subscription
> > set up page in Report manager? I want to use a parameter value the same
> > way
> > the global values are used.
> >
> > "frankiebody" wrote:
> >
> >> Hi !
> >> This can easily be achived by adding a reference in the sql string for
> >> the
> >> subscription. Example for the parameter Product_Name:
> >> Product_Name AS Subject
> >>
> >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> >> selectable to the subject field as well..
> >>
> >> Sample SQL Select:
> >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> >> ForrigeMnd as Mnd,
> >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> >> VARCHAR) AS FileName
> >> FROM dbo.MAN_RPT_ButikkInfo_V
> >> WHERE (EMail IS NULL)
> >>
> >> Regards
> >> Frank
> >>
> >> "toolman_2000" wrote:
> >>
> >> > Is there a way to get the subject line to report the values of
> >> > parameters?
> >> > For example, "Report Date Range is {start date param} to {end date
> >> > param}"
>
>|||The data driven subscription is the way to do it. You will have to include
the field you are selecting from the table in your subscription query if you
want to display it in the subject line. When you edit the page for "special
delivery extension settings" in the data driven subscription you will want to
change the 'subject' to select "Get the value from the dataabase" click on
the dropdown and select the field you want to display.
Hope this hleps!
golf4fun
"Granov" wrote:
> it is still not clear how to do so.
> let's say i have set a data driven subs. that returns all the email
> addresses i want to send to something like:
> select email,name,accountName from subscribers
> where subscribe=1
> how do i insert the field accountName into the subject of this mail?
> how do i refer to the recordset field in the subject field?
> pls show a simple example.
> "Daniel Reib [MSFT]" wrote:
> > What Frank refers to can only be done when using Data Driven Subscriptions.
> > You don't have access to the actual Parameter values, you would need to have
> > those values in a database table that you read during the execution of the
> > Data Driven subscription.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "toolman_2000" <toolman2000@.discussions.microsoft.com> wrote in message
> > news:D504C83D-1F98-481E-88B8-F9B9CE9FC8F4@.microsoft.com...
> > > OK, but how do you get those values into the subject line on the
> > > subscription
> > > set up page in Report manager? I want to use a parameter value the same
> > > way
> > > the global values are used.
> > >
> > > "frankiebody" wrote:
> > >
> > >> Hi !
> > >> This can easily be achived by adding a reference in the sql string for
> > >> the
> > >> subscription. Example for the parameter Product_Name:
> > >> Product_Name AS Subject
> > >>
> > >> In the sample below the YearLastMonth, ForrigeMnd and FileName would be
> > >> selectable to the subject field as well..
> > >>
> > >> Sample SQL Select:
> > >> SELECT Butikk_id_Parent, Produkt_id, Produkt_navn,
> > >> YEAR(DATEADD(m, - 1, GETDATE())) AS YearLastMonth,
> > >> ForrigeMnd as Mnd,
> > >> 'Butikk' + CAST(Butikk_id_Parent AS VARCHAR) + '_' + CAST(Produkt_id AS
> > >> VARCHAR) AS FileName
> > >> FROM dbo.MAN_RPT_ButikkInfo_V
> > >> WHERE (EMail IS NULL)
> > >>
> > >> Regards
> > >> Frank
> > >>
> > >> "toolman_2000" wrote:
> > >>
> > >> > Is there a way to get the subject line to report the values of
> > >> > parameters?
> > >> > For example, "Report Date Range is {start date param} to {end date
> > >> > param}"
> >
> >
> >