.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

INSERT Statement Inside a UDF?

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
I am using SQL Server 2005.  I know you can not put INSERT or UPDATE statements in a UDF.  I am looking for an alternative to getting the end result I need since this call has to go at a specific point in the code or it will never work.  I have a UDF which packs smaller boxes into a bigger box efficiently.  The UDF typically runs and creates as many bigger boxes as possible until all smaller boxes are used and then it returns a TABLE variable back to a stored procedure.  I need to insert a record into a new table at the point where each bigger box is full before it moves on to the next bigger box.  There is only a couple alternatives I can see but don't really know if each is possible.  I am open to an ideas: 1. Call a stored procedure from the UDF which executes the insert statement 2. Using EXECUTE to perform the insert statement 3. Re-write the UDF as a stored procedure.  It must be able to table two inputs and return a table which can be unioned with two other views. 4. Anything Else UPDATE: Scratch idea #1 since it says I can only call other functions or extended stored procedures UPDATE: Scratch idea #2 as it did not work

View Complete Post

More Related Resource Links

Insert Into SQL Server inside a For Each - Next Statement

I have the following code to For each Item in the file uploader, Insert Into database.... but It doesnt work in the For Each Statement but if i take it out of the For Each statement it works.   For Each item As AttachmentItem In Attachments1.Items            Dim objConn2 As New SqlConnection("Data Source=XXX")            objConn2.Open()            Dim objCmd2 As New SqlCommand("INSERT INTO clientport (name, event, date1, username, password, path)" & "VALUES (@name, @event, @date1, @username, @password, @path)", objConn2)                                 objCmd2.Parameters.AddWithValue("@name", clientname.Text)            objCmd2.Parameters.AddWithValue("@event", [event].Text)            objCmd2.Parameters.AddWithValue("@date1", [date].Text)            objCmd2.Parameters.AddWithValue("@username", username.Text)  &nbs

XML Repeater Inside Conditional Statement


Hi all,

I'm working on an VB.Net application which have 2 nasted repeaters using xml as data source. I need to add a conditional statement withen the first repeater ItemTemplate as follows:

	<%# IIf(XPath("@type")= 3 , "<html>", "<div id=""tblPages"" runat=""server""><divsub""><h2>"   &  XPath("@title") &  "</h2><ul class="sublinks"><asp:repeater id=""rpPages"" DataSource=""XPathSelect("@page")"" runat=""server""><ItemTemplate><li></li></ItemTemplate></asp:Repeater></ul></div></div>" ) %>

The problem come when I  try to add the

DataSource='<%# XPathSelect("page") %>'

so I get an error.

Does anyone have idea if the above code may work?

thank you

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

DropDownList inside FormView Insert Template

Hi, I am trying to change the textboxes in the FormView insert template to dropdownlists but they keep setting the parameters to null.   Here's what I have:  <asp:DropDownList ID="ddlYearOfQualification" SelectedValue='<%# Bind("YearOfQualification") %>' runat="server"> </asp:DropDownList> In Page_Load CType(frmSurvey.FindControl("ddlYearOfQualification"), DropDownList).Items.Add(New ListItem("2010", "2010"))CType(frmSurvey.FindControl("ddlYearOfQualification"), DropDownList).Items.Add(New ListItem("2009", "2009"))   and in the SqlDataSource InsertCommand="INSERT INTO [tblSurvey] (... [YearOfQualification] ...) VALUES ( ... @YearOfQualification ... ) And in insert parameters: <asp:Parameter Name="YearOfQualification" Type="Int16" /> And the insert fails with YearOfQualification as null. If I sue the standard textbox: <asp:TextBox ID="YearOfQualificationTextBox" runat="server"                 Text='<%# Bind("YearOfQualification") %>' /> It works.   Any ideas? Many thanks in advance.

Insert statement giving me syntax error


Good evening,


I have a MS Access table with the "ModelID" column set as AutoNumber and primary key. The ModelID is set as Field Size --> Long Integer, New Values --> Increment, Format --> General Number, Indexed --> Yes (No Duplicates).

Here is the code i am trying to execute, and is giving me an exception -> insert into syntax error:

string strInsert = "INSERT INTO tbl_Models (fName, lName, Image, Answer1, Answer2, Answer3, Answer4, Answer5, Votes, ImageHead, Age, Program, StudentID, PhoneNumber, EMail) VALUES (@fName, @lName, @Image, @Answer1, @Answer2, @Answer3, @Answer4, @Answer5, @Votes, @ImageHead, @Age, @Program, @StudentID, @PhoneNumber, @EMail)";
        OleDbCommand cmdInsert = new OleDbCommand(strInsert, myCon);

        cmdInsert.Parameters.AddWithValue("@fName", txtFirstName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@lName", txtLastName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Image", FileUploadBody.FileName.ToString() + sRandomBody);
        cmdInsert.Parameters.AddWithValue("@Answer1", txtWhyBeAModel.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Answer2", "");
        cmdInsert.Parameters.AddWithValue("@Answer3", "

(INSERT EXEC statement cannot be nested.) and (Cannot use the ROLLBACK statement within an INSERT-EX


hi all
i have a very important issue,

read this scenario please

i have three Stored Procedures Sp1,Sp2 and Sp3 .

the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.

if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

INSERT statement and Getting Data From User Into My Database



I have spent 2 days reading and trying to figure this out and everything i have read and every book I have brought only tells how to read info from a database and how to edit info that is in a database and they all display the data in the database!

I have a page created (getinfo.aspx)

On that page I have a correctly configured SQLDatasource (SQLSource1) (it is set to allow insert, edit and delete).

I have 1 fileupload control

I have 2 text boxes

I have 2 drop down list boxes

The 2 drop down list boxes are "binded" to to seperate tables (via 2 other correctly configured SQLDatasource controls 0 but with out permission to insert, edit or delete) and they correctly provide a drop down list for the user to pick from the 51 states and the 200+ Countries. Those work wonderfully).

I also have a Button Control

The table (myFriends) that SQLSource1 is "connected to" has the following columns:
Column Name  Type   Length Nullable
   userID  uniqueidentifier 16 false
   userName  sql_variant  80

DropDownLists inside Detailsview INSERT cant get to work.



I'm having a DetailsView which has two DropDownList's inside so I should be able to pick 2 values for the field from DDL instead of writing something for it. But I cant get it to Insert any new values to my Database...

Can anyone help me with this? I'm pasting my code and table details here below.

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
        CellPadding="4" DataKeyNames="SignUpKey" DataSourceID="LinqDataSource1"
        DefaultMode="Insert" ForeColor="#333333" GridLines="None" Height="50px"
        Width="125px" oniteminserted="DetailsView1_ItemInserted"
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

insert statement does not reflect in database


I am trying to insert data into my table whose primary key is auto increment. The problem i encounter is that anytime i perform the insert, it goes through and i can view it when my application is running but as soon as i stop debugging and i run again i dont see the data again. I also notice that the insertion i did does not show up in the database. Below is the code i am using:


String name, type;
name = txtname.Text;
type = txttype.Text;

int qtyToInt = Int32.Parse(txtqty.Text);
decimal priceToInt = decimal.Parse(txtprice.Text);

shopDataSet.stockRow newStockRow = shopDataSet1.stock.NewstockRow();

newStockRow.name = name;
newStockRow.type = type;
newStockRow.quantity = qtyToInt;
newStockRow.price = priceToInt;


Insert multiple rows with a single INSERT statement


With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?

How to insert a button or link inside a discussion forum


When users are inside the discussion list they want a link or button to return to the home page. How do I insert this on the discussion page?

cindi bethel carmona

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite".


How does this happen? the tblLinks doesn't even have a column called websiteID.  websiteID is the primary key of the table tblwebsite. LinkID is the primary key of tblLinks. There is not LinkID column in tblwebsite.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite". The conflict occurred in database "Linkexchanger", table "dbo.tblWebSite", column 'WebSiteID'. The statement has been terminated.


So how does this happen? How do I terminate the foreign key relationship? I understand I can add this to my Insert stored procedure:

DROP CONSTRAINT FK_tblLinks_tblWebSite

but that stored procedure is used for other functions in my website, and I don't know if doing that is a good idea. Is there a way to relax the constraints for the purpose of this function here?  I'm taking data from a backup links table and trying to write a new row in the links table.

INSERT statement conflicted with COLUMN FOREIGN KEY constraint...


Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "


Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you


Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

sql query select inside a select statement???????


hi all,

i have two tables named

1.LookupPageInfo and    (LookupPageInfoId,pathmessageID,pagemessageID)

2.Messages     (MessageId,MessageText,ChannelID)

Now i need to write a query something like this........in the query i need to pass the pathmessageid and needs to get messagetext from Messages Table,i am not sure how i need to write the below query..can someone guide me how can i write a query......as below..

select LookupPageInfoId,



          (select messagetext from Messages where MessageID=PathmessageId) as TEXT  

          from LookupPageInfo

insert statement violated because Foreign key constraint


I  create an SSIS package, i use data flow to import rows from different server to local server. Unfortunately, there always be error message something about insert statement violated because of foreign key "The INSERT statement conflicted with the FOREIGN KEY constraint" since the table is transactional table that has some foreign key. I want to minimize using temporary table and trying to use toolbox item in ssis.I would appreciate any since i am ssis newbie.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend