Inserting 2 Tables With Pk/Fk
Oct 28, 2006
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?
Thanks!! Curt.
View 3 Replies
ADVERTISEMENT
Oct 10, 2007
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!!
View 3 Replies
View Related
Feb 24, 2006
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?
View 2 Replies
View Related
Sep 21, 2007
Hi, I have two different tables that I want to insert into, i have the code (as below) my only problem is i want to insert a qty into tbl_multi and a different qty into tbl_stock, how do i do this. I have been comfusing myself with it all afternoon, maybe someone can point it out for me int i = 0;
int scount = 0;for (i = 1; i <= 5; i++)
{if (((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text != "")
{string sConnMAXID = "Data Source=sql380.mysite4now.com;Initial Catalog=nashdfDB1;User ID=******;Password=******";
SqlConnection objConnOID = new SqlConnection(sConnMAXID);int intStockIDMAX = (Convert.ToInt32(Session["intMAXSID"])) + 1;
int intQtyP1 = Convert.ToInt32(((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text);int intLocationP1 = Convert.ToInt32(((DropDownList)FormView1.FindControl("ddlP1_" + i.ToString())).SelectedValue);
scount = intQtyP1 + scount;using (objConnOID)
{
objConnOID.Open();string sqlInsert = "INSERT INTO tbl_stock_multi_location(stock_ID, qty, location) " + "VALUES (@stock_ID, @qty, @location);"; <<<<<<<<<<<<<<<< need to try and add the SQL onto the end here
//INSERT INTO tbl_stock(qty)" +// "SELECT qty AS SQTY FROM tbl_stock VALUES (@SQTY);";
SqlCommand objCmd1 = new SqlCommand(sqlInsert, objConnOID);
objCmd1.Parameters.AddWithValue("@stock_ID", intStockIDMAX);
objCmd1.Parameters.AddWithValue("@qty", intQtyP1);objCmd1.Parameters.AddWithValue("@location", intLocationP1);
// objCmd1.Parameters.AddWithValue("@tbl_stock.qty", scount); <<<<<<< this was the param that i was playing about with
// either will work
// objCmd1.ExecuteNonQuery();
objCmd1.ExecuteScalar();
objConnOID.Close();
View 1 Replies
View Related
Feb 22, 2004
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 !!!
customer ----link------customergaurdian-----link-----gaurdian
is there some kind of statement or something that gaurantee that insertion will take place in this form?
View 3 Replies
View Related
Mar 24, 2004
Hi all,
I am trying to insert user's input from a web form into the tables. For example,
PURCHASE TABLE(PurchaseID, PurchaseNo, Date, PartID)
PART TABLE(PartID, PartDescription,MachineID)
MACHINE TABLE(MachineID, MachineName)
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Thanks!
View 2 Replies
View Related
Apr 19, 2000
I have a shopping cart app in ASP. I don't want to upload to the DB until the credit card goes through. Once the credit card goes through, I update my tables: Shipping(info), Orders, and Billing(info).
I'm not done yet though. I have to loop though the shopping cart and pass the paramters to an SPROC each time through the loop. If I have three items in the cart, then I need to call the SPROC three times.(let me know if there is a better way to do this)
So I am thinking I will need 2 SPROC's, one for the first three tables, and one for the OrderDetail table. My concern is grabbing the proper foreign key from the Order table. If I use SELECT MAX(id), I may get the wrong ID if someone elses has inserted another row before I grab the proper ID.
I need to figure out how use my second SPROC to grab the proper ID from the Order table, and then insert that record into the OrderDetail table.(Am I being paranoid about another record being inserted? I think this could happen between the execution of my first SPROC and the second one.)
Thanks all
View 1 Replies
View Related
Dec 9, 2005
Sorry, I made a mistake. How can I delete this message?
View 3 Replies
View Related
Jul 20, 2005
Anyone who can help,I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fieldsK2 and F1. F1 is the foreign key relating to K1 and F2 is the foreignkey relating to K2.My initial question has been how can I add a row to both tables withaccurate Fx values in one SQL INSERT statement? I have been told itcannot be done.If it can be done in one statement, what is that statement?If it cannot be done in one statement, how can it be done in the leastnumber of statements?Thanks,Greg the SQL student
View 4 Replies
View Related
Jun 22, 2006
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
View 1 Replies
View Related
Dec 7, 2007
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
View 4 Replies
View Related
Jan 25, 2008
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..
View 6 Replies
View Related
Dec 2, 2003
Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?
View 2 Replies
View Related
Sep 3, 2013
Say for instance I got 2 tables
Subject Table and a Student Table
The Subject Table consist of the following attributes:
Subject_ID [PK], Subject_Name, Course_ID and Course_Name
The Student Table consists of the following attributes:
Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight
How can I use the INSERT function when I would like to add a row with the following details:
Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54
how to use the INSERT function for multiple tables.
View 4 Replies
View Related
Jun 30, 2006
CREATE PROCEDURE SimpleInsert (@custname custname_type,@carname carname_type)ASBEGIN TRANSACTIONINSERT Customers (CustName) VALUES (@custname)IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 ENDDECLARE @custid intSELECT @custid = scope_identity()INSERT Cars (ID, CarName) VALUES (@custid, @carname)IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 ENDCOMMIT TRANSACTIONIF no error this works but to test transaction, I chanded the tablename of second insert to 'car' in which doesn't exist and this erroroccured (calling sp by ASP .NET page):Invalid object name 'car'. Transaction count after EXECUTE indicatesthat a COMMIT or ROLLBACK TRANSACTION statement is missing. Previouscount = 0, current count = 1.ThanksHabib
View 2 Replies
View Related
Jul 20, 2005
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies
View Related
Nov 26, 2007
hi all
I have been posting in VB.NET forum ,but i am new to this forum.
I am working on an application using vast amount of data . So we get data in flatfiles. We used to get data for two or three months in more than three or four flat files .Some time we try to load the same files more than once .So it leads to duplicates in tables
I heard that there is some settings in table , so that it will not allow the same data once it is imported .
if anybody knows about this please help me
thanks & regards
View 6 Replies
View Related
May 14, 2007
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2
My Problem:
It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.
Total Specification Requirements:
i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.
Any Help would be graetlty appreciated
Kind Regards
Carel Greaves
View 4 Replies
View Related
Apr 10, 2007
i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key
View 3 Replies
View Related
Feb 7, 2007
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
View 5 Replies
View Related
Feb 8, 2007
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are:
tbltest
ECID – int (PK)
View 2 Replies
View Related
Dec 5, 2007
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.
View 2 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
Jun 2, 2005
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
View 1 Replies
View Related
Jun 18, 2007
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
View 3 Replies
View Related
Jul 23, 2005
Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou
View 1 Replies
View Related
Jul 20, 2005
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
View 5 Replies
View Related
Jul 30, 2007
Hi
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
Kind Regards
Carel Greaves
View 3 Replies
View Related
Jun 10, 2015
I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
View 3 Replies
View Related
May 26, 2008
Hi All,
In my DataFlow i have OleDBDataSource and OleDB Command. Using these i am inserting data to master and child tables.
In OleDBDataSource , i am inserting into master table and returning the ID of newly inserted rows. Next in the OleDB Command, i am inserting to child table using the ID returned from OleDBDataSource.
It is working fine. Now i want to put this in the Transaction so that if it fails to insert into child table, the changes made to the master table should be rolled back. I tried by giving Transaction Supported for dataflow. But does not looks like it works for me. Please suggest me the best approach for this.
Thanks in advance
DV
View 2 Replies
View Related
Feb 27, 2008
So I have been unfortunate enough to inherit a whole estate of sql servers running a collation of Latin1_General_BIN.
I have a batch of maintenance and monitoring scripts that I want to implement on these servers. However I seem to have problems compiling the stored procs as the case of the column names varies throughout the script and table definitions.
Ok I know I could recode them, but is there way to overide the case sensitivity in insert and update statements so they dont have to be recoded?
View 1 Replies
View Related
Feb 6, 2007
I'm trying to list out my a multi-value parameter in a table or list. Is this possible?
So far I've tried the simple do-it-the-same-way-as-a-dataset approach, and that doesn't work because it comes back with an error saying I need a dataset in my table.
I then tried creating a dataset that's identical my multi-value parameter, but I was stumped when it came to creating the correct SQL. I am able to pull the last value of my parameter into a dataset with a simple SELECT @MyParam query, but that's not going to cut it...
Any more ideas? Thanks!
View 1 Replies
View Related
Jul 13, 2007
string fileName = "d:\shiporder.xml";
DataSet dataSet = new DataSet();
dataSet.ReadXML(fileName);
//connection string
string cmd = "INSERT INTO Orders (OrderID, OrderPerson) VALUES (dataSet.Tables[0].Row[0][0].ToString(),dataSet.Tables[0].Row[0][1].ToString);
SqlConnection con = new SqlConnection(conection string);
SqlCommand mycmd = new SqlCommand(cmd,con);
con.Open();
mycmd.ExecuteNonQuery();
con.Close();
it gives me this error
dataSet.Tables its not permitted in this context. valid expression are constants, constant expression, . Columns name are not permitted
View 2 Replies
View Related
