Multiple Insert

Jan 11, 2008

Hi...
I have two database table. we shall call them table 1 and table 2
I want to insert data into table 1 and table 2 on the same page, so that when i click a submit button data goes into both tables. So what i will like to see happening is table 1 gets updated first then using that value it just inserted it gets used in table 2.

Table 1 has an ID field that Table 2 needs inorder to insert its data. The ID in table 1 is a primary key and its a foreign key in table 2.
Does anybody know how i can go about doing this in a nice and simple way. I am very new to sql and asp.net, hence a simple solution will be very helpful. I hope my problem make sense..
 
Thanx in advance. 
 

View 4 Replies


ADVERTISEMENT

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID  key1  key2  key3  key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 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

Bulk Insert Multiple Files To Multiple Tables - How?

Feb 15, 2008

I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?

Here is XML file:




Code Snippet
<ReferenceFiles>

<File>


<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>

<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>






Thanks.

View 1 Replies View Related

Wants To Insert Into Multiple Table By A Single Insert Query

Apr 11, 2008

Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks

View 3 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

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

Multiple Insert

May 30, 2008

 In my project I need to reserve rows of data in a table. I need to do this to make sure that unique automtic number, Identifier of that table will correspond with a sample that needs to be proces. Later that Id will be used for further upadate etc. 
I know I can insert new row by using code link to the button_click class
  protected void ReserveSample_Click(object sender, EventArgs e)
    {        SampleReserved.Insert(); 
    } but what if I would like to have a user to choose from a dropdown on the page amount of rows that needed to be inserted at once. Is there a way of tieing dropdown value, let say ‘20’ to sql insert statement to run the insert 20 times of the same values  Here is the insert statement  
 INSERT INTO flower                      (reserved, reserved_date)VALUES     (@reserved, GETDATE())
 

View 6 Replies View Related

Multiple Insert With The Same ID

Apr 15, 2004

Here is my problem:

I would like to execute 10 inserts and every insert in this batch should have the same ID. The next batch would have the next sequential number (ID) and so on...

I can do this by using a secondary table where I keep track of my IDs.

Is there another way of doing it?

Thanks.

dg

View 1 Replies View Related

Multiple Insert

Jun 12, 2005

hi friends,
i am having a problem here. I am using msde and i have a database with 3 tables.
Here is what i want to do.
1- I want to insert posted data to table 1 ( i can do this step)
2- I want to insert posted data to table 2 ( i can do this step, too)
3- I want to insert the primary keys of the rows inserted to table 1 and table 2 , to table 3 . I dont know anything about stored procedures . Can i do this in my code-behind using C# ?  If you can show me the way i will be glad.Thanks in advance

View 3 Replies View Related

Multiple Insert

Oct 4, 2006

hi, i have 4 textboxes in my form. i have one column in my table such as names.
i am filling names in those taxtboxes,so i want to use only one query to insert 4 textbox values in my column(names).so please tell me query to do this. is tere any insert all query in sqlserver?please give me query for my need

View 2 Replies View Related

Using INSERT Where There Is A Multiple Value

Nov 29, 2007

Hi

I am trying to do a similar thing to what I did before but now using the following command.

Basically I want to INSERT the clauses shown in the INSERT statement into the VALUES as shown but where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values. Do I need to somehow use UNION or UNION ALL for this?

I am getting the following error when I run the statement as shown below:-

Cannot insert duplicate key row in object 'communications' with unique index 'c2962cn7081'.
The statement has been terminated.



declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)

insert into [communications]
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select address_number, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from communications where address_number in (126, 127, 128)

Thanks for all your help.

Jon

View 4 Replies View Related

Multiple Insert

Jul 23, 2005

Hi, I'm trying to insert multiple rows with the following statment andam getting a syntax error on line 3. The datatypes are varchars exceptfor status which is numeric.insert S (S#, SNAME, STATUS, CITY)values('S2', 'Jones', 10, 'Paris'),('S3', 'Blake', 30, 'Paris'),('S4', 'Clark', 20, 'London'),('S5', 'Adams', 30, 'Athens');What's wrong?Thanks,Sashi

View 2 Replies View Related

Multiple Insert

Mar 28, 2008

So I've been working on this project and ran into another problem with adding multiple rows of data. I have a couple of tables particular one that associates a Minor and its classes together. What I want to be able to do is when a student adds a Minor it also adds any Minor classes that aren't already in the Student_Classes table. So let's see if I can lay it out correctly and the code I have so far...





Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Minors] Script Date: 03/27/2008 21:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 03/27/2008 21:39:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]

USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Classes] Script Date: 03/27/2008 21:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Classes](
[StudClassID] [int] IDENTITY(0,1) NOT NULL,
[StudentID] [int] NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditID] [int] NULL,
[Days] [nchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time] [nchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Classroom] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Grade] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Semester] [nchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Completed] [tinyint] NULL,
CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED
(
[StudClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID])
REFERENCES [dbo].[Credits] ([CreditID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]

USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Classes] Script Date: 03/27/2008 21:40:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Classes](
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LongName] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


INSERT INTO Student_Minors (MinorID, StudentID) VALUES(@minorid, @studid)
INSERT INTO Student_Classes (StudentID, ClassID, CreditID)
SELECT sClass.StudentID
,c.ClassID
,cred.CreditID
FROM Student_Classes sClass
INNER JOIN Classes c
ON MinorRequiredClasses.ClassID = c.ClassID AND MinorRequiredClasses.MinorID = @minorID
INNER JOIN Credits cred
ON cred.ClassID = c.ClassID
WHERE sClass.StudentID = @studid
AND sClass.ClassID NOT IN (SELECT mrc.ClassID
FROM MinorRequiredClasses mrc WHERE mrc.MinorID = @minorID)




An example of each table is below:
Minors
MinorID Description Criteria

Computer Science blah blah blah... blah blah blah...

MinorRequiredClasses
MinorClassID MinorID ClassID
0 Computer Science CSC180
1 Computer Science CSC200

Student_Classes
StudClassID StudentID ClassID CreditID bunch of others that aren't important...
0 0 CSC180 313 ...


Classes
ClassID LongName Description
CSC180 Intro to Computer Prog... blah blah...
CSC200 Intermediate Progr... blah blah...


To check if a minor already exists I can figure out myself but it is really adding multiple classes based on if the class is part of the minor a student is adding to his/her profile.


Hope someone can help

View 3 Replies View Related

Insert With Multiple Subqueries

Sep 17, 2007

Hi
what i like to do is insert in one table 2 value from 2 different row.
exp:
table1: person
id name
1  bob
2 john
 so id like to make an insert that will result in this
table 2: person_knowed
idperson1: 1
idperson2: 2
so the wuery should look something like this:
 
insert into person_knowed(idperson1, idperson2)(select id from personwhere name = 'bob',select id from personwhere name = 'john')); 
anybody have an idea of how to acheive this?
 

View 1 Replies View Related

Sqldatasource Multiple Insert

Oct 8, 2007

Hi all,
I have few insertions statements in one datasource insert command, which i use for my user creation process adding new billing info shipping info and so on.
I've put this code in createuserwizard usercreated step with a try catch code. On the insertion if it doesnt work it deletes the user account.
Well lets say the first insertion went trough with out any problems, but the second one got blocked or didnt work length or any wierd on controled issue.
I was thinking of putting begin transaction end transaction statements in front and end of the statements but this can prevent the insert not to return error, which will prevent try catch to fire?
Basicly the idea is to do all insertions with the user creation, and if one goes down all the others gets reversed. (if possible single sqldatasource, just to make the code look clean)

View 2 Replies View Related

Insert Multiple Row Values

Oct 25, 2007

hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')

View 8 Replies View Related

Multiple Row Update/Insert

Jun 29, 2005

Hi,I have a a table that is primarily a linking table that contains values for categories indid int             Indicator ID indtype int         Indicator Type can be either 0 or 1catflagnum int     Category Flag Number   the number of the Category catflagvalue int    Cat Flag Value    The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time   i.e    The Data set could look something like          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 1                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 2                            'This value will change         Catflagvalue = 3                           'This value will change          indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 3                             'This value will change         Catflagvalue = 1                           'This value will change            indid = 3                                       'This value will be the same for all rows         indtype = 0                                   'This will be the Same for all rows         Catflagnum = 4                            'This value will change         Catflagvalue = 5                           'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?

View 5 Replies View Related

Insert Multiple Values

Jan 5, 2000

i have about 2,000 record and i need to insert them in my table.
how do i insert these informations using this syntax??
au_id au_name au_fname
1003 vivian latin
1005 cecy mani
1004 bili david

insert into autors (au_id, au_name,au_fname)
Values ('1101', 'Rabit','jesicca')


thanks for your time and help.

View 1 Replies View Related

Multiple Record Insert

Dec 9, 2002

I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:

Location1
--> Part1
--> part2 etc
Location2
--> part1 etc

i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.

My code:

DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups

if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN

SET @PREFIX = 'JUST'

SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX

SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()

EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1

END

:confused:

View 1 Replies View Related

Insert Multiple Record

Mar 3, 2004

can anyone help to explain how to insert multiple record into one/two table?

ex:lets say when user specify start date and end date, then we need to created and insert the record on that duration.

and how to do with insert the record weekly or monthly?until the end of the date!

View 1 Replies View Related

Insert Into Multiple Tables

Aug 6, 2007

hi guys,

can someone please tell me the best ways to insert data into multiple related tables .

cheers guys

andy

View 2 Replies View Related

How Can I Speed Up My Multiple INSERT's?

Feb 7, 2005

I have a data gathering application written in MSVC++ 6 that uses ADO to insert large amounts of data into a table. Currently I have a stored procedure that inserts a single row at a time and I call it everytime I have more data to insert. However this can often fully load SQL Server - I often have 10's or 100's of inserts a second for short periods and load goes up to 100%...

Does anyone know a way of making the inserts more effiicient without resorting to dynamic SQL?

For example is there a way of batching up these inserts such as passing 10 at a time to the sp and inserting them all at once with an "insert into <table> select ..."?

Or would modifying my C++ and wrapping a block of inserts to the single insert sp in a transaction help?

A collegue suggested writing the data to a temporary text file then using bulk insert at regular intervals but that would then involve writing a file management system as well and seems to be a bit of a hack!

Any help much appreciated.

View 14 Replies View Related

How To Insert Multiple Values

Apr 14, 2014

this is my data

AM1WSJZ1241
AM1WLSU7162
AM1SXBI5100
AM1TWXX0477
AM1MSMQ6167
AM1WRQP1810
AM1HNME1411

i want a query to insert a data in table

View 1 Replies View Related

Insert Multiple Files

Jan 24, 2008

Is there any control flow task or any task in SSIS that is able to get 50 XML files
from a directory on a server and insert them into a table/column of xml datatype
in a sql server database?

I lnow I can use the foreachloop container and specify the directory path to pick up
the xml files but what do you do after that?

I also know of the OPENROWSET Function but that does 1 file at a time only!

??


View 5 Replies View Related

Insert To Multiple Table

Oct 14, 2007

I want to insert 2 table at the same time . There are relationship between them .My tables are:

Topic : topicId, topicDate,topicInformation
Reply: replyId,topicId, replyInformation

View 3 Replies View Related

Insert Multiple Files

Mar 6, 2008

I have 60 XML files in the following directory, how do I loop through each one to insert into a table like the code below only does 1 file.

How do I store each filename in a variable and the concatenate it to the following code?


DECLARE @XML XML

SELECT @XML = CAST(BulkColumn AS XML)

FROM OPENROWSET(BULK '\SQLSRV1ConvergCust_21012008.xml' , SINGLE_BLOB) AS x

INSERT dbo.Test.XML (XMLDoc)

SELECT @XML

View 4 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

Insert With Multiple Sub Queries

May 27, 2008

In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.


INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)

View 1 Replies View Related

Insert Multiple Values Into One Field

Jul 24, 2006

i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date.  i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this?  what i currently have doesn't blow up, but neither does it actually insert anything.  here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>"    InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)">    <InsertParameters>        <asp:Parameter Name="Major" Type="Int32" />        <asp:Parameter Name="Minor" Type="Int32" />        <asp:Parameter Name="Sub" Type="Int32" />        <asp:Parameter Name="Rev" Type="Int32" />        <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" />        <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" />        <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" />    </InsertParameters></asp:SqlDataSource>

View 7 Replies View Related

MS SQL Basics: Insert Into Multiple Tables At Once.

Aug 15, 2006

I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general.
I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties:
Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime)
Course (CourseID as UniqueIdentifier PK, Name as VarChar(50))
I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query.
The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere.
I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great.
Thanks.

View 9 Replies View Related

Insert Multiple Records Into 2 Tables

Aug 29, 2006

I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?

View 1 Replies View Related







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