Transact SQL :: Insert Records From Two Table Into A Table
Sep 20, 2015
Lets say we have two tables
tblPayments (Contains the records of Payments we made)
ID DATE AMOUNT BANK
----------------------------------------------------------
1 05/05/2015 5000 Natwest
2 05/05/2015 2000 Lloyds
3 05/06/2015 3500 Natwest
4 05/07/2015 4000 Natwest
5 05/08/2015 1500 Lloyds
tblReceipts (Contains the records of Receipts we received)
ID DATE AMOUNT BANK
----------------------------------------------------------
1 05/06/2015 5000 Natwest
2 05/06/2015 2000 Lloyds
3 05/07/2015 3500 Natwest
4 05/07/2015 4000 Natwest
5 05/08/2015 1500 Lloyds
Now, I also have a blank table (tblBankStatement) which contain the following columns
ID DATE RECEIPT PAYMENT BANK
-----------------------------------------------------------------------------
I want that when I execute the query, the query should INSERT the records to the New Table (tblBankStatement) from
tblPayments and tblReceipts by Date Ordered in ascending way WHEREBank should be 'Natwest'.
Also the Amount Column Data in tblPayments should be Inserted into the Payment Column in tblBankStatement and the Amount Column Data in tblReceipts should be Inserted into the Receipt Column in tblBankStatement.
So I could get the data just like below
tblBankStatement
ID DATE RECEIPT PAYMENT BANK
--------------------------------------------------------------
1 05/05/2015 0.00 5000 Natwest
2 05/06/2015 0.00 3500 Natwest
3 05/06/2015 5000 0.00 Natwest
4 05/07/2015 0.00 4000 Natwest
5 05/07/2015 4000 0.00 Natwest
What query should I write to perform the task above.
View 4 Replies
ADVERTISEMENT
Aug 30, 2015
Lets say we are executing this query below to retrieve each customer and the amount associated to a table
"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE - Inbound END) AS AMOUNT from
tblSaleStatementCustomer WHERE CustomerType = 'Dealer' GROUP BY CustomerName"
Which display the data like below
DEALER AMOUNT
------------------------------------------------
ABC 2000
XYZ 1000
However I have one more table TABLE2 which contains two columns
DEALER OPENING
-------------------------------------------------------
ABC 5000
XYZ 7000
I want to modify my query in such a way that AMOUNT column should also add
OPENING from TABLE2 So that I must get the result like below
DEALER AMOUNT
------------------------------------------------
ABC 7000
XYZ 8000
View 10 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Jun 29, 2015
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
View 11 Replies
View Related
Nov 2, 2015
INSERT
INTO [Table2Distinct]
([CLAIM_NUMBER]
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
View 5 Replies
View Related
Sep 5, 2015
How to update tblA with records from tblB (see below expected results) when they have common column DrawingNo?
View 4 Replies
View Related
Jun 25, 2015
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
View 4 Replies
View Related
Jul 14, 2015
I need to find out if a Transaction ID exists in Table A that does not exist in Table B. If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. This would work easily if there were only one row. What is the best way to handle it if there are a bunch of rows? Should there be some type of looping?
View 12 Replies
View Related
Oct 5, 2015
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
Actual Data
Expected Result
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
View 3 Replies
View Related
Aug 22, 2015
we have a table below
TABLE1
ID Roll Name Amount . . . . . . so on
---------------------------------------
1 2 Alex 500
2 5 Jones 600
3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on
---------------------------------------
1 2 Alex 500
3 2 Ales 400
how can I do this in a same sql statement.
View 3 Replies
View Related
Apr 20, 2008
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
View 1 Replies
View Related
Apr 22, 2015
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location
1 4/1/2015 bld1
2 4/2/2015 bld1
3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])
And the results would be:
4/3/2015 bld1
View 17 Replies
View Related
Oct 22, 2015
Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).
What is the best way to find out which query is doing this?
Could be also query which updates only one record(or couple of them) but is executed for each record once.
I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.
I can't use event notifications since there is service broker disabled.
View 5 Replies
View Related
Jul 23, 2015
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
View 10 Replies
View Related
Jul 21, 2015
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table.
2. I am updating in batches say 20000 record-wise.
3. Changed the recovery mode to bulk logged and auto-growth size is set to 300MB and there is enough space in my disk for transaction log .
But still the query is running slowly.
View 10 Replies
View Related
Aug 7, 2015
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
SELECT
[Blogs].[ID],
[Blogs].[BlogTitle],
[Blogs].[BlogType],
[Blogs].[BlogText],
[code]...
View 6 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Jun 5, 2015
I have a table (let's call it MyTable) that consists of four fields:
Id, Source, FirstField, and
SecondField, where Source only takes one of two values:
Source1 and Source2.
The records in this table look as follows:
Id
Source
FirstField
Secondfield
1
Source1
Product 3 name
Product 3 description
[code]...
I need to return, using 3 different T-SQL queries:
1) Products that exist only in Source2 (in red above)
2) Products that exist only in Source1 (in green above)
3) Products that exist both in Source1 and Source2 (in black above)
For 1) so far I've been doing something along the lines of SELECT * FROM MyTable WHERE Source=Source1 AND FirstField NOT IN (SELECT DISTINCT (FirstField) FROM MyTable WHERE Source=Source2)
I have read about INTERSECT and EXCEPT, but I am a little unclear if they could be applied in this case out of the box.
View 7 Replies
View Related
Nov 15, 2015
I have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
View 8 Replies
View Related
Nov 19, 2015
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
View 3 Replies
View Related
Jul 14, 2014
I have a table that holds Customer Part numbers.
Cus_no item_no
100 ABC
100 XYZ
200 ABC
300 XYZ
Looking at the data above. I want look at customer 100 and insert all of customer 100's records where other customers do not match.
Table name ItemCust
After running my script I would expect my data to look like:
Cus_no item_no
100 ABC
100 XYZ
200 ABC
200 XYZ
300 ABC
300 XYZ
View 3 Replies
View Related
Oct 11, 2007
insert into table1 (colname) values (value1)
can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?
We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take
value1, value2, value3 ....
from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks
View 5 Replies
View Related
Jul 26, 2007
Hi:
I need to create a trigger to save every record which has been updated in a table (e.g. "Contacts") into another (e.g. "Shadow_contacts). So basically something like a versioning on database level.
I have no experience with it and any precise hints/solutions would be very appreciated
selmahan
View 1 Replies
View Related
Dec 23, 2011
we have various user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)
This field is a drop down list , after insert committed , i want other user defined field to populate.
Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be populated.
CREATE TRIGGER User_Defined_field_KAE
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER
INSERT
AS
INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Type_Id,
name
SELECT
Client_Id,
'1089',
A.USER_PHONE_1
FROM inserted , [dbo].[ADMN_User_Details] as A
WHERE name = a.user_id;
View 11 Replies
View Related
Dec 29, 2014
I have need to insert records when not in the target table.
sample ddl for source and target tables. Notice the target table is missing the 'Upstairs' Rows:
CREATE TABLE source (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)
[code]....
View 2 Replies
View Related
Sep 17, 2015
I am trying to insert values from a temp table #TableName to a db table.
CATEGORY table- CategoryId, CategoryName, CategoryDescription
Then I did this :
Select CategoryName
into #TableName
from CATEGORY
Now I'm doing this :
Insert into #TestTable values(1,'This is:'+CategoryName)
select CategoryName from #Test
CategoryID right now is not PK.
So, the intention is to insert as many CategoryNames available into TestTable with id 1 and category name edited as shown in Inset statement.
What is wrong with this query. Its not working.
View 12 Replies
View Related
Sep 27, 2011
i need to write a query that insert in A table if and only if the row does not exist already in the table (the source is a select statement).
i tried the following:
insert into [A]
select * from [B]
where Not EXISTS (select * from [A])
table [A] is still empty, but it does not insert any thing! .. what i know is that the EXIST checks if the selected row exists in the subquery, ain't ?
View 9 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Oct 16, 2015
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
View 12 Replies
View Related
May 21, 2008
i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.
View 5 Replies
View Related
Nov 25, 2004
hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
---order no ----orderno
---issuedate ----itemname
---supplier ----desccription
---amount -----price
----Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000
anyone can give me some advise or some sample code ?
thanks in advanced!
View 2 Replies
View Related
Mar 25, 2014
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
View 3 Replies
View Related
