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


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

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server
 
This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is


View Complete Post


More Related Resource Links

How to declare a table variable in SSIS and then insert rows into it

  
Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

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

Insert Muiple rows Into Table using Entity Framework ??

  

Dear All,

 

i'm trying to use Entity Framework to insert into SQL SERVER 2005, using Visual studio 2008.

the following codes are a Button onclick event,

I want to insert into DB multiple rows at a time

protected void Button1_Click(object sender, EventArgs e)
    {
        MyTable NewRow = new MyTable();

        NewRow.columnName = "5";  //columnName is a primary key
        DbEntity.AddToAllowRevise(NewRow); 
        DbEntity.SaveChanges();

        NewRow.columnName = "6";
        DbEntity.AddToAllowRevise(NewRow);
        DbEntity.SaveChanges();
    }


however this does not work,

it seems we can only insert into DB only one row at a time (??)

if i write :

MyTable NewRow1 = new MyTable();

NewRow1.columnName = "5";

 

MyTable NewRow2 = new MyTable();

NewRow2.columnName = "6";

 

MyTable NewRow3 = new MyTable();

NewRow3.columnName = "7";

....

MyTable NewRow10 = new MyTable();

it will success.

but what if i have more rows, like 20~30 row? i have to seperately write 20~30

MyTable NewRow20 = new MyTable();  ........... MyTable NewRow30 = new MyTable();

 

?? sorry i'm a beginn

How to perform insert, update, delete and select rows in ASP.NET gridview control using stored proce

  
In this article, we will explore how to add, update, delete and select a record from sql server database using gridview control in a simple way. I'm using visual studio 2010 and sql server 2008 for this sample.

Insert Rows with a GridView

  
In ASP.NET version 1.x, I used the footer on the DataGrid control to insert new rows by placing a series of TextBox, DropDownLists, CheckBoxes etc. controls on it and then handling a save button. This also works with the GridView in version 2.0 but with one major exception: if your data source is empty, the GridView will hide your footer (and header) and only display the EmptyDataText.

How to use the table which is created with CTE(Common Table Expression) more than one select,insert

  
Hi,

I faced one problem while working with Common Table expressions in Sqlserver 2008.

the problem is How to use the table(means temporary table)  which is created  by with  CTE's after that one select statement.

Actaully the table(which is created using with CTE) is automatically deleted, one's after execution of any select,insert,update or create view statement .Right!

But I needed that table second time also. How it is possible.

please help if any one knows..........

Thanks in Advance........

Pavan.

Insert multiple rows with a single INSERT statement

  

With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?


multiple insert rows using Entity Framework

  

Dear All,

 

i'm trying to use Entity Framework to insert into SQL SERVER 2005, using Visual studio 2008.

the following codes are a Button onclick event,

I want to insert into DB multiple rows at a time

protected void Button1_Click(object sender, EventArgs e) 
{ 
	MyTable NewRow = new MyTable(); 
	NewRow.columnName = "5"; //columnName is a primary key
	DbEntity.AddToAllowRevise(NewRow); 
	DbEntity.SaveChanges(); 
	
	NewRow.columnName = "6"; 
	DbEntity.AddToAllowRevise(NewRow); 
	DbEntity.SaveChanges(); 
} 


 

however this does not work,

it seems we can only insert into DB only one row at a time (??)

if i write :

MyTable NewRow1 = new MyTable();

NewRow1.columnName = "5";

 

MyTable NewRow2 = new MyTable();

NewRow2.columnName = "6";

 

MyTable NewRow3 = new MyTable();

NewRow3.columnName = "7";

....

MyTable NewRow10 = new MyTable();

it will success.

but what if i have more rows, like 20~30 rows? i have to seperately write 20~30

MyTable NewRow20 = new MyTable();  ........... MyTable NewRow30 = new MyTable();

 

?? sorry i'm a beginner now, please anybody can help? better idea?<

How to insert column wise data as a seperate rows using ssis

  

Hi All,

I have my data in excel like this ....

SNO LOAN ID CM ACM RCM
1

how to insert new rows before last rows, the last row allway is Sum of ...

  

i have a table with 50 columns , in each day user will insert data, i want user will insert data by insert rows before the last row , and the last row allway bring the value is sum of data of that day  , not all days 


ado command does not insert all rows

  

In my C++ code, when I try to run the ADO command to insert rows into a table, it only inserts a certain number of rows. The same command works when using System.Data.SqlClient in .NET. 

Sql profiler shows the same textdata. Below is my insert commandtext, I'm not sure what else to do to uncover the issue here. Any help on this is appreciated.

Command text : 

declare @i int set @i = 1 while (@i < 255) begin insert into table1 (name,type, order, state) values (@i, N'type',0,0) set @i = @i +1 end

The above command ends at 153 rows. Is this dependent on table size ?

This is my connection string and the code I'm using to build the connection :

CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);
ADODB::_ConnectionPtr m_Conn = NULL;  
TCHAR connString[255];
  _stprintf(connString, _T("DRIVER=SQL Server;SERVER=np:(local)\\MyInstance;DATABASE=test;"));

   HRESULT hr;
   hr = m_Conn.CreateInstance(__uuidof(ADODB::Connection));
   if (hr != S_OK) {
     wprintf(_T("CreateInstance(__uuidof(ADODB::Connection)) failed. Error: %d"), hr);
     return 0;
   }

   m_Conn->ConnectionTimeout = 1800;
   m_Conn->CommandTimeout = 1800;

   hr = m_Conn->Open((LPCTSTR)connString, _T(""), _T(""), ADODB::adConnectUnspecified);
   if (hr != S_OK) {
     wprintf(_T(&

Insert value using Table Value Functions

  
a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Need to add value to table on insert

  

Hi,

I'm fairly new to .NET.  I need to add values CreatedBy, CreatedDate, ModifiedBy, ModifiedDate to the record before inserting it to the database. 

Please see code below. 

     [MetadataType (typeof(GRIPSMetaData))]
    public partial class SchoolDistrict
    {
        internal void OnSaving(ChangeAction changeAction)
          {
            if (changeAction == ChangeAction.Insert)
            {
              this.CreatedBy = 5;
              this.CreatedDate = DateTime.Now;
            }
            else if (changeAction == ChangeAction.Update)
            {
                this.CreatedBy = 5;
          &nb

How to setup db diagram to automatically insert username into table?

  
 I have a table setup in the database for users who are basically going to create a classified listing.  How can I setup a diagram that would easily create a table relationship with the Users table, so that it automatically inserts the users username when they submit a new ad?  If so, how?  I've tried adding a UserName row to my table and creating a relationship with the Users table "UserName" row, but it wasn't accepting the relationship when I tried to save the diagram...apparently because the UserName row in the Users table isn't a primary key?Also, one this is setup, what's the best way to provide the user a way to see a list of their ads and have the ability to edit them?

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

create and insert into a dynamic sql table at runtime

  
Below I have a bunch of SQL statements that creates a table at runtime and the "pic" column is a dynamic column and the code creates the number of "pic" columns depending on the count of the uploader control.  the problem comes when I try to insert into that table and I am trying to say for every "pic" declare variable @pic.... it works well for just one file in the uploader but for multiple files i get the following error The name "pic1" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Directory.CreateDirectory(MapPath(".") & "\port\" & clientname.Text & "\")        Dim objConn As New SqlConnection("Data Source=xxxxxxxxx.hostedresource.com; Initial Catalog=mrpoteat; User ID=xxxxxxx; Password=xxxxxxxxx;")        objConn.Open()        Dim strCommandText As String = ""        For index = 1 To Attachments1.Items.Count Step 1            If String.IsNullOrEmpty(strCommandText) Then                strCommandTex

How to insert the Current date and time in to SQL Table..

  
Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 
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