insert query by using select statement

Question

Hi all Actually i have two tables Suppose table name is Table A and Table B in Table A having User_namePasswordUnique_no field where Table B having User_nameUnique_noSales_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_nameUnique_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

Tapan Patel · Answer

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 Rokade · Answer

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 PasswordUnique_no field( this data filled whenever new user added) Now i have Sales_details table(User_nameUnique_noSales_countTo_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_nameunique_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_nameUnique_no) select User_nameUnique_no from login where unique_no=@unique_no;

Atul Rokade · Answer

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_countTo_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 T P · Answer

Hi Atul Try this query - INSERT INTO Sales_Details SELECT lg.User_namelg.Unique_no(isnull(sd.Sales_Count0)+1)getDATE() FROM Login lg LEFT JOIN Sales_Details sd ON lg.Unique_no=sd.Unique_no WHERE lg.Unique_no=@Unique_No

Bhuvan Pandey · Answer

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 Sontakke · Answer

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.

Atul Rokade · Answer

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_nameUnique_No from Login where Unique_No=@Unique_No Union select ISNULL(Sales_Count+10) as [@Sales_Count]DATE() AS [To_date] con); cmd.Parameters.AddWithValue(@Unique_No txtinput.Text); cmd.Parameters.AddWithValue(@Sales_countcounter); int n1 = cmd.ExecuteNonQuery(); if (n1 == 0) { MessageBox.Show(Invalid Unique No.); } else { this.DialogResult = DialogResult.OK; } } } } con.Close();

Atul Rokade · Answer

Hi nitin sir Now im directly telling you tables name Two tables is there Login and sales_details in login have User_namePasswordUnique_No field and in sales_details having User_nameUnique_NoSales_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 Sontakke · Answer

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