How To Combine Two Tables (not Query)
Apr 6, 2005
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
View 3 Replies
ADVERTISEMENT
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Dec 17, 2013
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
View 2 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
View 7 Replies
View Related
Sep 20, 2007
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
View 5 Replies
View Related
Mar 5, 2005
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
Here is a sample of the two databases:
Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124
Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745
I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated.
Thanks
View 2 Replies
View Related
Jan 31, 2008
Im trying to combine 2 unrelated tables to create a new table with all the data. Both tables have the same number of rows. if i have 2 tables with 1 column each and 5 rows in each column, eg.
tb1
c1:
1
2
3
4
5
tb2
c2:
a
b
c
d
e
if i run the query
SELECT tb1.[c1], tb2.[c2] INTO tb3 FROM tb1, tb2
i get
tb3:
c1: c2:
1 a
1 b
1 c
1 d
1 e
2 a
2 b
2 c
........
how do i get
tb3:
c1: c2:
1 a
2 b
3 c
4 d
5 e
thanks,
View 9 Replies
View Related
Nov 2, 2003
I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.
My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?
Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6
New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?
View 6 Replies
View Related
Apr 11, 2008
If i have to get the statuses from four different tables but want to get first table disabled , second table active and also have statuses field from the 3rd and 4th table how would i do that?
ALM,
PV,
AD,
ACE
select * from all tbls
where alm.id = pv.id
and alm status = 'disabled'
and pv status = 'active'
I want to include the status field from 3rd and 4th table.
THanks
View 1 Replies
View Related
May 23, 2008
Hi,
I have 2 tables called Table A, Table B,
In Table A i am having Data1, Data2 like 2 datas in Column 1
In Table B i am having Data2, Data3 Like 2 datas in Column 1
Now want a output like
Data1,
Data2,
Data3
Please help me to get this....
Thank you,
Senthil
View 4 Replies
View Related
Jul 6, 2015
I have 4 table
TBLA
id
1
2
3
----
TBLB
ID COUNT
1 2
2 2
3 2
----
TBLC
ID COUNT
1 2
2 2
----
TBLD
ID COUNT
1 3
2 3
NEED TO GET OUT PUT LIKE BELOW
ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2
View 2 Replies
View Related
Oct 6, 2015
I have 2 tables. first table contains name and age and the second table contains class and roll num. I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
View 8 Replies
View Related
Oct 29, 2006
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
Thanks
View 9 Replies
View Related
Dec 20, 2007
how to
how to combine 2 tables but like this
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
table 2
table Employee on holiday
------------------------
empid start_date end_date shift
-----------------------------------------
12345678 11/04/2007 31/04/2007 10
98765432 01/04/2007 19/04/2007 10
------------------------------------------
how to create a view that show me and combine the 2 tables
all month from first day of the month until the end of the month like this
-----------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 10
12345678 12/04/2007 10
12345678 13/04/2007 10
12345678 14/04/2007 10
.................................
.................................... ...................add the missing date until the end of the month
12345678 31/04/2007 10
98765432 01/04/2007 10
.................................... ...................add the missing date from the start of the month
98765432 02/04/2007 10
................................
..............................
98765432 16/04/2007 10
12345678 17/04/2007 10
98765432 18/04/2007 1
98765432 18/04/2007 10
98765432 19/04/2007 10
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
---------------------------------------
TNX
View 5 Replies
View Related
May 13, 2014
I am looking for a way to combine the following two tables to get a result set that would look like this:
1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red
[Code]....
View 6 Replies
View Related
Oct 11, 2006
I got two tables from 2 different server A and B.
I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"
I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"
i want to combine these two OLEDB sources with a resultset
"select ID, currencyNo, currencyName, exchangerate from table A , B
where A.currencyNo = B.CurrencyNo"
how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?
View 7 Replies
View Related
Jul 1, 2015
I need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
CREATE TABLE [dbo].[Blogs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BlogTitle] [nvarchar](200) NULL,
[BlogText] [nvarchar](max) NULL,
[Tags] [nvarchar](200) NULL,
[Code] ....
View 3 Replies
View Related
Sep 17, 2012
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
View 11 Replies
View Related
Mar 28, 2008
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View 10 Replies
View Related
Apr 5, 2012
I have write two query but its only work one at a time need your expertise what i am doing wrong.
[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID
View 2 Replies
View Related
Feb 4, 2014
I am trying to write a SQL statement query, that will calculate the SUM value in one table for a March period, and update that value to another table, here is what I have so far, but the compile error says Operation must use an updatable query.
UPDATE League_Totals SET League_Totals.March_Total = (SELECT SUM(Result_Value)
FROM Result_Details
WHERE LEAP_Date = "March_2014");
View 6 Replies
View Related
Aug 15, 2003
Is it possible to combine a detailed query with its related count and sum without using any #temp tables at all?
ex. select customerID, customerName,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'active') as countActive,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'inactive') as countInActive
from rfCustomers
something like that? I was heard SQL2k has some new feature like this, or a UDF may be required? Currently, I have to use #temp table to get it.
thanks
David
View 4 Replies
View Related
May 3, 2007
I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
View 3 Replies
View Related
May 13, 2004
SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View 9 Replies
View Related
Jun 2, 2008
This might be a question with an extremely easy answer.. I don't know but here I go.
I want a report with lets say
|A | B | C |
----------------
I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?
so lets say I have
select cola as A from table1 where ....
select colb as B from table2...
They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )
How would I do this? Am I missing something?
View 5 Replies
View Related
Jun 5, 2014
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
View 2 Replies
View Related
Dec 28, 2007
The following query gets all the data I need except for one new field that I need which combines multiple fields and some text. Here is the query:
SELECT [Make Mods-Additions HERE].StockNumber AS ProductID, [Make Mods-Additions HERE].[Long Description], [Make Mods-Additions HERE].[Short Description], [Make Mods-Additions HERE].NEWwholeEachCost AS [Wholesale Each], [Make Mods-Additions HERE].Units, [Make Mods-Additions HERE].[Sale/CameoPrice] AS [Case Price], [Make Mods-Additions HERE].MinQty, [Make Mods-Additions HERE].Multiples, [Make Mods-Additions HERE].UPC, [Make Mods-Additions HERE].MSRP, [Make Mods-Additions HERE].[Availability Date], [Make Mods-Additions HERE].[Item Description (Detailed)] AS [Full Item Description]
FROM [Make Mods-Additions HERE]
WHERE ((([Make Mods-Additions HERE].Active)="YES"));
I need one more field named 'Rep Order Description' that concatenates the following:
[Short Description], "-$", [Wholesale Each], " ea, MSRP $', [MSRP]
It is important that the [Wholesale Each] and [MSRP] values are in 0.00 format (they are currency)
Example of output:
Short Description-$0.00 ea, MSRP $0.00
View 1 Replies
View Related
Mar 14, 2008
I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?
View 2 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
View 1 Replies
View Related
Nov 19, 2015
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
View 2 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
View 5 Replies
View Related
Apr 8, 2014
Table:
CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[Q] [int] NULL,
[Wk] [int] NULL
)
To generate sample data:
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)
[Code] ....
Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414
[Code] ....
Expected result:
SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144
I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result.
View 2 Replies
View Related
May 19, 2008
Greetings,
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Thanks in advance for your replies.
View 3 Replies
View Related
