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..
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
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?
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...
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())
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.
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
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
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)
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
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
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.
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?
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)
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')
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?
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')
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:
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.
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!
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!
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.
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), )
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>
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.
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?
