i have 3 tables i want to insert data in, customer,gaurdian,customergaurdian
where the primary key in customer is Cust_id ,in gaurdian GD_id, in customer gaurdfian Cust_id & GD_id which are forgien keys from the opther 2 tables how can i preform the insertion in the three tables taking into consideration that all insertions must be commited and non will be ignored i.e. all the 3 will take place !!!
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it? i think; it should be implemented through transaction statements but not much aware about these
Hi All, am new to sql server in my application I am having one Asp.net web page, the user has to enter values for 5 fields(Empno,Empname,salary,deptno,deptname) in that web page and for that i created two tables in sql serverEmp Table- empno(primary key),empname,salaryDept Table- Deptno(primary key),empno(Foreign key ),Deptname(with some check constraint.) After the user enter all the values in the Asp.net web page now i want to store data into database for that i wrote the following stored procedure...create procedure usp_EmpDept @empno integer, @empname varchar(15), @salary money, @deptno integer, @deptname varchar(10) As Insert into emp(empno,empname,salary)values(@empno,@empname,@salary) Insert into dept(deptno,Empno,deptname)values(@deptno,@empno,@deptname) but the problem is whenever some constaint violation for eg. if some check constraint violation in Dept table its inserting the values in the Emp table only, but my requirement is, It must enter into both tables if there is no constaraint violation otherwise it has to ignore both the tables. And also please suggest is there any better way to insert values into two tables other than using the stored procedure Any help will be greatly appreciated..Thanks,Vision..
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
Hi guys,I have a problem with my query. What i want to happen is to populate my table EV_NOTIFICATIONDETAILS (Docownerid, CurrentSentDate, LastSentDate, detailsID, GeneralRemarks) using the datas from the two different tables EV_NOTIFICATIONHEADER and EV_DOCDETAILS.I tried to create some a query but im having a error. The problem is once i insert the data from datas to the columns Docownerid, CurrentSentDate, LastSentDate the datas are stored in the database and when i tried to insert the remaining columns TO EV_NOTIFICATIONDETAILS detailsID, GeneralRemarks getting the datas from EV_DOCDETAIL it creates a new set of records in the database. Meaning it doesn't update the records in the table EV_NOTIFICATIONDETAILS but it creates a new set of records.here's my code:INSERT INTO EV_NOTIFICATIONDETAILS (Docownerid, CurrentNoticeSentDate, LastNoticeSentDate) SELECTDocownerid, CurrentSentDate, LastSentDateFROMEV_NOTIFICATIONHEADERINSERT INTO EV_NOTIFICATIONDETAILS (detailsID,GeneralRemarks) SELECTdetailsID,GeneralRemarksFROMEV_DOCDETAIL Any ideas and suggestions will be greatly appreciated.
I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005. I have four tables, an Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date) Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets) Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time) and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)
My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....
//insert an attendance record to the attendance table string userID;
userID = Session["User"].ToString();
SqlDataSource pgpDataSource = new SqlDataSource(); pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
finally { pgpDataSource = null; } //same code as above for the resistance table
However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables. I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table... Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C# Cheers Scotty
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1. Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID) This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section. I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck. Regards! Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open() For Each ..... Problems here also Next cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
I have the Temporary table:ItemDetailID (int)FieldID (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
Hi there I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts. Thanks
I need to insert records in two tables, one is main table and another is child table. From my aspx page I need to pass info. for one records in main table, insert that record into main table, get the is of the inserted table.
Then insert 15 records in the child table.
Everything must be in a transaction, either everything works or everything fails. Should I do it with aspx or should I pass arrays to a stored procedure?
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID Primary Key TeamCode a code value representing different teams Week will equal to 2Points nullable valuee.g.Table 1======UID TeamCode Week Points1 A1 1 1002 A2 1 99trying to insert into table 1Table 1========UID TeamCode Week Points1 A1 1 1002 A2 1 993 A1 2 null4 A2 2 nulletc...As you can see, UID is primary key, TeamCode may repeat according to week value Week is a constant Points will be nullHow can I do that with a single Insert Command? Thank you for your help. :)
Hi, I am trying to insert multiple values from another table as well as an addition value defined by me. Here's my code which is obviously incorrect at the AND statement: Insert Into table_1 (ID, Col_1, Col_2)Select ID, Col_A, Col_B From table_A AND table1.Col_3 = 'XYZ' Where table_A.Col_A IS NOT NULL
Pls advise on the correct way of constructing this statement. Many Thanks
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
Is there a way to insert multiple values into a single column based on various "tests". For example, I want to check a sales_order table and flag all new orders coming in against previous orders placed that were determined to be fraudulent. If I were to set up i.e. five different tests(i.e. check email, credit_card number etc. against previous fraud orders), then there would be the possibility that any given order can be flagged 1 to 5 times. I want to record all of these tests within the same column if possible. Therefore the output may look something like the following:
order_number fraud_score 1234567890 a,b,d 5432109876 e 2345678901 null 3455607983 a,b,c,d,e
I was considering adding five additional columns to the table and running five different update steps, but this doesn't appear very scalable. Any suggestions would be greatly appreciated! thanks in advance- trevorb
I am trying to insert 9 rows into an table at the same time. My situation is this...
I have a survey page. There are 9 parts with each part ment for an individual person. Each part has 8 questions, and each part has the same 8 questions
The questions are answered using one of the answers in a drop down box.
So when the surveyer clicks submit, all the 9 parts should be entered into the table.
If this is confusing, I have the form up on the Internet at...
http://www.lavenderlane.ie/wage_survey_test.asp
I can insert one part no problem, (when I reduce the form to only 1 part) but i need to insert all of the 9 parts simultaneously. I reckon its some sort of for loop but if u could help me out i would appreciate it!
I am trying to insert into a row in SQL 2012 a default value for one column and then the results of some select count(*) queries into the other columns.Here is my example:
insert into x64_hourly_activity (Hour,Ingests,Restores,Proxies, Tape_Onsite, Tape_Offsite) select (select count(*) from x64_Ingest_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_restore_metrics_all_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_proxy_metrics_raw_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_Loc = 'XYZ'), (select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_loc = 'JKL')
What is the statement I use to insert 12AM as a value for Hour?
can we insert multiple values into the same field. as we do for the mailing list. that is can we use commas to enter multiple values into the same field
Is it possible to insert multiple rows in a table using one INSERT statment. If yes, how can I do that ? I tried doing this using the substitution method.
Using Substitution Method - This is how,I proceeded.
I'm using Microsoft SQL Server Management Studio with SQL Server 2005 (SP2).
I have not had much experience with t-SQL and iterative logic implementation through SQL, therefore I think my problem is fairly basic for most of the pros here.
Here it goes...
I have two tables at hand, TestTab1 and TestTab2.
TestTab1 contains two attributes (columns) namely account_name (nvarchar(50)) and entry_count (int). For all rows in TestTab1, account_name is unique, i.e. for each account_name there is only 1 row in TestTab1. This is my source table.
TestTab2 contains one attribute namely account_name (nvarchar(50)). This table is empty and is my destination table.
I need to insert X entries (rows) for each account_name into TestTab2 where X is the int value in entry_count againt each account_name in TestTab1.
In other words, I need to insert account_name into TestTab2 as many times as the number in entry_count indicates.
I tried reading through the documentation but the help was not friendly enough for me to understand how to implement this.
I just got started with ASP.NET and SQL Express a few days ago. I have a little bit of SQL (MySQL) experience, but nothing professional. I'm trying to build a toy application just as a refresher for SQL and to learn a few more things.Anyway, I've got two tables:Users:user_id, user_nameRegistration:reg_id, user_id, fname, lname, passwdNow, when someone new registers on the site, I need to update both tables. When I add a user to the Users table, the user_id is autoincremented. I'd like to get that user_id, somehow, so that I can then update the registration table with the correct user_id.What would be the "best" way to do this? SQL query? Stored procedure? Trigger?
Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.
Let's say... Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk) Table B has columns PkB(identity), MoreStuff(text)
I'll be executing SQL statements from my service - INSERTs, etc...
What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.
Is there a way I can insert into both tables with one SQL statement?
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
