Multiple INSERT

I have been using a Select and Insert into statement,

INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data)
SELECT @.NewFund_ID, @.NewFund_CountryID, DetailsData_FieldID, DetailsData_Data FROM DetailsData WHERE
DetailsData_FieldID in (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
AND DetailsData_FundID = @.Fund_ID
AND DetailsData_FundCountryID = @.Fund_CountryID

that inserts the result set of a Select statement into a table. In our database for each transaction performed a record is kept

e.g.
INSERT INTO TranDetailsData VALUES (1, GETDATE(), NULL, 1, @.NewFund_ID, @.NewFund_CountryID, 845, @.GroupId);

Is it possble to perform them both in one statement?no because they insert into different tables|||1. use trigger on insert into DetailsData for the second insert (i would use this approach if you must keep track of inserts).
2. there is a special sql code constructions in the latest version of db2 udb, but it seems like you are using ms sql server. may be ms sql server has someting similar, ask in corresponding group.
3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected).

regards,
dmitri|||well thanks for your suggestions.

I worked it out like this by using the data type table to store the data from the selectect statement.

DECLARE @.tblDetailData TABLE(DetailsData_FundID int, DetailsData_FundCountryID varchar(3), DetailsData_FieldID int, DetailsData_Data varchar(512))

INSERT @.tblDetailData SELECT @.NewFund_ID, @.NewFund_CountryID, DetailsData_FieldID, DetailsData_Data
FROM DetailsData WHERE DetailsData_FieldID IN (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
AND DetailsData_FundID = @.Fund_ID
AND DetailsData_FundCountryID = @.Fund_CountryID

INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT * FROM @.tblDetailData

INSERT INTO TranDetailsData(TranSessionID, TranTime, TranSequence, TranType, DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT 1, GETDATE(), NULL, 1, * FROM @.tblDetailData

chuzhoi
"3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected)."

I kinda guessed performing the statement twice would be an in efficient way of going about it.sql

Multiple inner joins on same field or nested query?

Hi,

I'm wondering how I can structure an SQL statement to perform either a multiple join on a single table, or possibly using a sub-query. Basically, I've got one table in which both fields are foreign keys to another table, as follows:

Table #1

employee_id (pk)
employee_name

Table #2
teamleader_employee_id
backup_employee_id


both fields in table 2 need to do a lookup in table 1 to get the name of the actual employee. Do I need to use nested queries to accomplish this? Is it possible to do two inner joins on the same table? (I've tried this, unsuccessfully!) Any help is greatly appreciated!

-PJtwo joins to the same table, using table aliases to distinguish which table the rows are from, and column to distinguish the columns in the result set
select lead.employee_name as teamleader
, bkup.employee_name as backup
from table2
inner
join table1 as lead
on teamleader_employee_id = lead.employee_id
inner
join table1 as bkup
on backup_employee_id = bkup.employee_id|||Thank you very much. Works like a charm!

Multiple fields in where statement

In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.|||This can also be done with a simple join:

Delete oop_test
from oop_test
inner join oop_temp
on oop_test.acct_no = oop_test.acct_no
and oop_test.curr_seq = oop_test.curr_seq
and oop_test.acct_type = oop_test.acct_type

blindman|||I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

THANKS!|||The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

blindman|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman|||Originally posted by blindman
All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman

I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)|||* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)|||Originally posted by ms_sql_dba
* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

I believe that's changed...Where SELECT * is actually optimized to perform better.|||Brett is correct. Select * is optimized, and the columns are not expanded.

blindman|||Originally posted by SSchuler
In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.

To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

DELETE FROM victim WHERE victim_id IN
(SELECT id FROM
table1 A JOIN table2 B USING
A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3
)

(Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)|||A little late, sundial. Read other member's posts first!

blindman

Multiple DECLARE Sections ... why?

Is there any value to having multiple DECLARE sections within a single store
d
proc/statement block? I understand the value of multiple DECLARE sections
that are related to local variable within specific statement blocks.
However, in working with existing SP's, I run across the following on a
regular basis and would like to better understand if this structure provides
any value and/or cost to the proc's effeciency.
Thanks in advance for your insight/feedback.
Example:
CREATE PROC test
AS
DECLARE
@.var1 int,
@.var2 int
DECLARE
@.text1 varchar(8),
@.text2 varchar(8)It makes no difference. SQL is interpreted, so this won't make any
difference at all to the execution of the SP.
It looks like it was done to aid reading the code, all variables of the same
type declare together.
Regards
Colin Dawson
www.cjdawson.com
"Tom B." <TomB@.discussions.microsoft.com> wrote in message
news:74FD5973-B982-4C06-92F9-3DEAB4879109@.microsoft.com...
> Is there any value to having multiple DECLARE sections within a single
> stored
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure
> provides
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>|||Sorry for any confusion, but the example was just that ... a basic
representation of the code that I've seen (actual code doesn't appear to hav
e
much rhyme or reason to the variable groupings). As for its value with
making the code more user friendly to read ... that makes perfect sense.
Thanks for the quick feedback!
"Tom B." wrote:

> Is there any value to having multiple DECLARE sections within a single sto
red
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure provid
es
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>

Multiple datasources

Hi,
I've written a report that uses a matrix to display some data that all
comes from a funky select statement. The report is month based, so
there is a where clause specifying the month in the select
The user now wants to add a Year to Date column. Because of the data
structure of the data i don't believe it is possible to pull the YTD
figure in the same statment.
Is it possible to have one of the columns in a matrix (or any other
control) that is based on a different dataset which takes a parameters
from the matrix row?
I'm guessing theres not, but I just want to make sure, theres alot of
smart people out there :)
Thanks
AndrewWhat you want to do is to investigate subreports. You can embed a subreport
into a column (I have done this). If the subreport returns multiple rows
then that column would go down multiple rows. In your case you will only be
returning a single row. You will want the subreport to be very simple
(obviously) since you are embeding it. Note that the subreport is called for
every row. What I do in this case is hide the report in list view so users
don't see it when selecting a report to run.
To develop a subreport you develop it like a normal report with parameters.
Make sure it works. Drag and drop the report into the column you want (add
an extra column/field that is blank and drop it into it). Then do a right
mouse click on the embeded subreport and select parameters to map the
subreport parameters to the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ajharvey@.gmail.com> wrote in message
news:1114049920.896557.185450@.o13g2000cwo.googlegroups.com...
> Hi,
> I've written a report that uses a matrix to display some data that all
> comes from a funky select statement. The report is month based, so
> there is a where clause specifying the month in the select
> The user now wants to add a Year to Date column. Because of the data
> structure of the data i don't believe it is possible to pull the YTD
> figure in the same statment.
> Is it possible to have one of the columns in a matrix (or any other
> control) that is based on a different dataset which takes a parameters
> from the matrix row?
> I'm guessing theres not, but I just want to make sure, theres alot of
> smart people out there :)
> Thanks
> Andrew
>|||Thanks,
I'll give that a try

multiple counts in a sql statement

How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?

For example

SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"

How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc...

Thanksrefer to your other message, and www.sqlcourse.com|||Got another question related to this. Since I have this part

SQL = "SELECT Totals = (SELECT Count(*) FROM nonconformance WHERE department_id=7), Totals2 = (SELECT Count(*) FROM nonconformance WHERE department_id=1)"

How would I add another statement in here to Sum a different column in the database.

For example

Select Totals = (Select Sum(nc_wafer_wty) FROM nonconformance WHERE department_id = 7) etc...

What I am trying to do is create a two row datagrid with the different "totals" as columns.

Is this possible?|||wouldn't a group by statement be better than actually having where clauses with id's coded into them?|||Yes i realize that. This is just for testing right now, later I will put variables in there place.|||you aren't paying attention. Variables or not.. if you're BINDING the resultset to a control, you're best off retrieving the data correctly.|||You mean GROUP BY department_id?|||yes. That way you won't have to make an extremely long sql statement, and loop to get specific ones.

Multiple connection type query

Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctosuse linked tables to sql server in Access and create your insert query in access.

Multiple conditions in "IF" statement in T-SQL?

Hi All,

Just wondering if anyone can tell me if you can test for multiple
conditions as part of an "IF" statement in T-SQL in SQL Server 2000?

ie something like:

IF @.merr = 1 or @.merr=2
Begin
SELECT statement
UPDATE statement
End

Any help much appreciated!

Much warmth,

M.M Wells (planetquirky@.planetthoughtful.org) writes:
> Just wondering if anyone can tell me if you can test for multiple
> conditions as part of an "IF" statement in T-SQL in SQL Server 2000?
> ie something like:
> IF @.merr = 1 or @.merr=2
> Begin
> SELECT statement
> UPDATE statement
> End

Yes you can.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multiple Columns Map to Different Rows in Join Table

Can a SQL statement be written that returns all of the information in
the Project table and the corresponding FullName for both the
CreatedBy and LastModifiedBy fields? Table structures are below.
A typical JOIN clause seems insufficient because every row in the
Project table maps to 2 rows in the User table. When doing reporting,
I've always avoided this problem by using subreports to do individual
lookups. However, I'm curious to see if there is a more direct
approach using SQL only.
Project
- ProjectNo
- ProjectName
- CreatedByUserNo
- LastModifiedByUserNo
User
- UserNo
- FullName
Your input is greatly appreciated.
Best Regards,
DavidSELECT P.projectno, P.projectname,
C.fullname AS createdbyuser,
M.fullname AS lastmodifiedbyuser
FROM Project AS P
JOIN User AS C
ON P.createdbyuserno = C.userno
LEFT JOIN User AS M
ON P.lastmodifiedbyuserno = M.userno
(untested)
--
David Portas
--
Please reply only to the newsgroup
--

multiple columns in "in" search

Is it possible to put a set of columns into a select statement using "in"

For example:

select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)

This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.

Any ideas?

No. SQL Server doesn't support row value constructors yet. You can use a correlated sub-query instead like:

select count(*) from table1 as b where exists (select * from table1 a

where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4)

|||

Thanks, that solves part of my problem.

What I want to be able to do is to delete using the same (similar) syntax, but from an earlier seperate post I cant prefix a table in a delete query!?!?

My select query is:

select count(*) from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

The way it works is that every person can be registered for a specific language and a specific course. They can sit am exam for a specific course and language as many times as they want, but they get a new entry in the datas1 table but with an incremented exam number from the previous entry. All other values can be the same. Now we want to be able to delete some of the older rows from the datas1 table (the earlier exam entries and keep the most recent ones up to a number specified in the NUMBER variable)

For example, a customer 1001 has sat language 1 in course 2 exam 5 times. If I set the NUMBER variable to 3 then all I want to remain in the datas1 table is entries for exam 3, 4 and 5
The sql above will work as a select but cannot get it to work when trying to do the delete .eg.

delete from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

Please help

|||

You need to use the TSQL extension for the DELETE statement. ANSI SQL doesn't allow aliasing of source table in the DELETE statement. You can modify your DELETE statement like:

delete from schema.datas1 /* note additional FROM clause here */

from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

|||

That works great. Thanks!

One more question.

Now I want to break my deletes from the datas1 table into batches of say 1000 records at a time using a loop statement in perl. What exactly would be the transact sql (I assume using top) to do this?

Again I can get this to work for the select statement, but having difficulty with the delete.

Many Thanks!

|||

In SQL Server 2005, you can also specify TOP clause for DML statements. So you can run a batch like below which will delete 1000 rows at a time:

while(1=1)

begin

delete top(1000) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

while(1=1)

begin

delete top(@.n) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

In older versions of SQL Server, you can do the same using SET ROWCOUNT like.

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

set rowcount @.n

while(1=1)

begin

delete from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

set rowcount 0

Multiple column update statement

Hi,

I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:

UPDATE TableX M
SET (M.column1, M.column2, M.column3)=
(SELECT T.column1, T.column2, G.column3)
FROM Table_Y T,
Table_Z G
WHERE join condition))
WHERE EXISTS (join condition for TableX)

Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.

What would be the equivalent on SQL Server?

Thanks for the help,

Laszlo M

SQL Server does not support row value constructors yet. So you will have to use the TSQL UPDATE statement extension which has a FROM clause like:

UPDATE TableX

SET column1 = T.column1, column2 = T.column2, column3 = G.column3

FROM TableX as M, Table_Y as T, Table_Z as G

WHERE <join condition>

and <condition from exists>

See Books Online UPDATE statement topic for more examples and details on the syntax.

|||

Hi Laszlo,

UPDATE TableX
SET column1 = T.column1, column2 = T.column2, column3 = G.column3
FROM TableX M INNER JOIN
(TableY T INNER JOIN TableZ G ON T.column4 = G.column1)
ON M.column2 = G.column1

Regards,

Mike H.

|||

Thanks for the quick response. All of you were very helpful.

Laszlo

Multi-parameters issue in data flow task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

The "Parse Query" never works when you have ? in your SQL command. Just ignore the error.