.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

SQL Update and Insert: Inserting a whole data table into another table

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :ASP.Net
 

Hello,

If I create a data tabe in C# like so:

datatable t = new DataTable;

DataColum C1 = new DataColum;//ID column

t.Columns.Add(C1);

//the same with column C2

Then I fill up the table with some data.

Then the goal is to either insert this data or update data in an SQLtable in SQL. If the data is inserted, the ID column in t would be empty, if updated, it would not be. So in the insertion case, the sql query needs to make sure that unique IDs are assigned during insertion, in the update case, it just has to look for the right IDs.

How to go about this?

Asking because I am only familiar with updating and inserting single lines into a table. One could loop in C# over the datatable t of course and insert or update line by line, but this would probably be slow. Is there a way to write an SQL query like this somehow:

Insert Into SQLTable Values (@t)

?

And Update like this:

Update SQLTable SET ... Where C1 = @t.C1

Or something like that.

I know that SQL server can create automatic update and insert queries and that you can then type things in C# like

Adapter.Update(t);

But, this has proven to be super slow on our server, for unknown reasons. I have asked on this forum and got some answers from experts, but was not able to srot out the problem.

How can we write


View Complete Post


More Related Resource Links

"The data source control failed to execute the insert command." when inserting to sql table with tri

  

Hi,

I have a data view web part that inserts a new record into a sql table. This works fine.

I have now needed to add a trigger to the sql table that sends an email when a new record is inserted (the code for which works fine) but now my insert fails with 'The data source control failed to execute the insert command.'.

If I disable the trigger the form works fine again.

Any ideas what could be causing this?

Jeni


reading xml data and insert into database table

  
hi friends, .very urgent for me so only i send following script i create for read xml data and to import to table. i got result for reading xml and rows are added to table but the values are showing null pls clarify this problem. thanks alter procedure pizza_sales ( @xml xml ) as begin INSERT INTO pizza_xml (LOC_CODE, CUST_CODE, PRODUCT_ID, INV_TYPE, INV_NO, INV_DT, INV_QTY_IN_INV_UOM, INV_RATE_IN_BS_CURR, INV_VU_IN_INV_CURR, -- TOTAL_DISC_IN_BS_CURR, NET_INV_VU_IN_INV_CURR, NET_INV_VU_IN_BS_CURR, TOTAL_TXES_IN_BS_CURR ) select table1.column1.value('@LOC_CODE','nvarchar(75)'), table1.column1.value('@CUST_CODE','nvarchar(75)'), table1.column1.value('@PROD_CODE','nvarchar(75)'), table1.column1.value('@INV_TYPE','nvarchar(50)'), table1.column1.value('@INV_NO','nvarchar(100)'), table1.column1.value('@INV_DT','datetime'), table1.column1.value('@INV_QTY_IN_INV_UOM','numeric(28,8)'), table1.column1.value('@INV_RATE_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@INV_VU_IN_INV_CURR','numeric(28,8)'), --table1.column1.value('@TOTAL_DISC_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_INV_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@TOTAL_TXES_IN_BS_CURR','numeric(28,8)') from @xml.nodes('pizza/pizzaxml')as table1(column1) end declare @id

update data on table

  
i have  100000 record  found in the  tha table and  i want  to update the column SocialState when found male he replace  the text male to 1 and when foun Female  he replace the text to 2   update [Drugs].[db_accessadmin].[Data] set SocialState='1' where SocialState='Male' update [Drugs].[db_accessadmin].[Data] set SocialState='2' where SocialState='FeMale' he give me this message "(0 row(s) affected)"

pull data from table A and insert into the same table

  
Hi All, When I pull data from one table and try to insert into the same table, is it possible for any erroneous data loading? ex:   Insert Into TableA Select Cat,Bank, City, Act From TableA A CROSS APPLY (SELECT COUNT(ACT) AS TOT from TableA ) B   Thanks        shamen

How to insert values into my table the data is coming from inline table valued function

  
Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

automatic insert data to mysql table

  
hi all,             Actually i need to transfer data from one table to another table in mysql with codition of Time.for example i need to transfer data at 10.00am when user login at 10.00am it must be insert into another data table.. if anybody know time control  please reply me..C#Thank You,Arunabathan.G

inserting an array of data into a database table

  
im looking to insert an array of information into a database tableessentially what id like to do is something like this:            objCmd = New OleDb.OleDbCommand("INSERT INTO pluINFO VALUES *", objConn)            For j = 0 To 265                objCmd.Parameters.Add( info(j) )            Next this code gives me a syntax error for the insert statement is there anyway i can set the insert statement that would allow me to add each element of the array(looping through rather than hardcoding several hundred entries) into the table?any input appreciatedThanks

SQL Checking the table before insert or update

  
I need your help with a SQL Stored Procedure PLEASE!!!GOAL: I need to check TableA for the ItemID if the ItemID Is Not found Insert a new record. If the ItemID Is found Update existing record.***********************Below is Incorrect***********************Please HELP!!!!IF @ItemID is not null          Set NewRecord =  Select ItemID FROM ContentItems Where EXISTS  (select ItemID from ContentForm_Fields where ItemID=@ItemID)IF NewRecord is not null   Update ContentForm_FieldsELSE Insert ContentForm_Fields

inserting one table data to another after filtering nulls and duplicates

  
hi i have tbl_ip i want to filter nulls and duplicate to my tbl_ipfilter can u suggest me a stored procedure for this. data of tbl_ip is like below . ip 27.24.83.105 NULL NULL NULL 92.20.83.203 92.20.83.203 67.21.83.315

Update one column of a table with data from another table

  
I have two tables which are different with the exception of a single field (column name = "LocationCode"). Both tables can also be joined with a common id field. I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id. It is impractical for me to update one row at a time. How can I update all the rows quickly?   But one more point both tables lies in different database Varinder Sandhu http://varindersandhus.blogspot.com/

Problem when Inserting xml data into a table using OpenXml

  
Hi, i stuck with a problemam trying to insert data from xml file to sql server.i made query like thisSET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, '<NewDataSet>  <xs:schema id=''NewDataSet'' xmlns='''' xmlns:xs=''http://www.w3.org/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-com:xml-msdata''>  <xs:element name=''Job''>     <xs:complexType>      <xs:sequence>        <xs:element name=''BillToNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''Colors'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CompanyNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrCode'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrName'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CustomerPoNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''DivisionOriginal'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''EstimateNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''FinalDeliveryDate'' type=''xs:string'' minOccurs=''0'' /> <xs:element name

Update one column of a table with data from another table

  

I have two tables which are different with the exception of a single field (column name = "LocationCode").

Both tables can also be joined with a common id field.

I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id.

It is impractical for me to update one row at a time. How can I update all the rows quickly?

 

But one more point both tables lies in different database


Varinder Sandhu http://varindersandhus.blogspot.com/

inserting data to Users Table in ASPNETDB from Create User Wizard

  

When a user starts a membership using the Create User wizard supplied by the aspnetdb profile service, I need to add a field to a table in aspnetdb and upon clicking the ContinueButtonClick insert some data.  The field I'll add is "SubscId".  My first question is what table is best for this - Users or Membership?

Secondly, what is the best way to insert new data for this scenario?  code behind?  markup?  Can someone provide basic syntax of insert code in code behind for this scenario?


How can we insert csv file data into Sql server table based on some condition ?

  

Hello All experts there,

 

I need to insert some csv file data into sql server 2008. but there are multiple tables created in sql server and I need to check some conditions before data insertion and based on the condition value, sql script should insert the data in to specific table. in other words I need to insert data from one csv file to different tables based on some condition.

is it possible? if yes then can anyone please give me an idea on how can we do that?

 

Thanks in advance

Regards

Nipendra Garg


how to insert data present in different rows of same collumn of one table into same rows of differn

  

Hi,

I am have task that i have to insert data present in different rows of same collumn of one table into  same rows of differnt collumn of other table below shows the table structure source & distination

c_date                c_balance

11/1/2010          100

11/2/2010          200

11/3/2010          300

     ----                ----

  -----                ---

12/1/2010          150

12/2/2010          300

12/3/2010          400

   ---                  ----

   ----                  ---

like above i have thousands of data with other collumns

now i have the detination structure  as

Inserting and querying data on the same table

  

I need to implement a logic where in I insert or update a table in a loop.If the record is inserted then I need to update the table.To check this I have given IF NOT EXISTS statement with a query. But IF NOT EXISTS always returns true, so many records are getting inserted and update always fails

How do I resove this problem


Inserting 15 minute Interval Data in a table

  

What is the most efficient way of creating a 15 minute increment table. This table needs to continue to increment with time so its not a static table. While loop? how will that affect the performance, if I have to add dates starting 2005 till today and then basically after that with every hour a value needs to be added.. How can I go about doing that?

I am using SQL 2008

 Thanks -Sarah


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend