SQL tool for MSDE: insert

Showing posts with label insert. Show all posts

Tuesday, March 27, 2012

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
Sincerely
Why use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
David Portas
SQL Server MVP
--

alias in insert statement

I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyWhy use the alias at all? It isn't needed. The INSERT INTO tablename
defines which table is impacted by the statement...I don't understand why
it would be helpful to alias the table name within the insert statement.
--
Keith
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:05EEA4BE-2351-49C2-923B-62DAC9391068@.microsoft.com...
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely|||Since an INSERT statement can only insert to one table an alias isn't
necessary and isn't supported by this statement. If you need more help
please explain just what you are trying to achieve.
--
David Portas
SQL Server MVP
--

Monday, March 19, 2012

Aid with store procedure

Hello, I have a problem, I want to execute in a single transaction three insert in store procedure, as it could implement. Thanks for its attentione.g.

create proc usp
as
begin tran
insert tb1 values(...)
if @.@.error<>0 goto rollbk
insert tb2 values(...)
if @.@.error<>0 goto rollbk
insert tb3 values(...)
if @.@.error=0
begin
commit
goto done
end

rollbk:
rollback

done:
return

Thursday, March 8, 2012

Aggregate string concatenation

I have the following data:

create table TempTable(name varchar(50), value varchar(50))
insert into temptable values ('A', 'one')
insert into temptable values ('A', 'two')
insert into temptable values ('A', 'three')
insert into temptable values ('B', 'four')
insert into temptable values ('B', 'five')

and i would like the following output:

'A', 'one, two, three'
'B', 'four, five'

any ideas on how to accomplish this in Sql Server 2000?

thx in advance..Never mind, i figured it out:

CREATE FUNCTION dbo.TempFunction (@.Name as varchar(50))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.RetVal varchar(1000)
SELECT @.RetVal = ''
SELECT @.RetVal=@.RetVal + value + ', '
FROM temptable
WHERE name=@.name
select @.RetVal = left(@.RetVal, len(@.RetVal)-1)
RETURN (@.RetVal)
END

SELECT name, dbo.tempfunction(name) as [values]
FROM temptable
GROUP BY name;

aggregate sql question

Given this table:
CREATE TABLE #T_PEOPLE (
SSN char (9),
BIRTHDAY datetime
)
insert #T_PEOPLE(ssn, birthday) values('123456789', '1/1/60')
insert #T_PEOPLE(ssn, birthday) values('111223333', '1/1/50')

Which query is faster to find the ssn of the older person?
This query:
select top 1 SSN
from #T_PEOPLE
order by birthday

or this one:
select SSN
from #T_PEOPLE
where birthday=
(select min(birthday) from #T_PEOPLE)I prefer first as it does not require grouping and subquery

Madhivanan|||Those two queries are not equivalent so a performance comparison is not
necessarily very useful. The TOP query will return at most, one row. If
there are multiple people with the same birthday for the minimum date
then you will get one arbitrary, unknown row. Unpredictable results are
generally bad news so if you use that version I recommend you use the
WITH TIES option.

Also, I notice you have made Birthday nullable. The TOP result will
return a NULL if any exists, so you will probably want to add WHERE IS
NOT NULL. The following is nearly equivalent to the MIN() query:

SELECT TOP 1 WITH TIES ssn
FROM #T_people
WHERE birthday IS NOT NULL
ORDER BY birthday

Except that this will return an empty set rather than a NULL if the set
Birthday IS NOT NULL is empty. Top queries are harder than they look!

As to which is quicker. Performance generalizations are really no
substitute for doing the testing yourself with your structure,
constraints, data and indexes - factors which I know nothing about.

--
David Portas
SQL Server MVP
--

Monday, February 13, 2012

AFTER INSERT TRIGGER PLEASE HELP

I need to set up a trigger that updates a field in a record directly
after it is inserted. I have been burning some serious cycles on this
and can't figure it out. Any help would be apreciated.
Here is what I have so far:
CREATE TRIGGER DateMod ON tablename
AFTER INSERT
AS
DECLARE @.RECORDID VARCHAR (20)
SELECT @.RECORDID = SELECT MAX(recordid) FROM tablename
UPDATE field2_newdate SET field2_newdate = (field1_olddate)+1
WHERE recordid = @.RECORDID;
As you can tell by the code, I am a newbie to sql triggers. Because of
this, I will provide a
more detailed explanation of what I am trying to accomplish.
****************************************
********************************
tablename
(before update)This is what the end result should look like
recordid field1_olddate field2_newdate
1 01/01/2000 02/02/2000
****************************************
********************************
Now lets add a record:
recordid field1_olddate field2_newdate
2 01/04/2000
****************************************
*******************************
The trigger should add 1 day to the field1_olddate and set the value of
field2_newdate to 01/05/2000
I need the trigger to add one day to the date in the field1_olddate and
then update field2_newdate in the same record with the new value
directly after the record is submitted.
Please help!
sql trigger newbiesteven@.mindspring.com,
The trigger is executed per statement instead per row, so you have to keep
in mind that the statement could take several rows. Try:
CREATE TRIGGER DateMod ON tablename
AFTER INSERT
AS
UPDATE tablename
SET field2_newdate = (select dateadd(day, 1, i.field1_olddate) from inserted
as i where i.recordid = tablename.recordid)
where exists(select * from inserted as i where i.recordid =
tablename.recordid)
go
AMB
"steven@.mindspring.com" wrote:

> I need to set up a trigger that updates a field in a record directly
> after it is inserted. I have been burning some serious cycles on this
> and can't figure it out. Any help would be apreciated.
> Here is what I have so far:
> CREATE TRIGGER DateMod ON tablename
> AFTER INSERT
> AS
> DECLARE @.RECORDID VARCHAR (20)
> SELECT @.RECORDID = SELECT MAX(recordid) FROM tablename
> UPDATE field2_newdate SET field2_newdate = (field1_olddate)+1
> WHERE recordid = @.RECORDID;
>
> As you can tell by the code, I am a newbie to sql triggers. Because of
> this, I will provide a
> more detailed explanation of what I am trying to accomplish.
>
> ****************************************
********************************
> tablename
> (before update)This is what the end result should look like
> recordid field1_olddate field2_newdate
> 1 01/01/2000 02/02/2000
>
> ****************************************
********************************
> Now lets add a record:
>
> recordid field1_olddate field2_newdate
> 2 01/04/2000
> ****************************************
*******************************
> The trigger should add 1 day to the field1_olddate and set the value of
> field2_newdate to 01/05/2000
> I need the trigger to add one day to the date in the field1_olddate and
> then update field2_newdate in the same record with the new value
> directly after the record is submitted.
> Please help!
> sql trigger newbie
>|||Thank you. You are a GOD! Your code works perfectly. Could you pleas
point me toward a good resource to learn about sql triggers?
Thanks again

AFTER INSERT trigger not firing in SQL 2005

Nothing fancy; just a trigger on a sharepoint table that supposed to
write a record to another SQL table.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [TV_UpdateFileSyncProgress]
ON [dbo].[Docs]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

BEGIN
IF EXISTS (SELECT null FROM inserted WHERE DirName like
'csm/%/Shared Documents')

BEGIN
IF NOT EXISTS (SELECT null FROM inserted INNER JOIN
TV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =
LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/Shared
Documents','') + '\' + inserted.LeafName)))

BEGIN
INSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,
ChangeType, SiteId, DirName, LeafName, FlagForDelete)
SELECT
0,1,1,SiteId,'F:\common\Extranet\',Replace(Replace (DirName,'csm/',''),'/Shared
Documents','') + '\' + LeafName,0 FROM inserted

END

ENDibrettferguson@.gmail.com wrote:

Quote:

Originally Posted by

Nothing fancy; just a trigger on a sharepoint table that supposed to
write a record to another SQL table.
>
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
>
ALTER TRIGGER [TV_UpdateFileSyncProgress]
ON [dbo].[Docs]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
>
BEGIN
IF EXISTS (SELECT null FROM inserted WHERE DirName like
'csm/%/Shared Documents')
>
BEGIN
IF NOT EXISTS (SELECT null FROM inserted INNER JOIN
TV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =
LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/Shared
Documents','') + '\' + inserted.LeafName)))
>
BEGIN
INSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,
ChangeType, SiteId, DirName, LeafName, FlagForDelete)
SELECT
0,1,1,SiteId,'F:\common\Extranet\',Replace(Replace (DirName,'csm/',''),'/Shared
Documents','') + '\' + LeafName,0 FROM inserted
>
END
>
END
>
END
>
END

Figured it out.

A bug in my win service was deleting the records as they were being
inserted in the destination table.

Neat.

(Today, I would like to own a lawn care business... yes, a lawn care
business. )

After Insert Trigger Issue

Trying to create a trigger that will based on the recorded inserted into an enrollment table will update a schedule table.

My syntax is as listed:

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Where dbo.tblClassSchedule.CourseNumber = inserted.EnrollCourseNumber

End

I keep getting msg 4101 'the multi-part identifier could not be bound'

Not really sure how to fix this, I have attempted also referencing the table that trigger is fired from, along with referencing an exists statement ; and of course when I didn't place a where statement the trigger will create itself, but will increase the enrollment count on every record

*** I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions***

Try something like this:

Code Snippet

CREATE TRIGGER IncreaseEnrollments

ON dbo.tblEnrollRegistrations

AFTER INSERT

IF @.@.ROWCOUNT = 0

RETURN

BEGIN

SET NOCOUNT ON;

UPDATE dbo.tblClassSchedule

SET EnrollNumber = ( EnrollNumber + 1 )

FROM dbo.tblClassSchedule c

JOIN inserted i

ON c.CourseNumber = i.EnrollCourseNumber

END

As a side note, using 'tbl' as a table name prefix is quite out of 'style'. You will usually know from context if it is a table, as the wasted three keystrokes every time you type is serves no purpose.

I hope you are all creating a trigger to decrease the EnrollNumber upon a registration cancellation.

|||

The following queries also work,

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Wheredbo.tblClassSchedule.CourseNumber in (select EnrollCourseNumber from inserted)

End

--or

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

WhereExists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)

End

|||

Can you have multiple course numbers in the registrations table? If so, none of the posted solutions will produce the correct results. You need to do the following:

Code Snippet

update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + (select count(*) from inserted as i

where i.EnrollCourseNumber = dbo.tblClassSchedule.EnrollCourseNumber);

The solutions with joins or exists (IN approach is same as EXISTS) will not count duplicate course numbers - for example, if there are more than one registrations per course which is likely.

|||

Nathan,

From your previous message

My scenario is listed below, with some sample code suggested. I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions

, please let us know exactly what you are "attempting to accomplish".

If you don't give any feedback, we can't tune our suggestions to help you find a solution.

|||

nothing is happening ... thats the problem ... I am attempting to increase an enrollment head count with the triggers listed ... i know i can do it with Visual Basic, but the code that I know it will take seems to be more complicated than what a trigger would allow ...

my first issue with my original trigger was that it wasn't recognizing the column referenced, which basically the 3 suggestions should have fixed. the 'enrollment number' does increase when i don't restrict the reference to a specific record, of course the problem with that is that it increases (and when i add the decrement trigger, decrease) the value of all records, which of course is not a result I want

thx

|||

For none of the suggestions to be working properly, there must be something about the tables and/or data that hasn't been disclosed and that would be helpful to find a working solution.

Please post the table DDL for ClassSchedule and EnrollRegistrations, and a few rows of sample data in the form of INSERT statements (see this link for ideas).

|||

Here Are The Table Structures. Pretty Much I am Working With Little Data Right Now, Because It Hasn't Been Rolled out yet ...

tblClassSchedule

USE [NHSMS]
GO
/****** Object: Table [dbo].[tblClassSchedule] Script Date: 07/26/2007 15:56:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblClassSchedule](
[ClassInstID] [int] IDENTITY(1,1) NOT NULL,
[CourseNumber] [int] NULL,
[CStartDate] [smalldatetime] NULL,
[CEndDate] [smalldatetime] NULL,
[MaxEnroll] [smallint] NULL CONSTRAINT [DF_tblClassSchedule_MaxEnroll] DEFAULT ((0)),
[EnrollNum] [int] NULL CONSTRAINT [DF_tblClassSchedule_EnrollNum] DEFAULT ((0)),
[InstName] [int] NULL,
[ClassType] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassType] DEFAULT ((1)),
[DaySchedule] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active1] DEFAULT ((1)),
[ClassStatus] [int] NULL CONSTRAINT [DF_tblClassSchedule_ClassStatus] DEFAULT ((1)),
[ClassRoom] [int] NULL,
[Notes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Active] [bit] NULL CONSTRAINT [DF_tblClassSchedule_Active] DEFAULT ((1)),
[EntryDate] [smalldatetime] NULL,
[WebSch] [bit] NULL CONSTRAINT [DF_tblClassSchedule_WebSch] DEFAULT ((1)),
[CLength] [int] NULL CONSTRAINT [DF_tblClassSchedule_CLength] DEFAULT ((0)),
[CLocation] [int] NULL,
[CScheduleType] [int] NULL,
[Day1] [smalldatetime] NULL,
[Day2] [smalldatetime] NULL,
[Day3] [smalldatetime] NULL,
[Day4] [smalldatetime] NULL,
[Day5] [smalldatetime] NULL,
[Day6] [smalldatetime] NULL,
[Day7] [smalldatetime] NULL,
[Day8] [smalldatetime] NULL,
[Day9] [smalldatetime] NULL,
[Day10] [smalldatetime] NULL,
[Day11] [smalldatetime] NULL,
[Day12] [smalldatetime] NULL,
[CStartTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CEndTime] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tblClassSchedule] PRIMARY KEY CLUSTERED
(
[ClassInstID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [NHSMS]
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblClassRooms] FOREIGN KEY([ClassRoom])
REFERENCES [dbo].[tblClassRooms] ([ClassRmID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblClassStatus] FOREIGN KEY([ClassStatus])
REFERENCES [dbo].[tblClassStatus] ([CStatusID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourses] FOREIGN KEY([CourseNumber])
REFERENCES [dbo].[tblCourses] ([CourseID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourseSchType] FOREIGN KEY([CScheduleType])
REFERENCES [dbo].[tblCourseSchType] ([CSchTypeID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblCourseType] FOREIGN KEY([ClassType])
REFERENCES [dbo].[tblCourseType] ([SchItemTypeID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblLocation] FOREIGN KEY([CLocation])
REFERENCES [dbo].[tblLocation] ([LocID])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblTimeValue] FOREIGN KEY([CStartTime])
REFERENCES [dbo].[tblTimeValue] ([TimeValue])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblTimeValue1] FOREIGN KEY([CEndTime])
REFERENCES [dbo].[tblTimeValue] ([TimeValue])
GO
ALTER TABLE [dbo].[tblClassSchedule] WITH CHECK ADD CONSTRAINT [FK_tblClassSchedule_tblUsers] FOREIGN KEY([InstName])
REFERENCES [dbo].[tblUsers] ([UserID])

tblEnrollReg

USE [NHSMS]
GO
/****** Object: Table [dbo].[tblEnrollReg] Script Date: 07/26/2007 15:56:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEnrollReg](
[EnrollID] [int] IDENTITY(1,1) NOT NULL,
[EStudentNum] [int] NULL,
[ECourseNumber] [int] NULL,
[PayMethod] [int] NULL,
[ClassPrice] [money] NULL,
[EnrollDate] [smalldatetime] NULL,
[EnrollBy] [int] NULL,
[EActive] [bit] NULL CONSTRAINT [DF_tblEnrollReg_EActive] DEFAULT ((1)),
[CancelDate] [smalldatetime] NULL,
[CancelBy] [int] NULL,
[ClassStartDate] [smalldatetime] NULL,
[ClassEndDate] [smalldatetime] NULL,
[CourseLocation] [int] NULL,
[EnrollNotes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Approved] [bit] NULL,
[ApprovedBy] [int] NULL,
[ApprovalDate] [smalldatetime] NULL,
CONSTRAINT [PK_tblEnrollReg] PRIMARY KEY CLUSTERED
(
[EnrollID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [NHSMS]
GO
ALTER TABLE [dbo].[tblEnrollReg] WITH CHECK ADD CONSTRAINT [FK_tblEnrollReg_tblClassSchedule] FOREIGN KEY([ECourseNumber])
REFERENCES [dbo].[tblClassSchedule] ([ClassInstID])
GO
ALTER TABLE [dbo].[tblEnrollReg] WITH CHECK ADD CONSTRAINT [FK_tblEnrollReg_tblStudent] FOREIGN KEY([EStudentNum])
REFERENCES [dbo].[tblStudent] ([StuRecID])

|||

Well, first off you didn't include the the table in question, but in this table, the key of the table you are relating too is ClassInstId, not CourseNumber. CourseNumber is nullable. So it would be better if the

dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

Second, I would suggest you don't do this in a trigger and just count them as needed, unless there are few inserts. This way ends up locking way more things than needed, usually. You will have to implement UPDATE and DELETE triggers also

But consider this example, with code modified from Umachandar's post:

CREATE TABLE [dbo].[tblClassSchedule](
[ClassInstID] [int] IDENTITY(1,1) NOT NULL,
[CourseNumber] [int] NOT NULL UNIQUE,
EnrollNumber int default 0
)
go
Create table dbo.tblEnrollRegistrations
(
enrollRegistrationId int identity primary key,
CourseNumber INT NOT NULL
)
go
create Trigger increaseEnrollments
on dbo.tblEnrollRegistrations
After Insert As
Begin
set nocount on;

update dbo.tblClassSchedule
set EnrollNumber = EnrollNumber +
(select count(*)
from inserted as i
where i.CourseNumber =
dbo.tblClassSchedule.CourseNumber);

End
go

insert into [tblClassSchedule](CourseNumber)
select 100
union
select 101
go
insert into dbo.tblEnrollRegistrations (CourseNumber)
values (100)

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 1
2 101 0

insert into dbo.tblEnrollRegistrations (CourseNumber)
select 100
union all
select 100
union all
select 100
union all
select 101
union all
select 101

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 4
2 101 2

Works as expected.

After Insert Trigger Issue

Trying to create a trigger that will based on the recorded inserted into an enrollment table will update a schedule table.

My syntax is as listed:

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Where dbo.tblClassSchedule.CourseNumber = inserted.EnrollCourseNumber

End

I keep getting msg 4101 'the multi-part identifier could not be bound'

*** I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions***

Try something like this:

Code Snippet

CREATE TRIGGER IncreaseEnrollments

ON dbo.tblEnrollRegistrations

AFTER INSERT

IF @.@.ROWCOUNT = 0

RETURN

BEGIN

SET NOCOUNT ON;

UPDATE dbo.tblClassSchedule

SET EnrollNumber = ( EnrollNumber + 1 )

FROM dbo.tblClassSchedule c

JOIN inserted i

ON c.CourseNumber = i.EnrollCourseNumber

END

I hope you are all creating a trigger to decrease the EnrollNumber upon a registration cancellation.

|||

The following queries also work,

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

Wheredbo.tblClassSchedule.CourseNumber in (select EnrollCourseNumber from inserted)

End

--or

Code Snippet

Create Trigger increaseEnrollments

on dbo.tblEnrollRegistrations

After Insert As

Begin

set nocount on;

Update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + 1

WhereExists (select 1 from inserted as I Where i.EnrollCourseNumber = tblClassSchedule.CourseNumber)

End

|||

Can you have multiple course numbers in the registrations table? If so, none of the posted solutions will produce the correct results. You need to do the following:

Code Snippet

update dbo.tblClassSchedule

set EnrollNumber = EnrollNumber + (select count(*) from inserted as i

where i.EnrollCourseNumber = dbo.tblClassSchedule.EnrollCourseNumber);

The solutions with joins or exists (IN approach is same as EXISTS) will not count duplicate course numbers - for example, if there are more than one registrations per course which is likely.

|||

Nathan,

From your previous message

My scenario is listed below, with some sample code suggested. I went and applied all three indepently & found none of them would actually do the update that I was attempting to accomplish ... any other suggestions

, please let us know exactly what you are "attempting to accomplish".

If you don't give any feedback, we can't tune our suggestions to help you find a solution.

|||

thx

|||

For none of the suggestions to be working properly, there must be something about the tables and/or data that hasn't been disclosed and that would be helpful to find a working solution.

Please post the table DDL for ClassSchedule and EnrollRegistrations, and a few rows of sample data in the form of INSERT statements (see this link for ideas).

|||

Here Are The Table Structures. Pretty Much I am Working With Little Data Right Now, Because It Hasn't Been Rolled out yet ...

tblClassSchedule

tblEnrollReg

|||

dbo.tblEnrollRegistrations table had the ClastInstId, rather than the CourseNumber.

But consider this example, with code modified from Umachandar's post:

update dbo.tblClassSchedule
set EnrollNumber = EnrollNumber +
(select count(*)
from inserted as i
where i.CourseNumber =
dbo.tblClassSchedule.CourseNumber);

End
go

insert into [tblClassSchedule](CourseNumber)
select 100
union
select 101
go
insert into dbo.tblEnrollRegistrations (CourseNumber)
values (100)

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 1
2 101 0

insert into dbo.tblEnrollRegistrations (CourseNumber)
select 100
union all
select 100
union all
select 100
union all
select 101
union all
select 101

select *
from [dbo].[tblClassSchedule]
go

ClassInstID CourseNumber EnrollNumber
--
1 100 4
2 101 2

Works as expected.

After insert trigger fires even though no insert actually occured

SQL Server 2000 SP3a
Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine fires
on an insert being run but with no actual inserts occuring.
ie I have a sproc which inserts into a table, I've checked the select
statement of the insert and it returns no rows, so I presumed this meant tha
t
the trigger would not fire as no rows are being inserted, but it does.
======================
-- The basic spoc is this, the select returns no rows, so no inserts should
occur':
insert into table_a (
a_col_1,
a_col_2)
select
b_col_1,
b_col_2
from some_table b
left outer join table_a on
b.pkey = a.pkey
where
a.pkey is null
=======================
======================
-- The basic trigger is
Create trigger tg_Trigger_Name
on A_Table
after insert, update
if update (col_a)
begin
update some other table set some stuff
end
======================
I've looked through BOL, but it simply sais after insert/update trigger
fires after completion.
Thanks for any pointers.> if update (col_a)
> begin
> update some other table set some stuff
> end
The trigger will still fire, even if no rows are affected. So, you should
base any activities in the trigger on whether or not anything actually
happened. I typically wrap my DML trigger activities inside of the
following:
IF @.@.ROWCOUNT > 0
BEGIN
.. stuff here
END
For an insert trigger, you could equally say:
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
.. stuff here
END
A|||This is the way it works. This is not bad, if your actions inside the
trigger are based on contents of the inserted table, because this table is
empty. However, you can use the following code to skip the logic inside the
trigger (the same is true for updates):
if not exists(select * from inserted)
return
Leonid.
"Steve'o" <Steveo@.discussions.microsoft.com> wrote in message
news:55564004-68B8-4879-9F24-7BE5CF2E8F43@.microsoft.com...
> SQL Server 2000 SP3a
> Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine
> fires
> on an insert being run but with no actual inserts occuring.
> ie I have a sproc which inserts into a table, I've checked the select
> statement of the insert and it returns no rows, so I presumed this meant
> that
> the trigger would not fire as no rows are being inserted, but it does.
> ======================
> -- The basic spoc is this, the select returns no rows, so no inserts
> should
> occur':
> insert into table_a (
> a_col_1,
> a_col_2)
> select
> b_col_1,
> b_col_2
> from some_table b
> left outer join table_a on
> b.pkey = a.pkey
> where
> a.pkey is null
> =======================
> ======================
> -- The basic trigger is
> Create trigger tg_Trigger_Name
> on A_Table
> after insert, update
> if update (col_a)
> begin
> update some other table set some stuff
> end
> ======================
> I've looked through BOL, but it simply sais after insert/update trigger
> fires after completion.
> Thanks for any pointers.

After insert trigger exec sp problems

Hi all,

I have an sp that sends cdomail which requires 4 variables.
I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?

Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@.From varchar(100) ,
@.To varchar(100) ,
@.Subject varchar(100)=" ",
@.Body varchar(4000) =" "
/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg

GOTry:

CREATE TRIGGER your_trigger_name ON dbo.your_table_name
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100) ,
DECLARE @.To varchar(100) ,
DECLARE @.Subject varchar(100),
DECLARE @.Body varchar(4000)

SELECT @.From = i.From, @.To = i.To, @.Subject = i.Subject, @.Body = i.Body FROM inserted i

EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF|||Thanx Man you where really really helpful!|||the best way would be not to do so.
instead store the records in a staging table and then configure a job to send the mails.|||Do you have an example for me?|||Try:

CREATE TRIGGER your_trigger_name ON dbo.your_table_name
FOR INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100) ,
DECLARE @.To varchar(100) ,
DECLARE @.Subject varchar(100),
DECLARE @.Body varchar(4000)

SELECT @.From = i.From, @.To = i.To, @.Subject = i.Subject, @.Body = i.Body FROM inserted i

EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF

This workes, i used this in an insert an update trigger, now when i insert a new record it fires the insert 1 time and the update 4 times which generates 4 emails when only 1 is the good one,

The triggers are:

Insert trigger

CREATE TRIGGER KRS_email_insert ON dbo.KRS_KRFID
after INSERT
AS

SET NOCOUNT ON

DECLARE @.From varchar(100)
DECLARE @.To varchar(100)
DECLARE @.Subject varchar(100)
DECLARE @.Body varchar(4000)

SELECT @.From = 'Klachtenregistratiesysteem',
@.To = i.email,
@.Subject ='nieuwe melding onder volgnummer '+ cast(i.volgnummer as varchar),
@.Body = '<style type="text/css">

</style>
<p>
<table width="*" border="0">
<tr>
<td colspan="2">Geachte '+M.NAAM+',<br>U heeft een klachtregistratieformulier ingevuld bij het JVH gaming products Klachtenregistratiesysteem, uw klacht is in het systeem opgeslagen onder volgnummer: <span class="style1">'+ cast(i.volgnummer as varchar)+'<br><br><br></span></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Onderwerp:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.onderwerp +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Probleemomschrijving:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.probleemomschrijving +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Melddatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.melddatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Evaluatiedatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.evaluatiedatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right"></div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="right"><br><br><br></div> <div align="left">LET OP: Deze e-mail is verzonden door een automatische mailbox, vragen die u naar deze mailbox stuurt worden niet beantwoord. <br>
Voor vragen of opmerkingen kunt u terecht bij het Niels Beukenex, telefoon: 0900-1793 of via email: <a href="http://links.10026.com/?link=mailto:nbeukenex@.jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@.jvh.nl</a>. </div></td>
</tr>
</table>
<p> </p>'
FROM inserted i, MAN_MEDEWERKERS m
--WHERE MELDDATUM < GETDATE()and m.uid = i.melder
EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF

Update trigger

CREATE TRIGGER KRS_email_update ON dbo.KRS_KRFID
After update
AS

SET NOCOUNT ON

DECLARE @.From varchar(100)
DECLARE @.To varchar(100)
DECLARE @.Subject varchar(100)
DECLARE @.Body varchar(4000)

SELECT @.From = 'Klachtenregistratiesysteem',
@.To = i.email,
@.Subject ='Uw melding met volgnummer '+ cast(i.volgnummer as varchar)+' is bewerkt',
@.Body = '<style type="text/css">

</style>
<p>
<table width="*" border="0">
<tr>
<td colspan="2">Geachte '+M.NAAM+',<br>U klacht met volgnummer <span class="style1">'+ cast(i.volgnummer as varchar)+' </span> is gewijzigd, klik <a href="http://links.10026.com/?link=http://pc/Support/KRS_KRFID/ShowKRS_KRFIDRecord2.aspx?KRS_KRFID='+ cast(i.volgnummer as varchar)+'">hier</a> voor meer details<br><br><br></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Onderwerp:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.onderwerp +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Probleemomschrijving:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+i.probleemomschrijving +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Melddatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.melddatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right">Evaluatiedatum:</div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left">'+cast(i.evaluatiedatum as varchar) +'</div></td>
</tr>
<tr>
<td width="*" bgcolor="#CCCCCC"><div align="right"></div></td>
<td width="*" bgcolor="#FFFFCC"><div align="left"></div></td>
</tr>
<tr>
<td colspan="2"><div align="right"><br><br><br></div> <div align="left">LET OP: Deze e-mail is verzonden door een automatische mailbox, vragen die u naar deze mailbox stuurt worden niet beantwoord. <br>
Voor vragen of opmerkingen kunt u terecht bij het Niels Beukenex, telefoon: 0900-1793 of via email: <a href="http://links.10026.com/?link=mailto:nbeukenex@.jvh.nl?subject=Vragen en/of info over JVH gaming products BV Klachtenregistratiesysteem">nbeukenex@.jvh.nl</a>. </div></td>
</tr>
</table>
<p> </p>'
FROM inserted i, MAN_MEDEWERKERS m
where i.melder = m.uid
EXEC sp_send_cdosysmail @.From, @.To, @.Subject, @.Body

SET NOCOUNT OFF

When a new record is inserted it will fill in the email field en the afdeling field from another table trough 2 other triggers.
After that the insert email trigger should run...

Now it runs the update email trigger 4 times when inserting a new record.
Can someone help me with this

Sunday, February 12, 2012

After an insert, how do I get the primary key of the new row?

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @.@.IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @.@.IDENTITY'."}. does anyone know what to do?Hi,

have a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

If you are on SQL2k5 you can use the new OUPUT parameter and put the information back via this technology.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

You can use DataTables and DataAdapter to make insertion in the database after the insertion into the database the DataAdapter retrive the values from database for automatically.

You have to use CommandBuilder for this purpose.
Make changes to the DataTable and then run UpdateDataBase method on that table of the DataAdaper..check MSDN for details.

Or you can create Stord procedure to Insert in DB after inseration it will get the PK and will return it for you.

Just Select Max(pk_id) From Table1

|||Akbar,

Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database? This fails if someone else writes to the database before you send your second query, correct?|||The recommended ways of doing that is by using scope_identity()|||The aggregate MAX is a function that was used in the old days to get the highest / recent value. Its not practical / preferable / suggested / recommended / whatever in days of writing with multiple users to a database. Like Joyei said, I would rather use the SCOPE_IDENTITY() approach.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Someone asked me the same thing today and after a couple of hours I've came up with this (the code is in VB but I will try to explain the concept as well as I can):

I'll use a sample table from a SQL Server (I work with an SQL 2000 right now). The table is called _FluxModels and has two fields FluxModelID (an identity field) and FluxModel (a varchar (50) field).

In order to update the table I use a SqlConnection, a SqlDataAdapter, a DataTable and a DataRow.

Instead of generating the InsertCommand with a CommandBuilder, I declare a separate SqlCommand and use this for getting the CommandText and Parameters.

Then I pass the CommandText and the Parameters to the InsertCommand of the DataAdapter.

I added a new parameter "@.ID" (you can use any name you like) of type SqlDbType.Int.

I added this text to the CommandText of the InsertCommand : select @.ID=SCOPE_IDENTITY()

I set the UpdatedRowSource property of the InsertCommand to UpdateRowSource.OutputParameters (actually it works without this setting).

Now, whenever I add a new record, I have the FluxModelID returned in the @.ID parameter of the InsertCommand of the SqlDataAdapter.

Maybe this is not the best way for doing this but it works.

Here is the code I used:

Dim cn As New SqlConnection("Data Source=[YOUR SQL SERVER];Initial Catalog=[YOUR DATABASE NAME];Integrated Security=True")

Dim da As New SqlDataAdapter("Select * from _FluxModels", cn)

Dim db As New SqlCommandBuilder(da)

Dim dt As New DataTable

Dim drr() As DataRow 'array of DataRows used for updating

Dim dr As DataRow

Dim NewCmd As SqlCommand

Dim i As Integer

Dim p As SqlParameter

cn.Open()

'Use the NewCmd instead of da.InsertCommand

NewCmd = db.GetInsertCommand

'Initialize the da.InserCommand as a new SqlCommand

da.InsertCommand = New SqlCommand

'Pass the parameters from the generated command

For i = 0 To NewCmd.Parameters.Count - 1

p = New SqlParameter

p.ParameterName = NewCmd.Parameters(i).ParameterName

p.SourceColumn = NewCmd.Parameters(i).SourceColumn

p.Direction = NewCmd.Parameters(i).Direction

p.DbType = NewCmd.Parameters(i).DbType

p.Value = NewCmd.Parameters(i).Value

da.InsertCommand.Parameters.Add(p)

'Pass the connection to the InsertCommand

da.InsertCommand.Connection = da.SelectCommand.Connection

'and the Commandtext

da.InsertCommand.CommandText = NewCmd.CommandText

'modify the CommandText

da.InsertCommand.CommandText = da.InsertCommand.CommandText & " select @.ID=SCOPE_IDENTITY()"

'add the parameter for the identity

da.InsertCommand.Parameters.Add("@.ID", SqlDbType.Int, 4, "")

'set the parameter direction

da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Direction = ParameterDirection.Output

'works without the next line

'da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

'Fill the DataTable

da.Fill(dt)

'Add the new record

dr = dt.NewRow

dr("FluxModel") = "BBB"

dt.Rows.Add(dr)

'Use an array of 1 DataRow to update the database

ReDim drr(0)

drr(0) = dr

da.Update(drr)

'Test if the parameter contains a valid value

If Not da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value Is System.DBNull.Value Then

'Display the identity field of the new record

MsgBox(da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value)

End If

'Close the connection to the database

cn.Close()

I hope this will help you.