insert query by using select statement

Atul Rokade

Hi all

Actually i have two tables

Suppose table name is Table A and Table B

in Table A having User_name,Password,Unique_no field

where Table B having User_name,Unique_no,Sales_count, Today_date field

Now, what i want, in Table B when i trying to do insert query at same time query should be extract User_name and Unique_no base on Unique_no condition + query can be insert sales_count+1 and today date also

for this i make a query

insert into Table B select User_name,Unique_no from Table A where Unique_no =@Unique_no

but not understand how can i insert sales_count increament and today date into Table B At same time

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Atul Rokade10y ago
Hi friends still im not able to find solution im using ms-access as database so i unable to write sp and trigger i have to manage this all things from coding only one more time im giving my requirment hope this time my problem solve, pls read fully
I have two tables Sales_details and Login in Login table have
User_name, Password,Unique_no field( this data filled whenever new user added), Now i have Sales_details table(User_name,Unique_no,Sales_count,To_date) in this table sales detail has enter base on Login table,
Now what i exactly want when salesman done the sales at that time His user_name,unique_no extracted from login table and inserted into Sales_details but while inserting into sales details Sales_count should be 0 by default value when he try to first sale into the day afterwords sales count can be update as per his sales and To_date should be todays date
Now i able to extract User_name and Unique_no from Login to inserted Sales_details but i unable to insert by default value 0 in sales_count and Today date into To_date
note: im using ms-access database
insert into sales_tables(User_name,Unique_no) select User_name,Unique_no from login where unique_no=@unique_no;
Atul Rokade10y ago
Thank you friends for reply but im using access database so how could i used trigger or store procedure in there?
@Nitin sir : Actually i just want extract data from Login table and put into Sales_Details table base on unique_no field and after extracting value should be increment sales_count should increment as 1 and also insert today date in table i dont need any increment in Login table only extraction i want from Login table After extraction done successfully Sales_Details have sales_count,To_date field that should be inserted
suppose im extracting two fields in login table user_name PV Sindhu and Unique_no 1234
now this to value i want to insert in Sales_Detail table
User_name Unique_no Sales_count To_Date
PV Sindhu ,1234, Sales_count(1),19/08/2016
Midhun Tp10y ago
Hi Atul,
Try this query -
INSERT INTO Sales_Details
SELECT lg.User_name,lg.Unique_no,(isnull(sd.Sales_Count,0)+1),getDATE() FROM Login lg LEFT JOIN Sales_Details sd ON lg.Unique_no=sd.Unique_no WHERE lg.Unique_no=@Unique_No
Bhuvan Pandey10y ago
You have to create stored procedure in SQL Server to perform two operation
Operation 1) Select data from Table A and store this data into local variable.
Operation 2) Write insert statement into Table B and store new data and stored data (selected by Table A)
If you have any doubt to create stored procedure, kindly let me know. I will try to create a complete stored procedure.
Nitin Sontakke10y ago
Let me try to put it in my words and please tell me if I am right.
Login and Sales details are two related tables and they are related by common column User Name and Unique No.
A column in Sales details you want to increment and an incremented value you want to keep track of in the Login table.
Please tell me if my understanding is wrong. If it is wrong, please clarify further.
If you ask me, you are duplicating the information and that is a bad design practice. It would be nice to know why you want to do it.
If for some reason, information in two tables is not in sync we have hard time deciding which piece of info is correct.
Please take time to think and decide now, it would be helpful later.
If you still want to go ahead you can use trigger as Tapan suggested.
Tapan Patel10y ago
Why don't you write a trigger on Insert - for Table A. So that, when you insert a record into Table A, it can insert the desired fields into Table B and also, it can do the count calculation for you (You just need to add a simple select query to get count for that Unique No).

This way, you can get rid of those code in C# and rest of thing would be taken care by Trigger.

Just a thought.
Atul Rokade10y ago
Hi nitin sir here is my whole code which is right now im work
string todaydate = DateTime.Now.ToString("dd/MM/yyyy");

String access = "select To_Date from Sales_Details";
cmd = new OleDbCommand(access, con);
con.Open();
using (OleDbDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
string date2 = read["To_Date"].ToString();
if (todaydate == date2)
{
cmd = new OleDbCommand("update Sales_Details set Sales_count= IIF(IsNull(Sales_count), 0, Sales_count) + 1, [To_Date]=Date() where [Unique_No]=@Unique_No", con);
cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
con.Open();
int n = cmd.ExecuteNonQuery();
if (n == 0)
{
MessageBox.Show("Invalid Unique No.");
}
else
{
this.DialogResult = DialogResult.OK;
}
con.Close();

}
else
{
int counter=1;
// cmd = new OleDbCommand("insert into Sales_Details select User_name,Unique_No from Login where Unique_No=@Unique_No Union select ISNULL(Sales_Count+1,0) as [@Sales_Count],DATE() AS [To_date]", con);
cmd.Parameters.AddWithValue("@Unique_No", txtinput.Text);
cmd.Parameters.AddWithValue("@Sales_count",counter);

int n1 = cmd.ExecuteNonQuery();
if (n1 == 0)
{
MessageBox.Show("Invalid Unique No.");
}
else
{
this.DialogResult = DialogResult.OK;
}

}
}
}
con.Close();
Atul Rokade10y ago
Hi nitin sir
Now im directly telling you tables name
Two tables is there Login and sales_details
in login have User_name,Password,Unique_No field
and in sales_details having User_name,Unique_No,Sales_count and To_date field
login details are enter in Login table when new user is created
in sales_details when user enter some details when sales_count should be 1 at first time when same user enter other at same day then sales_details then sales_count will be 2
and i dont understand What is the source of sales_count column? this?
Nitin Sontakke10y ago
Forget the Inset into Table B.
Just concentrate on the select first. Insert part is simple afterwards.
What is the source of sales_count column? Today_date may be managed by just getdate().
select user_name, unique_no, ??? [sales_count], getdate() [today_date]
from a