Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

Multiple Group/Detail Records are not showing

I have a report with a page header, a report header and footer, and many group headers, one group / detail line, and some group footers.

The problem is that only one record per ID is allowed in the one group/detail line. Both records per ID will show in data view but not in the report output. I do not have "allow duplicates" selected anywhere that I am aware of.

Where else can I look?

Thanks,
cj

I assume you are using table in your report. Is your table detail grouped on ID? If so, all rows with the same ID will be grouped into one. Try removing the grouping in Details Grouping and see if you get all records displayed.

Multiple Deletions From Different Tables in SQL Server Trigger

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @.AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDischarge Table
DELETE FROM tblProgramDischarge
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignment Table
DELETE FROM tblRoomAssignment
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySurvey Table
DELETE FROM tblUnitedWaySurvey
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @.AdmissionIDDoes it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:
> I have written a trigger that's supposed to go out and delete
> corresponding records from multiple tables once I delete a specific
> record from a table called tblAdmissions.
> This does not work and I'm not sure why...
> Here's the code that's supposed to run, let's say, if a user (via a
VB
> 6.0 interface) decides to delete a record. If the record in the
> tblAdmissions table has the primary key (AdmissionID) of "123", then
> the code below is supposed to search other tables that have related
> information in them and also have an AdmissionID of "123" and delete
> that information as well.
> Any ideas? Here's the code:
> CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
> -- and here is the table name
> ON tblAdmissions
> -- the operation type goes here
> FOR DELETE
> AS
> -- I just need one variable this time
> DECLARE @.AdmissionID int
> -- Now I'll make use of the deleted virtual table
> SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)
> -- And now I'll use that value to delete the data in
> -- the tblASIFollowUp Table
> DELETE FROM tblASIFollowUp
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblTOADS Table
> DELETE FROM tblTOADS
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @.AdmissionID|||What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp.admissionid)

... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--|||On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

>This does not work and I'm not sure why...

Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?

>Any ideas? Here's the code:
(snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:

>SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

First, the second @. should be left out. This will simply set the variable
@.AdmissionID equal to itself.

But if you change it to

SELECT @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @.AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @.AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.AdmissionID = tblASIFollowUp.AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarges
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.

Multiple Datasets and Report Design problems

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
How do I accomplish this?
Thanks in advance.
PamHi Pam,
I'm not sure how your databases are set up (are they on the same
server?), but I would probably choose to combine the data from the
database instead of combining it at the report level - that way you
only need 1 report table and no sub reports and grouping/toggling the
data will be a piece of cake:
SELECT * from User INNER JOIN DatabaseB.dbo.CallLogs CallLogs ON
User.username = CallLogs.username ORDER BY Department, UserName
I hope this helps.
Take Care!
Michelle

Multiple Datasets and Report Design confusion

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
Any ideas on how to accomplish this scenario?
Thanks in advance.If both these databases are in the same SQL Server then it is a piece of
cake to do this in a Stored Procedure, just join the two tables. If they are
in different servers it is a little more difficult, you would need to use
linked servers. Same strategy though, you need to look at creating a stored
procedure. Based on your description it does seem to me that a subreport
will not work for you.
Oh, another idea. You don't even need a stored procedure if they are on the
same server, use the generic query designer and create the sql:
select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
b.somedatefield > @.startdate and b.somedatefield < @.enddate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23v$M9Z7AFHA.2112@.TK2MSFTNGP09.phx.gbl...
> I have a table in Database A with users and a table in Database B with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally started
> with a subreport to print the call detail for a user. Unfortunately, I
> need to be able to total and avg call detail for each user, dept which I
> believe must happen in the main report. By using a sub report I don?t
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||I believe that your best bet would be to either have some process that
creates an intermediary resultant table combining the abstract data
relationship between the two tables, or design your stored procedure so
that you are joining the two together.
As a design key I always try to leave the real work to the best of
breed. In this case you are going to be better off doing your real data
work in the database layer(SQL Server), not in the application layer
(Reporting Services).
-Brian
pb wrote:
> I have a table in Database A with users and a table in Database B
with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally
started
> with a subreport to print the call detail for a user. Unfortunately,
I
> need to be able to total and avg call detail for each user, dept
which I
> believe must happen in the main report. By using a sub report I
don't
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||THanks Brian, Bruce and Michelle. I have chosen to link my servers (data
on two different servers). I created some views and stored procedures.
All is woking great. Thanks for the direction!
Bruce L-C [MVP] wrote:
> If both these databases are in the same SQL Server then it is a piece of
> cake to do this in a Stored Procedure, just join the two tables. If they are
> in different servers it is a little more difficult, you would need to use
> linked servers. Same strategy though, you need to look at creating a stored
> procedure. Based on your description it does seem to me that a subreport
> will not work for you.
> Oh, another idea. You don't even need a stored procedure if they are on the
> same server, use the generic query designer and create the sql:
> select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
> innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
> b.somedatefield > @.startdate and b.somedatefield < @.enddate
>

Multiple data insertion with For clause

Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...

INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a

You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

For bigger sequential sets, build yourself a temporary table of sequential integers like this:

CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)

INSERT INTO #i
VALUES(NULL)

INSERT INTO #i
SELECT y FROM #i

Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...

USE Northwind
GO

CREATE TABLE myTable99 (sl int,age int)
GO

DECLARE @.x int, @.y int

SELECT @.x = 1, @.y = 1

WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y

SELECT @.x = @.x + 1, @.y = @.y + 1
END

SELECT COUNT(*) FROM myTable99
GO

DROP TABLE myTable99
GO