Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
ADVERTISEMENT
Jun 27, 2007
Hello all,
I've got a question. I have an array that I am inserting into a table. The code is in classic asp and looks like this:
SQL1 = "INSERT INTO transactionz (classid, userid) values (" & listOfAnswers(i) & ",8)
The problem is that I want to only insert values that have the value "1". listofAnsers is an array that returns values of "1" and "2". If there a way to limit the inserts to values of "1". Another words, I want to just insert the values of "1" into the table. I know that you experts would say I should control this on the code side and thats true, however, I can't (because I don't control the code side) so is there a way to do this via SQL? Am I making any sense? I was thinking that I needed to add a "where" clause at the end of the code, but I don't know where to go from here.
View 1 Replies
View Related
Oct 15, 2007
Hi,
I have the following bit of SQL:
Code Block
INSERT INTO [INTRANETSQLEXPRESS].Trapped.dbo.TBL_Debtsolv (Debtsolv_ID, Payment_Amount, Payment_Status, Client_Status)
SELECT Client.ID AS ClientID,
InitialInstallment.OffsetAmountExpected AS FirstExpectedPayment
,
CASE WHEN Payment.TotalPaid <= 0 OR LatestPaymentDate IS NULL
THEN 'No Money Paid'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND Payment.TotalPaid >0
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) >GETDATE()
THEN 'Still Awaiting - Part Paid'
WHEN Payment.TotalPaid >= InitialInstallment.OffsetAmountExpected
THEN '1st Payment Made'
WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected
AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) <GETDATE()
THEN 'Late - First Payment Not Made'
ELSE 'Status Unclear'
END
AS Status
, CASE WHEN
Client.Status IN(0,1,2,3,4,5,6,7)
THEN 'In Preparation'
WHEN Client.Status IN(8,9)
THEN 'Active'
ELSE 'Inactive'
END AS ClientStatus
FROM SALEEN.Debtsolv.dbo.Client_Contact as Client
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Payment_Schedule AS Schedule
ON Client.ID = Schedule.ClientID
INNER JOIN SALEEN.Debtsolv.dbo.Client_LeadData CLD
ON Client.ID = CLD.Client_ID
INNER JOIN SALEEN.Debtsolv.dbo.Type_Client_Status TCS
ON Client.Status = TCS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source TLS
ON CLD.SourceID = TLS.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source_Group AS LeadGroup
ON TLS.[Group] = LeadGroup.ID
LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Users [User]
ON CLD.Counsellor = [User].ID
LEFT OUTER JOIN (SELECT ClientID
, SUM(CAST((Amount + ISNULL(AmountOffset, 0))AS Money) / 100) AS OffsetAmountExpected
, MAX(DateExpected) AS DateExpected
FROM SALEEN.Debtsolv.dbo.Payment_Schedule AS PS
WHERE (SequenceID <=(SELECT
CASE WHEN NumInitialInstalments = 0 THEN 1
ELSE IsNull(NumInitialInstalments, 1)
END
AS NumInitialInstalments
FROM SALEEN.Debtsolv.dbo.Client_LeadData AS LD
WHERE (Client_ID = PS.ClientID)))
GROUP BY ClientID
)
AS InitialInstallment
ON Client.ID = InitialInstallment.ClientID
LEFT OUTER JOIN (SELECT Receipt.ClientID
, SUM(CAST(Receipt.Amount AS Money)) / 100 AS TotalPaid
FROM SALEEN.Debtsolv.dbo.Payment_Receipt Receipt
INNER JOIN SALEEN.Debtsolv.dbo.Type_Payment_Status Type
ON Receipt.Status = Type.ID
WHERE (Receipt.Status = 5)
GROUP BY ClientID)
AS Payment
ON Client.ID = Payment.ClientID
LEFT OUTER JOIN (SELECT ClientID
, MAX(ID) AS MaxPSID
, MAX(LastPayment) AS LatestPaymentDate
FROM SALEEN.Debtsolv.dbo.Payment_Schedule Schedule
WHERE (NOT (LastPayment IS NULL)
AND LastPayment > CONVERT(DATETIME, '1980-01-01 00:00:00', 102))
GROUP BY ClientID)
AS LatestPaymentDate
ON Client.ID = LatestPaymentDate.ClientID
INNER JOIN (SELECT ClientID
, MAX(SequenceID) AS LFPDSequenceID
FROM SALEEN.Debtsolv.dbo.Payment_Schedule PS
WHERE (PaymentType = 1)
GROUP BY ClientID)
AS LastPayment
ON Client.ID = LastPayment.ClientID
AND Schedule.ClientID = LastPayment.ClientID
AND Schedule.SequenceID = LastPayment.LFPDSequenceID
WHERE Client.ID = TBL_Debtsolv.Debtsolv_ID
Executing this statement gives the following error:
Code Block
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "TBL_Debtsolv.Debtsolv_ID" could not be bound.
I think thsi is soemthing to do with referencing another table in the INSERT into command as if I put:
Code Block
WHERE Client.ID = '12065'
The statement executes with no problem. I think I have read in some other threads that you cannot reference another table in the WHERE part of an INSERT INTO clause but cannot find out how I can make this work.
Most of the statement comes from a pre-made report from one of our systems and I have slighlty changed it to insert the data into another database that we have.
Any suggestions would be greatly appreciated
Thanks
View 1 Replies
View Related
Nov 15, 2007
Hello,
I have a trigger on a table named Store. The trigger updates the longitude and latitude on store based on the zip. Simple right? Well, I'm trying to import data and of course the trigger is not updating the data as triggers are not on a row by row basis with multi row inserts.
Here is the error message I'm receiving:
Msg 512, Level 16, State 1, Procedure SetLongLat, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How would one go about resolving this issue? I've includes my snippets below:
Trigger:
Code Block
ALTER TRIGGER [dbo].[SetLongLat]
ON [dbo].[Store]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Long float
DECLARE @Lat float
SELECT @Long = LONGITUDE, @Lat = LATITUDE
FROM Zipcode..ZipcodeLite
WHERE ZIP_CODE = (SELECT Zip FROM Inserted)
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
IF UPDATE(Zip)
BEGIN
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
END
END
Thanks for your help!
Nathan
View 5 Replies
View Related
Mar 22, 2006
I have two tables:tb_news---story_id-productlisttb_lookup---story_id-productwhen an insert command is run on tb_news, productlist field ispopulated with a value such as 'abc, def, de'when this happens, i need tb_lookup to be populated with seperaterecords for each product in productlist and the story_id from tb_news.Example:INSERT INTO tb_news (story_id, product_list)VALUES (12345, 'abc, def, de')Results:tb_news--12345, 'abc, def, de'tb_lookup--12345, 'abc'12345, 'def'12345, 'de'Ideally, I would like this to use recursion and give me tha ability tochange the delimiter at any time (might not always be a comma). someproducts may have a period in them. number of products is unknown andmight be 0 (field may be empty or NULL).
View 2 Replies
View Related
Feb 1, 2008
Hi,
When a record is to be inserted, how can I check whether the insert is succeeded or not?
CREATE TRIGGER MY_TRIGGER ON MyTable AFTER INSERT
AS
DECLARE @id INT
DECLARE @name NVARCHAR(50)
SELECT @id=id,@name=name FROM INSERTED
BEGIN
INSERT INTO MySubTable(id,name) VALUES(@id,@name)
-- if failed, rollback
END
GO
GO
View 3 Replies
View Related
Apr 6, 2015
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
This is on SQL Server 2008R2.
View 2 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Nov 25, 2006
In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,
View 1 Replies
View Related
Jul 20, 2005
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Apr 9, 2007
I am trying to write a complex insert statement and not really sure how to get started.First I am building an application to a record label to add artist, songs, pictures, video, and music to their site.Thanks to asp.net 2.0 building the application interface was easy, but now I realize I have run into a brick wall and need some help.in one application, called "add artist album"in this application, you, the website administrator, are adding information to two different tables (artist (holds all info about the artist), album (holds all the info about artist album))I have no clue how to really start writing an insert statement for this.Basically I want to be able to insert the album data (artistid (foreign key), album name and release date) and also have a drop down listbox, which is connected to the database by the sqldatasource connector to allow the administrator to choose an artist from the artist table so that when they add the albem and release date they, the administrator, can choose which artist in the drop down list box to associate with the album and release date data that goes in the album table. That association is made with the artistid because it is a foreign key to the artist table but how do I insert the chosen artistid number into that foreign key field for the album table?
Please can someone point me in the right direction. I am thinking that the insert statement will be something like and insert with nested select statement and inner join included but i haven't a clue how to do this.My table DDLCreate table artist (artistid int prmrykey, artistname varchar (100), bio varchar(100))Create table album (albumid int prmrykey, albumname varchar (100), releasedate date, artistid int foreignkey)My DML for the insert statement so far:ALTER PROCEDURE sp_AddArtistAlbum @AlbumName varchar(50),@ReleasedDate datetime,@ArtistID intAS
SET NOCOUNT ON
DECLARE @AlbumID INT
INSERT INTO tb_Album
(AlbumName, ReleasedDate, ArtistID)
VALUES (@AlbumName, @ReleasedDate, select ArtistID from tb_Artist where ArtistID = (ArtistID from sqldatasource from the drop down listbox) )
Like I said know I am supposed to have a select subquery statement that is nested to really make this thing work but I do not know where to start, can someone please help me. I hope I have provided enough information.My expected results are to insert data from into the album table and have that data associated with an artist chosen in the drop down box.Please Help!!!!!!!!!!!!!!
View 1 Replies
View Related
Mar 26, 2004
Hi,
I have a question about Insert into .....
Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example,
insert into #TempTable
{
.......
........
}
Select * from products order by @SortBy @SortDirection.
//
First of all, the order by clause does not take
variables. I have tried to use
declare @query varchar (1000)
set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection
exec (@query)
//
However, I get an error message because I should supply a Select statement.
How can I solve this problem??
Thanks for your help!
View 3 Replies
View Related
Aug 20, 2007
Hi gurus
Please help me to get out the problem ... i wanna store the output of SP_MONITOR into a table. Please let me know how can i do this
Thanks & Regards
Chander
View 7 Replies
View Related
Mar 11, 2008
Hi Guru's,
INSERT INTO Books (BookName) SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)
Where my XML looks like:
<Objects>
<Book>
<ForeignID>876</ForeignID>
<BookName>SQLTeam Rocks</BookName>
</Book>
<Book>
<ForeignID>981</ForeignID>
<BookName>My SQL must get better</BookName>
</Book>
</Objects>
When I insert run the query I want to get the @@IDENTITY for each row and insert them with the corresponding ForeignID into a lookup table structured:
[ID Auto], [BooksID (from Books table)], [ForeignID (from XML)]
Any thoughts appreciated!
View 13 Replies
View Related
Aug 14, 2006
Hi,
Could some one help me how to do
insert into test2(id,name) values ((select max(id) from test1),'user1')
in MS SQL Server
its throwing "Subqueries are not allowed in this context. Only sc
alar expressions are allowed" Exception. Help is appreciated.
Thanks,
Murali
View 2 Replies
View Related
Sep 29, 2015
I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
[code]....
View 2 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Sep 14, 2006
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
View 1 Replies
View Related
Jun 4, 2008
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
View 2 Replies
View Related
Mar 20, 2015
I'd like to know how to get windows events script below into a SQL Server Table.
Get-WinEvent -LogName application -MaxEvents 200 | where {$_.LevelDisplayName -eq "Error"}
View 1 Replies
View Related
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
Mar 2, 2007
Hello everyone.
I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.
Someone does know how to do this?
View 5 Replies
View Related
Nov 20, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
Dec 3, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
Nov 12, 2001
Should a insert trigger fire my update trigger as well? it fires it automatically after the insert? Is this a bug?
View 1 Replies
View Related
Aug 31, 2000
I have to create a insert trigger that calls a stored procedure usp_a
I need to pass two parameters to this stored procedure, value coming from the row inserted...
What is the syntax? Any help will be appreciated?
View 1 Replies
View Related
Jan 14, 2005
Hi.
Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.
Any ideas?
Thanks.
Bill
View 4 Replies
View Related
Apr 26, 1999
I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.
View 3 Replies
View Related
Oct 13, 1998
I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?
Tax Detail Table Exchange Table
Account# int Country char
Description char ExchangeRate Money
Amount money
Country char
I am working with MSS 6.5. Any and all help will be greatly appreciated.
Fidencio Peña
View 3 Replies
View Related