Insert Multiple Rows With A Trigger That Invoke A Function

Jan 17, 2012

Multiple rows to insert:
---------------------
insert into Customer(CustomerId,Name,Value)
select CustomerId,Name,Value
from CustomerTemp

Trigger in Customer table that invoke a function:
alter TRIGGER [dbo].[Calculation] ON [dbo].[Customer]
AFTER INSERT
AS

update Customer
set Percentage = dbo.GetPercentage((select Value from inserted))
where CustomerId = (select CustomerId from inserted)

I'm getting the error for the multiple row.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Is there a way to let me insert multiple rows, using the trigger that invoke a function ?

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Trigger Inserted Multiple Rows After Insert

Aug 6, 2014

I create a Trigger that allows to create news row on other table.

ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX]
ON [dbo].[_IMPORT_FILES_CONTACTS]
AFTER INSERT
AS

[code]...

But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC001 - ACCOUNT 001
C003 - AD003 - AC001 - ACCOUNT 001
C004 - AD004 - AC001 - ACCOUNT 001
C005 - AD005 - AC001 - ACCOUNT 001

I search a means to have 50 lines differently on my CONTACT table.

C001 - AD001 - AC001 - ACCOUNT 001
C002 - AD002 - AC002 - ACCOUNT 002
C003 - AD003 - AC003 - ACCOUNT 003
C004 - AD004 - AC004 - ACCOUNT 004
C005 - AD005 - AC005 - ACCOUNT 005

View 9 Replies View Related

Multiple Insert Call For A Table Having Insert Trigger

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

Trigger With Multiple Rows

Jun 1, 2006

Hi i've searched a lot with no results, im trying to do a trigger, this is the scenario

Got 3 tables, Product, Lines, Sublines

If Update Multiple sublines it should be updated to the products table, but i cant because the inserted table has multiple rows, the only way to do this is using cursors???

(Note, i cant do this using cascade because there are others, if i include this get a circular problem so i have to solve this other way)

View 1 Replies View Related

LINQ Layer - How To Invoke A Sort Function

Jun 5, 2007

 
 Dear all,
I'm trying to create a BLL for an objectdatasource to used for a sorted DataGrid. I am trying to learn about how to use LINQ and I'm sure this is a simple question.
var query = (from p in db.MP3Collections select p).OrderBy(?????????);
 
Now I want to invoke the following method  List<MP3Collection> GetAllMP3s(string sortColumns), which can contain a string name for the field in MP3Collection that is to be sorted (and optionally the "desc" tag).
Now I'm resorting tocoding the query on a case by case basis, which is very tiresome (see the partial code below). This works fine, but is very long and tedious coding. I'm not sure how to go about neatening the code, though am sure it involves the ?????? space above.
Any ideas?
 public static List<MP3Collection> GetAllMP3s(string sortColumns)
{System.Diagnostics.Debug.WriteLine("Pure sort expression: " + sortColumns);
bool descending = false;if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
{
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);descending = true;
}string[] columnNames = sortColumns.Split(',');
 MP3DataClassesDataContext db = new MP3DataClassesDataContext();
List<MP3Collection> query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();foreach (string columnName in columnNames)
{switch (columnName.Trim().ToLowerInvariant())
{case "filename":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename ascending");
}
break;case "filepath":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath ascending");
}
break;
//OTHER CASES HEREdefault:
query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();
//throw new ArgumentException("SortColumns contains an invalid column name.");break;
}
}
 
//var query = (from p in db.MP3Collections select p).OrderBy(p=>p.fileName).ToList<MP3Collection>();return query;
}

View 1 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

SP5 Problem For Insert Trigger Update Function

Mar 8, 1999

In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.

Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?

View 4 Replies View Related

How Can A Function Return Multiple Rows

Nov 14, 2007

hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is

[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like

Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'

now can i make a function which returns 'NL','UK','IN','GN','JK'

View 1 Replies View Related

Update Trigger, Multiple Rows, Non Static Primary Key

Jul 17, 2007

Hello,

Anyone know how to create an update trigger where the primary key isn't fixed.

If the primary key change how can I tie together the Deleted and Inserted tables if more than one row is updated?

/Patrik

View 9 Replies View Related

How To Invoke Insert Stored Procedure

Apr 3, 2007

Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database.
Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________
CREATE PROCEDURE newuser003.InsertCompanyInfo
@CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20)
AS
INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN
C# CODE BEHIND: ______________________________________________________ 
public partial class ContractorSubmision : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {       }    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text);        cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text);        cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text);        TextBox4.Text = "Completed";    }}
 

View 5 Replies View Related

Sp_send_dbmail Trigger On Multiple Row Insert

Feb 15, 2008

Hi,


I have a SQL Agent Job that selects records that are of a particular age (from Table1) and inserts them into another table (Table 2) - multiple records get inserted as a single INSERT step. On Table 2 I then have a trigger set FOR INSERT which I was hoping would send an email using the fields copied from the batch job, one of which being an email address.


The trigger is: -




Code Snippet
CREATE TRIGGER trgSendMail ON Table2 FOR INSERT AS
DECLARE @myEmail VarChar(50)
SELECT @myEmail = strEmail FROM Inserted
EXEC sp_send_dbmail
@Profile_Name = '{MailProfile}',
@Recipients = @myEmail,
@Subject = '{MailTitle}',
@Body = '{MailBody}'






Thing is, the agent works fine but the trigger only sends an email to the first row inserted.
Any ideas on how to get around this so that if the batch job inserts 10 rows into Table2 then 10 emails are sent out?

Shaun.

View 6 Replies View Related

How To Do Multiple Rows Insert?

Nov 14, 2006

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;


Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";if (Connection.State != System.Data.ConnectionState.Closed) {
  Connection.Close();
}
Connection.Open();command.ExecuteNonQuery();Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
  Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Using this method i found out bug (or so i think).


I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

View 13 Replies View Related

Create A Compatible Function That Can Invoke A Stored Procedure Without Writing The ParameterName And Remembing The Type And Size....

May 30, 2008

suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name)    {        string[] strs=stringParameter.Split('&');        SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString());        SqlCommand cmd=new SqlCommand(name,conn);        cmd.CommandText=name;        cmd.CommandType=CommandType.StoredProcedure;        foreach(string str in strs)        {            cmd.Parameters.Add(".....",SqlDbType........).Value=str;  //my trouble        }        conn.Open();        cmd.ExecuteNonQuery();        conn.Close();        cmd.Dispose();    }

View 2 Replies View Related

AFTER INSERT Trigger Takes Forever On A Large Table (20 Million Rows)

Aug 30, 2007

I have a row that is being used log track plays on our website.

Here's the table:


CREATE TABLE [dbo].[Music_BandTrackPlays](
[ListenDate] [datetime] NOT NULL DEFAULT (getdate()),
[TrackId] [int] NOT NULL,
[IPAddress] [varchar](20)
) ON [PRIMARY]


There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.

I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:


CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount]
ON [dbo].[Music_BandTrackPlays] AFTER INSERT
AS
UPDATE
Music_BandTracks
SET
Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount
FROM
(SELECT TrackId, COUNT(*) AS PlayCount
FROM inserted
GROUP BY TrackId) AS TP
WHERE
Music_BandTracks.TrackId = TP.TrackId


When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.

How exactly is the pseudo 'inserted' table formed?

For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.

I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?

Thanks!

View 6 Replies View Related

Help! How To Insert Multiple Rows Into Database???

Jan 7, 2007

I keep getting this error but it will only insert the 1st row into my database table
The variable name '@CustId' has already been declared. Variable names must be unique within a query batch or stored procedure.
 
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 
 
 
 
Dim drow As GridViewRow
 
For Each drow In GridView1.Rows
 
Dim textBoxText As String = CType(drow.FindControl("Label2"), Label).Text
 
SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName)
SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString)
SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice)
SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText)
 
SqlDataSource2.Insert()
 
Next
 
 
 
 
Response.Redirect("checkout.aspx")
 
End Sub

View 3 Replies View Related

How Do I Insert Multiple Rows In A Table At Once?

Mar 26, 2008

 Hi,i m using sqlexpress 2005 and sql management express studio. I want to know how could i insert multiple records on a single query in a table?i also want to whats wrong with this insert query?DROP TABLE IF EXISTS `tblcountry`;CREATE TABLE `tblcountry` (  `ID` int(3) NOT NULL auto_increment,  `LCID` int(4) unsigned default '0',  `CountryCode` char(2) default NULL,  `Country` varchar(50) default NULL,  `CountryInt` varchar(50) default NULL,  `Language` varchar(50) default NULL,  `Standard` tinyint(1) unsigned default '0',  `Active` tinyint(1) unsigned default '0',  PRIMARY KEY  (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;INSERT INTO `tblcountry` (`ID`,`LCID`,`CountryCode`,`Country`,`CountryInt`,`Language`,`Standard`,`Active`) VALUES  (1,1030,'DK','Danmark','Denmark','Dansk',0,1), (2,2057,'GB','England','England','English',1,1), (3,1031,'DE','Deutschland','Germany','Deutsch',0,1);finally how could i extract the database structure and data from the sql express management studio?so that i can copy it and re use it some other computer.Thanks.Jack.  

View 7 Replies View Related

Insert Multiple Rows Into SQL Table

Aug 31, 2004

I am trying to insert multiple rows into a table getting data from a web form.

I have 2 fields being passed from a web form to the below stored procedure
@FormBidlistID Sample Data --> 500
@CheckBoxListContractors Sample Data --> 124,125,145,154,156,

The below DELETE function is working great. However for some reason or another the INSERT INTO sql command is not putting seperating the string and adding rows to the database? I am not sure where the error is occuring.

In the end I need the data to go into the database columns like so

Table: BidlistContractors (2 Columns: BidlistID, ContractorID)

BidlistID ContractorID
500 124
500 125
500 145
500 154
500 156

Stored Procedure Code:

CREATE PROCEDURE dbo.UpdateBidlistContractors
@FormBidlistID int,
@CheckBoxListContractors varchar(3999)
AS
DELETE FROM BidlistContractors
WHERE BidlistID = @FormBidlistID
DECLARE @ContractorID nvarchar(10)
DECLARE @BidlistID nvarchar(10)
DECLARE @startPosition int
DECLARE @commaPosition int
SET @startPosition =1
SET @commaPosition = 0
WHILE (@startPosition < LEN(@ContractorID))
BEGIN
SET @commaPosition = CHARINDEX(' , ' , @ContractorID, @startPosition)
SET @ContractorID= SUBSTRING(@ContractorID, @startPosition, @commaPosition - @startPosition)
INSERT INTO BidlistContractors (BidlistID, ContractorID)
VALUES (@BidlistID, @ContractorID)
SET @startPosition = @startPosition + LEN(@ContractorID) + 1
END
GO


Where am I going wrong? Thank you in advance for any help.
:-)

View 1 Replies View Related

Insert Data Into Multiple Rows From ASP.net

Nov 3, 2005

 I am stuck. I have some vars being passed to an aspx page that I need to dump into a db table in multiple rows, how do I do it? I am going into a SQL database using VS.Net 2003 here's the format that the vars come in the page as: UserID = 46 k12SessionArray0interaction_id = Interaction_01 k12SessionArray0correct_response = d k12SessionArray0student_response = d k12SessionArray0result = C k12SessionArray0latency = 00:00:02 k12SessionArray1interaction_id = Interaction_02 k12SessionArray1correct_response = c k12SessionArray1student_response = c k12SessionArray1result = C k12SessionArray1latency = 00:00:02 k12SessionArray2interaction_id = Interaction_03 k12SessionArray2correct_response = a k12SessionArray2student_response = a k12SessionArray2result = C k12SessionArray2latency = 00:00:03 now here's the format of the database AnswerID | UserID | InteractionID | CorrectResponse | StudentResponse | QResult | Latency How do I insert the data to have it be mulitiple rows like: 1 | 46 | Interaction_01 | d | d | C | 00:00:02 2 | 46 | Interaction_02 | c | c | C | 00:00:02 3 | 46 | Interaction_03 | a | a | C | 00:00:03

View 4 Replies View Related

INSERT INTO Multiple Tables Rows

Jul 5, 2005

Can i insert values into multiple tables? Usually we using this

INSERT INTO Customers (CustomerID) VALUES ('ABC')

But i want to combine both into one statement
INSERT INTO Customers (CustomerID) VALUES ('ABC')
INSERT INTO Orders (OrderID) VALUES ('DEF')

View 12 Replies View Related

Insert Multiple Rows - Performance

Jun 18, 2008

hi,

I came across the following topic which speaks about inserting multiple rows into a table.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52980

The following is the code it concludes:
----------------------------------------------------------
INSERT INTO InstitutionManagement.dbo.b10_partC
(unitid, repyear, test, subject_area, scores,
credit_awarded, comments, recno)
select
unitid = 'data1a',
repyear = 'data1b',
test = 'data1c',
subject_area = 'data1d',
scores = 'data1e',
credit_awarded = 'data1f',
comments = 'data1g',
recno = 'data1h'
union all
select
unitid = 'data2a',
repyear = 'data2b',
test = 'data2c',
subject_area = 'data2d',
scores = 'data2e',
credit_awarded = 'data2f',
comments = 'data2g',
recno = 'data2h'
union all
select
unitid = 'data3a',
repyear = 'data3b',
test = 'data3c',
subject_area = 'data3d',
scores = 'data3e',
credit_awarded = 'data3f',
comments = 'data3g',
recno = 'data3h'
... And so on...
----------------------------------------------------------

My question is based on the performance of the above insert statement aganist Microsoft.Net SqlBulkCopy Class.

One more thing: Does the above statement gets executed as asingle statement or as multiple statements (One execution for each Select statement).

thanks

regards,
vinay

View 2 Replies View Related

Insert Multiple Rows From Dataset Into SQL Database

Aug 16, 2006

Hi,
is there anyway to insert all the rows from a dataset to SQL Server table in a single stretch..

Thanks
Anz

View 1 Replies View Related

How To Insert Multiple Rows Using Stored Procedure

Feb 1, 2005

How to insert multiple rows with using a single stored procedure and favourably as an atomic process?

View 4 Replies View Related

Insert Multiple Rows Using A Stored Procedure

Sep 3, 2004

I'm writing a Intranet web application to allow users to add presentation files to a web site for others to download. The presentations are to be grouped by categories, however I want them to be able to create additional categories if needed. I have created two tables.

Table 1 - PresentationCategories
Table 1 Fields - ID, Category

Table 2 - PresentationFiles
Table 2 Fields - ID, Name, Description, Filename, Filesize, CategoryID

On my web page I want to call a stored procedure to insert records into the PresentationFiles table. I have check boxes on the web form for all the possible categories that exist. A user can check each category that this presentation applies too.

In my stored procedure, how do I accomplish inserting a record for each category that is selected on the web form?

I'm guessing that I'll need to pass the categoryID's parameter into the procedure as a delimited string and then process this string for each categoryID and insert records into the PresentationFiles table using a While loop. I'm just not clear on how this is accomplished.

Any advice on how to do it differently or other resources that you can point me to is very much appreciated.

View 1 Replies View Related

SQL 2012 :: How To Insert Multiple Rows With Condition

May 19, 2015

Create table #table (id int identity , from_country varchar(20) ,
to_country varchar(20),noofdays int, datetravel datetime )
insert into #table(from_country,to_country,noofdays,datetravel)
values
('Malaysia','India',2,getdate()-99),
('India','Singapore',4,getdate()-88),
('Singapore','China',5,getdate()-77),
('China','Japan',6,getdate()-66),
('Japan','USA',7,getdate()-55)
select * from #table

I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.

Ex :
#table
1MalaysiaIndia22015-02-09 02:04:09.247
2IndiaSingapore42015-02-20 02:04:09.247

[code]...

In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.

View 2 Replies View Related

Inserting Multiple Rows With A Single INSERT INTO

Jul 23, 2005

Hi,I have an application running on a wireless device and being wireless Iwant it to use bandwidth as efficiently as possible. Therefore, I wantthe SQL statement that it uploads to the SQL Server to be as efficientas possible. In one instance, I give it four records to upload, whichcurrently I have as four seperate SQL statements seperated by a ";".However, all the INSERT INTO... information is the same each time, theonly that changes is the VALUES portion of each command. Also, I haveto have the name of each column to receive the data (believe it or not,these columns are only a small subset of the columns in the table).Here is my current SQL statement:INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18, '610T142', 'K8',520);INSERT INTO tblInvTransLog ( intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]', '[CurrentUser]','3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30, '14841', 'B9', 344);Since the SQL statement INSERT INTO portion remains the same everytime, it would be good if I could have the INSERT INTO portion onlyonce and then any number of VALUES sections, something like this:INSERT INTO tblInvTransLog (intType, strScreen, strMachine, strUser,dteDate, intSteelRecID, intReleaseReceiptID, strReleaseNo, intQty,dblDiameter, strGrade, HeatID, strHeatNum, strHeatCode, lngfkCompanyID)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 5, 0.016, '1018', 18,'610T142', 'K8', 520)VALUES (1, 'Raw Material Receiving', '[MachineNo]','[CurrentUser]', '3/21/2005', 888, 779, '2', 9, 0.016, '1018', 30,'14841', 'B9', 344);But this is not a valid SQL statement. But perhaps someone with a morecomprehensive knowledge of SQL knows of way. Maybe there is a way tostore a string at the header of the command then use the string name ineach seperate command(??)

View 2 Replies View Related

Generate Multiple Rows For Insert From Single Row

Jan 15, 2007

Dear all,

I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?

Regards,

Pedro Martins

View 1 Replies View Related

Help With Query - Insert Multiple Rows And Link Between Tables.

Feb 27, 2007

I am trying to do the following:
Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems.
I'm inserting rows from a Table called EATemplateItems.  
So far I have something like this: (I have the PackageID already at the start of the query).
INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
 
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.

Any Advice / help would rock! Thanks

View 3 Replies View Related

Insert Into Multiple Rows Instead Of One Comma-delimited List?

Jul 20, 2005

Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J

View 2 Replies View Related

SQL 2000 How To Insert Multiple Rows Ina Single Table

Oct 25, 2007



I am using SQL 2000
I am getting a syntax error when I parse this sql script:

insert into Elec_Sub_Test1
values ('10-20-2007',35),
('10-21-2007',24)

What is the correct syntax to insert mutlipe rows in a single table.

View 4 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View 3 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved