Inserted Identities

Hi i have a Query Like this:

INSERT INTO TABLE1
SELECT * FROM TABLE2

TABLE1 has a identity column,
now i want to know what identities have been inserted into TABLE1 after the Query executes.

Be Sure,
Hosseinhi try this

INSERT
INTO Table1
SELECT *
FROM Table2

-- assuming Col1 and Col2 are your unique column identifiersa
SELECT t1.TheIdentityColumn
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Col1 = t2.Col1
t1.Col2 = t2.Col2|||

You can do with @.@.ROWCOUNT.

Code Snippet

SET NOCOUNT ON;

Insert Into <Your Identity Table>

Select <some columns> from <some table>;

Select * From <Your Identity Table>Where identity_column > Scope_Identity() - @.@.Rowcount

|||

This use of SCOPE_IDENTITY() is not guaranteed to to work. It is possible and happens that rows can be inserted into the table in the middle of the sequence. If you are using SQL Server 2005, you can use the OUTPUT clause with your INSERT statement to fetch the identity columns of the inserted rows.

Rhamille's code will work if you have the alternate keys to the table.

|||I agree with Kent point.|||Here is how you can use the OUTPUT clause:

DECLARE @.table1 TABLE
(
IDCol INT
)

INSERT INTO Table1(fldlist)
OUTPUT INSERTED.IDCol INTO @.table1(IDCol)
SELECT * FROM Table2

SELECT * FROM @.table1 will give you the identity columns that were inserted.

Insert, Update & Delete on two tables with same data structure...

I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.

Following Points to Consider.

1. Both tables are in the same database.

2. Table1 is using for data entry & I wants the same data in the Table2.

3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.

4. I need real time data insert, update & delete on Table2.

I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.

I want to understand how can I impletement this successfully without any ambiguity.

I have attached data structure for tables. Thanx...You want TWO tables with IDENTICAL structure and the SAME data in a SINGLE database?
We can help you debug your triggers if you post the code, but WHY?|||Actually we required some reports and as we have old version application, it could not be possible to generate required reports.

The data is dynamic (i.e. Table1) & changing with the stock quantity IN & OUT, thats why I will store data for specific span of time in the new table (Table2). I will use that data for reporting.

Which code you required..? I have attached script for creating a tables.|||I understand you want inserts copied to the second table.
What about updates? Do you want the data in the second table updated, or do you want a new record added instead?
What about deletes? Do you want the data in the second table deleted as well, or do you just want to mark the record as deleted?

Did you try writing triggers for Update and Delete? If so, post the code for those triggers and we will help you debug it our fix syntax errors.|||oye...redundant data...

In any case if you follow the Hint link sticky at the top of the forum and post what it tells you, I'm sure we can supply enough rope|||As I have told that the data entry done through frontend, I want each and every effect (i.e. row insert, update or delete) on Table2.

As I have told you that the second table I am using for reporting purpose & the reports will be wrong if it is not reflect the data which entered or modified last.

May be you think its too cumbersome but now let me explain the full scenario.

1. I have to do this because I have added new column in the Table2 which is not part of Table1.

2. Using insert trigger on Table1 I can add new row in Table2 same as Table1 as well as I can feed data in the new added column which is not part of Table1.

3. Whenever row inserted, update or delete in Table1 the Table2 should update accordingly.

4. I can not cascade update or delete because both tables are having only foreign keys. (cFinYrs, cLocCode, cMonth, cItemCode are foreign keys)

5. I will re-write triggers according to my requirement, but I need little help to be clear of the concept from you expert guys.

6. The script which I have given for creating a tables will create the same data structure for tables.

I have written trigger for insert, it's given below. It's working good for insert.

CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS

Declare @.cFinYrs varchar(3)
Declare @.cLocCode varchar(7)
Declare @.cMonth varchar(3)
Declare @.iSrNo int
Declare @.cItemCode varchar(20)
Declare @.dQty dec
Declare @.dRate dec(9,2)
Declare @.cDesc varchar(200)
Declare @.cCreated varchar(6)
Declare @.dtcreated datetime
Declare @.cModified varchar(6)
Declare @.dtModified datetime
Declare @.cMachIP varchar(15)

SET @.cFinYrs = (select cFinYrs from inserted)
SET @.cLocCode = (select cLocCode from inserted)
SET @.cMonth = (select cMonth from inserted)
SET @.iSrNo = (select iSrNo from inserted)
SET @.cItemCode = (select cItemCode from inserted)
SET @.dQty = (select dQty from inserted)
SET @.cDesc = (select cDesc from inserted)
SET @.cCreated = (select cCreated from inserted)
SET @.dtcreated = (select dtCreated from inserted)
SET @.cModified = (select cModified from inserted)
SET @.dtModified = (select dtModified from inserted)
SET @.cMachIP = (select cMachIP from inserted)

Select @.dRate = drate from ssstockmst where citemcode=@.cItemCode

Insert INTO Table2 values(@.cFinYrs, @.cLocCode, @.cMonth,
@.iSrNo, @.cItemCode, @.dQty,
@.dRate, @.cDesc, @.cCreated,
@.dtCreated, @.cModified,
@.dtModified, @.cMachIP)

How I can make this happen..? Thanx for replying...|||oye...redundant data...

Yeah it could be redundant data but it will helps me lot to produce reports according to management requirement. And this data will not be heavy in size (1 to 5MB) so don't affect the server space as we have provision for same. :)|||I have to do this because I have added new column in the Table2 which is not part of Table1.This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

But regarding your insert trigger...

The method you have chosen is not only slow and verbose, but will also fail if more than one record is inserted into the table by a single transaction. Triggers MUST be designed to function correctly with multi-record inserts.

No exceptions.

This is the method you want to use for your insert trigger:CREATE TRIGGER [InForRpt] ON [dbo].[Table1]
FOR INSERT

AS
begin
insert into Table2
(cFinYrs,
cLocCode,
cMonth,
iSrNo,
cItemCode,
dQty,
dRate,
cDesc,
cCreated,
dtCreated,
cModified,
dtModified,
cMachIP)
select inserted.cFinYrs,
inserted.cLocCode,
inserted.cMonth,
inserted.iSrNo,
inserted.cItemCode,
inserted.dQty,
ssstockmst.dRate,
inserted.cDesc,
inserted.cCreated,
inserted.dtCreated,
inserted.cModified,
inserted.dtModified,
inserted.cMachIP
from inserted
left outer join ssstockmst on inserted.cItemCode = ssstockmst.cItemCode
endMuch simpler, eh?
Now, I really recommend that you go back to Books Online and read the sections on triggers, paying careful attention to the examples given.|||This still makes no sense. Why not just add the column to Table1? Are you dealing with a reduced record set in table2? Is that data truncated occasionally, or filtered? We need to know how that data is being retained before helping you create Update/Delete triggers.

I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.

We found best solution for a while is to create a new table for same & we will update the table1 later, when we upgrade our database & application.

No, data is truncated...

Thanx blindman, for simplify insert trigger...|||I thought to add new column in the Table1 but Table1 is being used & lots of data in the table1. Second thing, I have to think of the forntend application too.Still makes no sense. You're taking up extra space by storing redundant data in table2, extra processing time by keeping the data synchronized, extra development time in setting up this process, and a properly designed front-end won't care or even know that you've added an extra column to the table.
You need a DBA to help you with this project...|||I have solved the problem.

1. I have created a surrogate key (combination of cFinYrs, cLocCode, cMonth & cItemCode) on Table1 and accordingly foreign key on Table2.

2. Set cascade for update & delete.

I have test it, it's working fine.

I am understanding what you want to say but right now I am not allowed to modify working table's structure. Surely I will do it but later.

Thanx blindman for all efforts you placed...

INSERT WITH relationships

Hi,

I've got two tables that i wish to insert data into simultaneously. The two tables have a relationship between the id on table1 and the field table1ID on table 2. the id field on table1 is an identity field and i wish to insert that into table2 [for several rows]

I can insert into table1 but not table2.

Does this make sense?

Kind Regards,
Rim:confused:Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW|||Originally posted by GWilliy
Can't do it with one Statement

Table2 Can't be an Identity field as well

You will have to insert the Header(Table1) First then Details(Table2) After

When Inserting into identity field records I believe theres a system variable @.@.Identity that can identify the newly created id without having to do a re SELECT

U Can

Maybe pass the Insert Values into a Stored Proceedure that will complete the 2 steps for you

BEGIN
--Insert Header Row
END
@.MyId = (SELECT @.@.Identity)
BEGIN
--Insert Detail Rows with @.MyId
END

Type thingy

Hope this helps

GW

yeah that makes sense. i figured out the @.@.identity thing. It's good to get someone's opinion too rather that just the help files.

thank you very much.

Insert Trigger

Hi,
I am new to triggers and would like your help
I am trying to write an insert trigger on a table, so that when a record is
inserted in table1 a dummy record is inserted in table2
eg: table "master" has a record with fields "1', "Honda", "1998"
When this record is inserted into the "master" table, I need it to insert
another record in the "userlog" table, with the following fields "1",
"Honda", "datatimestamp". The trigger inserts the record when I use
hardcoded values. However, I do not know how to reference the values that
were inserted into the "master" table and then insert those values into the
"userlog" table.
Please help
Thanks
- RichIn triggers, there are 2 "special" tables in memory, that
exist for use only inside triggerville, called INSERTED or
DELETED. These tables are maintained for you by SQL
Server, and the layout of columns, datatypes matches back
exactly to the "master" table. Refer to these like other
tables (e.g. select * from INSERTED) INSIDE the trigger of
the table being modified...
On an INSERT, the new values inserted are stored in
INSERTED only.
On an UPDATE, the old values are stored in DELETED, and
new values are stored in INSERTED.
On a DELETE, the old values are stored in DELETED only.
Remember that a trigger is executed once per SQL action
against that table, so if your statement inserts 100 rows
to table_X, the INSERT trigger for table_X is fired ONCE,
not 100 times...
Bruce
>--Original Message--
>Hi,
>I am new to triggers and would like your help
>I am trying to write an insert trigger on a table, so
that when a record is
>inserted in table1 a dummy record is inserted in table2
>eg: table "master" has a record with
fields "1', "Honda", "1998"
> When this record is inserted into the "master" table, I
need it to insert
>another record in the "userlog" table, with the following
fields "1",
>"Honda", "datatimestamp". The trigger inserts the record
when I use
>hardcoded values. However, I do not know how to
reference the values that
>were inserted into the "master" table and then insert
those values into the
>"userlog" table.
>Please help
>Thanks
>- Rich
>
>.
>|||Bruce
Thanks you very much. This helped
- Rich
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:097b01c3adfb$5205bc50$a601280a@.phx.gbl...
> In triggers, there are 2 "special" tables in memory, that
> exist for use only inside triggerville, called INSERTED or
> DELETED. These tables are maintained for you by SQL
> Server, and the layout of columns, datatypes matches back
> exactly to the "master" table. Refer to these like other
> tables (e.g. select * from INSERTED) INSIDE the trigger of
> the table being modified...
> On an INSERT, the new values inserted are stored in
> INSERTED only.
> On an UPDATE, the old values are stored in DELETED, and
> new values are stored in INSERTED.
> On a DELETE, the old values are stored in DELETED only.
> Remember that a trigger is executed once per SQL action
> against that table, so if your statement inserts 100 rows
> to table_X, the INSERT trigger for table_X is fired ONCE,
> not 100 times...
> Bruce
> >--Original Message--
> >Hi,
> >I am new to triggers and would like your help
> >
> >I am trying to write an insert trigger on a table, so
> that when a record is
> >inserted in table1 a dummy record is inserted in table2
> >
> >eg: table "master" has a record with
> fields "1', "Honda", "1998"
> > When this record is inserted into the "master" table, I
> need it to insert
> >another record in the "userlog" table, with the following
> fields "1",
> >"Honda", "datatimestamp". The trigger inserts the record
> when I use
> >hardcoded values. However, I do not know how to
> reference the values that
> >were inserted into the "master" table and then insert
> those values into the
> >"userlog" table.
> >Please help
> >Thanks
> >- Rich
> >
> >
> >.
> >sql

Insert Statement...

Hi All,

I am trying to insert data into table1 by getting data from table2, table3 and looking whether the data is already not exist in table1.

Here is my code...

insert into table1
(first_name, last_name, user_login, email, organization_name) values
(select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2ru, table3 wepsUsers
whereru.id =wepsUsers.user_id and
not exists
(select user_login from table1
whereuser_login =ru.UserID))

When I excute the code, I am getting the following error....

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.

But, If execute the following code...

select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2ru, table3 wepsUsers
whereru.id =wepsUsers.user_id and
not exists
(select user_login from table1
whereuser_login =ru.UserID)... I am able to see the values.

Can any one shade on my code? and please let me know, where I am doing wrong. I am appreciate your help.

Thanks.

Srinivas.

insert into table1
(first_name, last_name, user_login, email, organization_name)
select ru.firstname, ru.lastname, ru.UserID, ru.EmailAddress, ru.BusinessName from table2ru, table3 wepsUsers
whereru.id =wepsUsers.user_id and
not exists
(select user_login from table1
whereuser_login =ru.UserID)

|||In case you don't see it right away, "values" should not be there.|||

Hi,

Thanks to every one for the help.

--Srinivas

insert row question

Hello all, my main question is i guess, is this the easiest way?
INSERT INTO table1 (c1, c2, c3...)
SELECT number1 AS c1, c2, c3...
FROM table1
WHERE c1 = number2
Just so there is no confusion: I am only working with one table here. I
basically want to copy a row where c1 = some_number, and paste on a new row
where c1 = different_number: Here is a crude example:
some_number, c2, c3, c4...
different_number, c2, c3, c4...
The reason I ask is beacuse the columns can sometimes extend way out there,
it is not very convenient to get the names and list them all out. I have
saved code to run a lot of the queries I need just as anyone would do, but
they are not always in hand.
I was looking for something real simple, but that may be asking to much.
Would a cursor, or using a temp table be better? Or just stick to way above?
I really hope this is all not to confusing, and thank you all very much.
Josh.Just so I understand, you have a table with some number of rows in it,
like so:
5, 10, 15
6, 11, 26
And you want to add an additional row for every row in the table where
the first column = some value (say 5 in this case), but you want to
change that value to be something else (7), eg.
7, 10, 15
So your results would be
5,10,15
6, 11, 26
7, 10, 15
Correct?
If so, your method is fine.
Stu|||Stu, that is close to what is happening. The colums are a variety of data
types, not all numbers. Also, I do not always want to add them in sequential
order. The first colum is an ID number that associates all rows with that
number together, I will use all numbers as an example since it is easiest:
7, 4, 2, 6, 5
7, 9, 6, 2, 6
3, 2, 5, 8, 9
6, 8, 3, 1, 7
4, 0, 5, 7, 3
4, 0, 5, 8, 2
4, 9, 4, 2, 3
Now lets say I want to take that very first row of data and make a copy to
add to the group starting with 4.
So after running the query end up with something like this:
7, 4, 2, 6, 5
7, 9, 6, 2, 6
3, 2, 5, 8, 9
6, 8, 3, 1, 7
4, 0, 5, 7, 3
4, 0, 5, 8, 2
4, 9, 4, 2, 3
4, 4, 2, 6, 5
I know that is not really far from what you had wrote but I just thought I
would clarify. Do you still think the best way is with what I am doing now?
Thank you, Josh
Stu wrote:
>Just so I understand, you have a table with some number of rows in it,
>like so:
>5, 10, 15
>6, 11, 26
>And you want to add an additional row for every row in the table where
>the first column = some value (say 5 in this case), but you want to
>change that value to be something else (7), eg.
>7, 10, 15
>So your results would be
>5,10,15
>6, 11, 26
>7, 10, 15
>Correct?
>If so, your method is fine.
>Stu|||Sorry, my example was a poor fit; yes, you should be OK.
Stu

Insert question.

I'm sending a command to SQL Server:
Insert into table1 (aaa) values ('bbb')
it works fune but what if I wish to incert value b'bb?
Insert into table1 (aaa) values ('b'bb') causes an error.
What do I have to change in that command?> it works fune but what if I wish to incert value b'bb?
> Insert into table1 (aaa) values ('b'bb') causes an error.
You have to escape the apostrophe with doubling it:
Insert into table1 (aaa) values ('b''bb')
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

Insert question.

I'm sending a command to SQL Server:
Insert into table1 (aaa) values ('bbb')
it works fune but what if I wish to incert value b'bb?
Insert into table1 (aaa) values ('b'bb') causes an error.
What do I have to change in that command?> it works fune but what if I wish to incert value b'bb?
> Insert into table1 (aaa) values ('b'bb') causes an error.
You have to escape the apostrophe with doubling it:
Insert into table1 (aaa) values ('b''bb')
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/