insert/update trigger

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@.work_ord_num = work_ord_num , @.work_ord_line_num = work_ord_line_num

Thanks for your help!tbl2 and tbl3 should be joined with inserted.

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
George
You can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>
|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||I'm glad it helped you out.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>
sql

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
GeorgeYou can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||I'm glad it helped you out.
--
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>> You can explicitly CAST as desired. For example:
>> INSERT INTO MyTable (MyShorterColumn)
>> SELECT CAST(MyLongerColumn AS char(20))
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "george collins" <george@.nospan.com> wrote in message
>> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>>
>

Insert trigger to populate other columns in same row

I'm looking for an efficient way to populate derived columns when I
insert data into a table in SQL Server. In Informix and PostgreSQL
this is easily done using the "for each row..." syntax, but all I've
been able to come up with for SQL Server is the following:

create table testtrigger(id integer unique, b integer, c integer)
go

create trigger testtrigger_ins on testtrigger
for insert as
update testtrigger set c = (select ...some_function_of_b... from
testtrigger t1,inserted t2
where t1.id = t2.id)
where id in (select id from inserted);
go

where id is testrigger's unique id field, and c is a field derived from
b.

This seems terribly inefficient since each insert results in an extra
select and update. And if the table is large and unindexed (which it
could be if we are bulk loading) then I would imagine this would be
very slow.

Are there any better ways of doing this?

Many thanks,...
Mike Dunham-WilkieCREATE table ><>>Look at computered columns is your answer . BTW no
data is stored for this type of column.

e.g. CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2

Duncan|||On 22 Sep 2006 13:11:12 -0700, mike@.barrodale.com wrote:

Quote:

Originally Posted by

>I'm looking for an efficient way to populate derived columns when I
>insert data into a table in SQL Server. In Informix and PostgreSQL
>this is easily done using the "for each row..." syntax, but all I've
>been able to come up with for SQL Server is the following:

(snip)

Quote:

Originally Posted by

>This seems terribly inefficient since each insert results in an extra
>select and update. And if the table is large and unindexed (which it
>could be if we are bulk loading) then I would imagine this would be
>very slow.
>
>Are there any better ways of doing this?

Hi Mike,

Since SQL Server is optimized for set-based operations, you'll probably
find the speed of these operations to be quite adequate in most cases.

Here's an alternate syntax of the UPDATE statement that might result in
even faster operation. Note, though, that this syntax has some quirks,
especially if there's not a guaranteed one to one mapping of rows in the
update target and the source of the data.

UPDATE t
SET c = some_function_of_b
FROM testtrigger AS t
INNER JOIN inserted AS i
ON i.id = t.id

But only use this if you have a solid reason for being unable to use a
computed column, as Duncan ("undercups") demonstrates in his reply!

--
Hugo Kornelis, SQL Server MVP

Insert to Subset of Columns

In a sp I am populating a temp table that may have a number of null column
values. I'd like to populate the columns that will not have null values by
using an INSERT statement, and then separately UPDATE the other columns
which may have null values.
When I execute the INSERT statement to populate *only* the MemberID,
LinkText, and TargetURL columns, I get the following error:
<< Insert Error: Column name or number of supplied values does not match
table definition >>
My Question: Must I specify some value for all columns in the INSERT
statement, or is there a way to insert into a subset of the columns and then
update the rest later?
CREATE TABLE #LINKS
(
MemberID int,
LinkText varchar(100),
TargetURL varchar(100),
ImageURL varchar(150) NULL,
ActiveImageURL varchar(150) NULL,
ExpandedImageURL varchar(150) NULL,
HoverImageURL varchar(150) NULL,
ImageHeight int NULL,
ImageWidth int NULL,
)DO you mean that ? :
INSERT INTO (Column1[,COlumn2...])
VALUES (Value1[,Value2...])
--OR
INSERT INTO (Column1[,COlumn2...])
<Query>
Refer to the BOL there are some example in there.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Smithers" <A@.B.com> schrieb im Newsbeitrag
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||You have two options. Assume table has three columns, and you don't want to
insert in the first or
third columns:
INSERT INTO tblname(first, second, third)
VALUES(default, 23, default)
INSERT INTO tblname(second)
VALUES(23)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smithers" <A@.B.com> wrote in message news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...[col
or=darkred]
> In a sp I am populating a temp table that may have a number of null column
values. I'd like to
> populate the columns that will not have null values by using an INSERT sta
tement, and then
> separately UPDATE the other columns which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID, LinkT
ext, and TargetURL
> columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match t
able definition >>
> My Question: Must I specify some value for all columns in the INSERT state
ment, or is there a way
> to insert into a subset of the columns and then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>[/color]|||INSERT INTO #LINKS (MemberID, LinkText, TargetURL)
VALUES (<MemberID>, <LinkText>, <TargetURL> )
works.
How do you exactly populate that table, because that error message doesn't
look familiar?
Jacco Schalkwijk
SQL Server MVP
"Smithers" <A@.B.com> wrote in message
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||INSERT INTO tbl
VALUES(111,222, DEFAULT, DEFAULT, N'bla-bla', DEFAULT, ....)
Message posted via http://www.webservertalk.com

INSERT QUERY!

Hi all,
I am attempting to populate a table with the results of the following
queries.
Unsure of what syntax is required.
Help appreciated!!!!
INSERT INTO [Question] (ExamID, Question, Idx) ?
(SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]),
(SELECT ex2_mult_Q FROM exam2 WHERE ex2_name = @.ExamName),
(SELECT Idx FROM Exam WHERE ExamID = (SELECT MAX(ExamID) FROM
[EasyLearning].[dbo].[Exam]) + 1)
Cheers AdamHi Adam,
INSERT INTO [Question] (ExamID, Question, Idx)
SELECT
(SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]) AS ExamID,
(SELECT ex2_mult_Q FROM exam2 WHERE ex2_name = @.ExamName) AS
Question,
(SELECT Idx FROM Exam WHERE ExamID =
(
SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]) + 1
)
) AS Idx
HTH, jens Suessmeyer.|||Look up INSERT.SELECT in Books Online, but start by building the SELECT quer
y
that returns all the values that you intend to insert.
Once the query returns the correct values, use it in the INSERT...SELECT
query.
If more than one row should be returned, make sure the values are properly
related - right now I see no relationship between the three selects - but if
you do, and it works as you expect, then that's good enough for me.
ML
http://milambda.blogspot.com/