Drawing line between table rows and columns

how to insert line between rows and columns in reporting services 2005Couldn't you just set the borderstyle top, bottom, left, or right according to where you wanted the lines?

Double Table Insert

Tables :

EmailUsers
ID int - PK
Email nvarchar(256)

ListsUsers
ListID int - FK to List Table - Combo PK
UserID int - FK to EmailUsers Table - Combo PK

When a person adds a user I need to:
A. insert them as a new entry into EmailUsers - no problem
B. insert their EmailUsers.ID from step A and ListID (passed in parameter) into ListsUsers - not so easy
C. if they're already in EmailUsers don't insert them but pass their existing EmailUsers.ID to part B

Any thoughts or examples I can follow? Maybe it's easier to do two seperate queries and control the if exists logic in asp.net?I would check out the starter kits--such as the TimeTracker. They illustrate the following:

if select Count(*) from emailusers where .. = 0
Begin
Begin Transaction

insert into Email users

select @.id = @.@.identity

if no error
continue

insert into ListUsers
(x, @.id)

Commit Trans
end
else
select @.id = id from emailusers

insert into ListUsers
(x, @.id)

Hope this points you in the right direction|||

CREATE PROCEDURE SaveUser
 @.EmailAddress VARCHAR(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 SELECT @.UserID = ID FROM EmailUsers WHERE Email = @.EmailAddress
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO EmailUsers (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 END
 INSERT INTO ListUsers (ListID, UserID) VALUES (@.ListID, @.UserID)

|||I'd say use SCOPE_IDENTITY() rather than @.@.IDENTITY.. if there was another insert at the same time @.@.IDENTITY will return the id of that insert. SCOPE_IDENTITY() will work within the scope of the insert..

hth|||ndinakar: Thanks for that little tidbit, I didn't realize there was a potential for scope issues with @.@.IDENTITY.|||it should be ok to use @.@.IDENTITY as there could be 1/1000 ( or more or less...just a guesstimate) chance of your insert coinciding with another one but that could very well runi your day...so...check out BOL for more info on them...

hth|||I was in Vegas for the past week. Will give the suggestions here a try on Monday morning.

Thanks!|||Works great but I'm slightly confused on my return value

CREATE PROCEDURE [dbo].[Add_List_Users]
 @.EmailAddress nvarchar(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 DECLARE @.returnCode INT
 SELECT @.UserID = ID FROM Email_Users WHERE Email = @.EmailAddress
 SET @.returnCode = 0
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO Email_Users (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 SET @.returnCode = @.returnCode + 1
 END
if not exists (select * from Lists_Users where ListID = @.ListID and UserID = @.UserID)
 INSERT INTO Lists_Users (ListID, UserID) VALUES (@.ListID, @.UserID)
 SET @.returnCode = @.returnCode + 2
return @.returnCode
GO

Scenarios:
User Added to Users Table and List Table - returnCode 3
User exists in Users Table and Added to List Table - return code 2
User exists in both tables - return code 0

Actual Return Values:
2
1
-1

That's fine, I just check for 2, 1, -1 instead of 3, 2, 0 but I'd like to know why the values returned aren't logically what they should be?

SET @.returnCode = 0
SET @.returnCode = @.returnCode + 1
SET @.returnCode = @.returnCode + 2
shouldn't that = 3??

It's as if @.returnCode starts off at -1 since all the return values are 1 less than I expect them to be.|||The SCOPE_IDENTITY really comes into play if you've got hidden inserts. For example, another insert triggered from your original insert. That's where simply returning @.@.IDENTIY becomes totally wrong. Like the poster said, use SCOPE_IDENTITY and you can sleep at night!|||plus..you might want to look at OUTPUT Parameters to return the id ..check out BOL for more info on OUTPUT parameters..

hth

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'
You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
Anith
|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id =
'1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo =
'soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
thank
francesco
Anith Sen ha scritto:

> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith
|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'[vbcol=seagreen]
What if there are more than one row in tLabel for Label_id value 1?
[vbcol=seagreen]
What if there are more than one row in tGenere for generi_tipo value 'soul'?
[vbcol=seagreen]
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
Anith

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
--
Anith|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id ='1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo ='soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
--
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
--
thank
francesco
Anith Sen ha scritto:
> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'
>> "
What if there are more than one row in tLabel for Label_id value 1?
>> cantanti_genere_id=the value coming "from tGenere where generi_tipo =>> 'soul'"
What if there are more than one row in tGenere for generi_tipo value 'soul'?
>> to do this i want to use the subquerys!
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
--
Anith

double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
Anith|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id =
'1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo =
'soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
--
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
--
thank
francesco
Anith Sen ha scritto:

> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'[vbcol=seagreen]
What if there are more than one row in tLabel for Label_id value 1?
[vbcol=seagreen]
What if there are more than one row in tGenere for generi_tipo value 'soul'?
[vbcol=seagreen]
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
Anith

dont insert if record exists

/*if key values exist don't insert new record*/
SELECT

/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@.nStudent, @.nAssignment, 0)
END

FROM tblScores
WHERE gtStudentId = @.nStudent AND assignmentId = @.nAssignment

tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

I'm getting syntax errors when I click check syntax (near keywords insert from and end).

one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

thanksI think the problem is that the INSERT-statement is not a result_expression (see BOL). Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?|||Besides, execute an INSERT-statement from a select-case? What are you trying to accomplish?

Its rather complicated but I'll try:

This code is inside a trigger for an enrollment table. If a student joins a class late (after assignements have been assigned) then the trigger creates new records in the scores table giving the new student a 0 for each assignment already assigned to the class. If a student gets unenrolled from the class the scores are maintained in case the student gets reenrolled. If the student gets reenrolled then I want to do a select on the scores table and bypass any scores the student may have had prior to being unenrolled.
INSERT-statement is not a result_expression
How does one do a conditional INSERT?|||IF NOT EXISTS(
SELECT TOP 1 gradeId
FROM tblScores
WHERE
gtStudentId = @.nStudent
AND assignmentId = @.nAssignment)
BEGIN

INSERT INTO tblScores (
gtStudentId,
assignmentId,
score)

SELECT
@.nStudent,
@.nAssignment,
0
END|||I forget which site...but we just did this...Gotta find the code I wrote...

Why would you want to double every access to the database?

Just handle the dup key error...in a calling sproc or the application...

EDIT: It was originally about Contraints...same thing

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1), CHECK(Col1 IN ('Y','N')))
GO

CREATE PROC mySproc99 @.x char(1) AS INSERT INTO myTable99 SELECT @.x RETURN
GO

CREATE PROC mySproc00
AS
DECLARE @.rc int
EXEC @.rc = mySproc99 'Y'
SELECT @.rc, @.@.ERROR
EXEC @.rc = mySproc99 'B'
SELECT @.rc, @.@.ERROR
GO

EXEC mySproc00
GO

DROP PROC mySproc00
DROP PROC mySproc99
DROP TABLE myTable99
GO|||I did not understand very clearly what you are trying to do... is it inserting into your table only those records that _do not already exist_ in that table?

If so, ty this:

Insert into tblScores (gtStudentId, assignmentId, score)

VALUES (@.nStudent, @.nAssignment, 0)

Where (not exists (Select * from tblScores
Where WHERE gtStudentId = @.nStudent
AND assignmentId = @.nAssignment))|||My Point, just attempt the insert...and trap the Error if it fails

Don't make 2 data access attempts to the database

Dont Insert duplicate rows

Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3.

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col2, col3)
)

This is my "insert" code:

INSERT INTO table1
SELECT table2.col1,table2.col2, table3.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

Wich conditions shoud i add to this code?

Thanks.

fmilano.If Col2 and Col3 is controlled by Primary key then it will be taken care and if any data is added will be errored.|||To be erroed is what I want to avoid. So, I want to filter this rows by columns col2 and col3.

doing a distinct insert?

hi there,

i was just wondering if it's possible to do a INSERT INTO, but with a DISTINCT on three columns at ones?

i want to insert values from a SELECT DISTINCT column_1, column_2, column_3 FROM table

thanks

SJB

Is this what you want to do?

INSERT newTab (col1, col2, col3)
SELECT DISTINCT col1, col2, col3 FROM otherTab

This works just fine.

/Kenneth

|||

yes

if i understand your ideea , this is the scenario:

I have 2 similar tables :

CREATE TABLE [dbo].[person](

[last] [nvarchar](50) NULL,

[first] [nvarchar](50) NULL,

[age] [nvarchar](50) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[person](

[last] [nvarchar](50) NULL,

[first] [nvarchar](50) NULL,

[age] [nvarchar](50) NULL

) ON [PRIMARY]

person have 3 rows with 2 duplicates

insert into person1

select distinct last,first,age

from person

insert only 2 rows