SQL Query: Merging Tables

Jul 23, 2005

This SQL query has been driving me nuts, I am sure there is a simple
way of doing this but I am afraid it is eluding me at the moment.

I have two tables:

Table1
X Y
----------- -----------
1 12
2 41
6 14
9 12

Table2
X Y
----------- -----------
1 10
2 12
3 12
5 12
6 32

What I am after is a list of the maximum Y values for each value of X
from the tables:

X Y
----------- -----------
1 12
2 41
3 12
5 12
6 32
9 12

Any help would be greatly appreciated

Thanks - Jim

View 5 Replies


ADVERTISEMENT

Merging Two SQL Tables

Sep 14, 2006

I have two SQL tables with the following structureInstructor tableINIDINNameINEmailInstructor PhotoIPIDINIDIPPhotoNow my new Instructor table got a new fieldINIDINNameINEmailINPhotoQuestion, how can I merge the Instructor Photo table IPPHoto field into Instructor table INPhoto field? Thank you. 

View 3 Replies View Related

Merging Tables

Feb 21, 2008

Hello Everybody,

I need some help and any input would be appreciated.

I am trying to merge 2 tables without using JOINS..

Is the only way to do this by creating a new table with all the columns from both tables or is there another way to do this?

View 5 Replies View Related

Merging 2 Tables - Neither With An ID Column

Jun 15, 2007

I have 2 tables and I would like to somehow merge them and have an ID (primary key) automatically assigned while its being merged.
Ive tried exporting the tables into MS Access and doing it there but these tables have a HUGE amount of data in them and it basically kills whatever machine its ran on.

View 2 Replies View Related

Merging Two Tables Then Grouping Them

Feb 14, 2005

I have two tables, one is named Employee and the other Job_title. I'm trying to combine the two tables so I can group certain columns.

This is what I thought of so far and please correct me if I'm wrong.

SELECT Last_name FROM Employee
UNION
SELECT Job_title_code FROM Job_title
GROUP BY Exempt_non_exempt
FROM Job_title

Now this is just a theory (obviously it doesn't work) but basiclly I'm trying to have columns from two tables and have the grouped.

View 6 Replies View Related

Merging Two Tables With Selection

Aug 26, 2006

I would like to have two tables. One I call SystemPropertyTypeTable whichcontains the defaults and the other UserPropertyTypeTable. Each has 3fields. PropertyType, Description, Status.The idea here is to allow a user to change his/her defaults or to add a newProperty Type without messing with the system default list.I would like to Merge these two tables using the following logic.The SystemPropertyTypeTable any records that have "ACTIVE" for the status.The UserPropertyTypeTable all records.Group by Name and remove any duplicates.if the UserPropertyTypeTable has INACTIVE then Throw away the Active Recordfrom the SystemPropertyTypeTable and keep the INACTIVE record.Here is my code so far.SELECT T.PropertyType, T.StatusFROM [SELECT PropertyType,StatusFROM SystemPropertyTypeTable Where Status='ACTIVE'UNION ALLSELECT PropertyType,StatusFROM UserPropertyTypeTable]. AS TGROUP BY T.PropertyType, T.StatusHAVING (((Count(*))=1));here is the resultsetShowAllRecordsMerged PropertyType StatusAPARTMENT ACTIVEAPARTMENT INACTIVEBUILDING ACTIVEGARAGE ACTIVEKOISK ACTIVEMAINTENANCE SHOP ACTIVEMAINTENANCE STORAGE AREA ACTIVEOFFICE ACTIVEPARKING SPACE ACTIVEPARKING SPACE INACTIVESHOP ACTIVESTORAGE AREA ACTIVESo looking at this I would still like to remove any duplicates leaving theINACTIVE ones which would be the first APARTMENT record and the firstPARKING SPACE record. Also it would be nice to add the description back intothis as well.Any help anyone can be here would be wonderful.Thanks in advance.Bruce

View 2 Replies View Related

SQL Server 2012 :: Merging Two Large Tables (More Than 100m Rows)

Aug 18, 2014

SQL 2012

I have a source table in the staging database stg.fact and it needs to be merged into the warehouse table whs.Fact.

stg.fact is not a delta feed it is basically an intra-day refresh.

Both tables have a last updated date so its easy to see which have changed.

It will be new (insert) or changed (update) data that I am interested in, there are no deletions.

As this could be in the millions of rows that are inserts or updates then this needs to be efficient.

I expect whs.Fact to go to >150 million rows.

When I have done this before I started with T-SQL Merge statement and that was not performant once I got to this size.

My original option was to do this is SSIS with a lookup task that marks the inserts and updates and deal with them seperately. However I set up the lookup tranformation the reference data set will have a package variable in the SQL commnd. This does not seem possible with the lookup in 2012! Currently looking at Merge Join transformation and any clever basic T-SQL that could work as this will need to be fast, and thats where I think that T-SQL may be the better route.

Both tables will have >100,000,000 rows
Both tables have the last updated date
The Tables are in different databases but on the same SQL Instance
Each table holds 5 integer columns, one Varchar, one datatime

Last time I used Merge it was a wider table with lots of columns so don't know if this would be an option.

View 6 Replies View Related

SQL Server 2012 :: Merging Two Tables Based On Date Field?

Sep 8, 2015

I have two tables like,

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit

[code].....

I want result as the attached image.

Create table query for result is: CREATE TABLE dbo.#Result(
ID varchar(50),
Fee varchar(100),
Bill varchar(50),
A_Date date,
B_Date date,
Status VARCHAR(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime
)

how to achieve this in sql server 2012.

View 6 Replies View Related

Transact SQL :: Merging Two Tables Using Effectivestart And Effectiveend Date Fields

Sep 10, 2015

I have two tables. Status and Fourhistory tables.

Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.

Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.

Desired Result: I want to merge the status column into FourHistory table.

Below i have given some possible sample scenarios which i face and the third table contains the expected output.

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)

[Code] .....

View 10 Replies View Related

MSSQL Query Merging...?

May 2, 2007

I have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).


What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:

Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.

The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.

I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.

I have this code so far:
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID
from timekeeperDetails,events
where timekeeperDetails.timekeeperID = events.timekeeperID
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast
from timekeeperDetails

If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org

It would be very much appreciated.

View 3 Replies View Related

Left Join Help (Probably) - Query Merging Anyway

Dec 10, 2007

I have this database running (ignore that the ERD below was done in Access, this is being made in Microsoft SQL Server 2005).


What I need to do is if you look at the users table and the orderContents table I need to make a query that:

Collects the users' names and any products they have bought (preferably shown by title not ID) as well as still showing the users that have not ordered any products.

I get the feeling there's a left join involved, but can't quite see how to do it.


Thank you in advanced for any help.

View 3 Replies View Related

Complex Merging Query In Sql Server 2000

Oct 6, 2006

Hello,I have a Database in a SQL Server 2000 where I have different userstables with equal fields like this:id (int)email (varchar)name (varchar)address (varchar)joinedon (datetime)I want to merge all the tables in one taking rows with the same emailbut taking always the more recent fields based on the 'joinedon' field.So if I have this four different rows:Tbl email name address joinedon--------------------------------------------------------------------------------------------T1 Join Bytes! johnathan NULL 01/01/95T2 Join Bytes! NULL barcelona street 01/01/98T3 Join Bytes! john valencia street 01/01/97T4 Join Bytes! john Q NULL 01/01/99And the final row entered in the new table would beTbl email name address joinedon----------------------------------------------------------------------------------------new Join Bytes! john Q barcelona street 01/01/99I am trying doing his with union statements, but i am not getting thereal merging of data. Any clue?Thanks for your help.

View 1 Replies View Related

Simple Merging A Few Queries In One Query Question

Jan 25, 2008

how can I fill a data set with a multi parameter filter:

I want to create in my form few textboxes and create a query that filters the dataset to show only the specific data.
but the problem is if the user doesn't supply any words in some textbox the dataset is not filled.

it would be a good solution for me if I can either:
for example let's say my query is SELECT * FROM table WHERE column = @parameter,
i want to write before the query something like "if @parameter="" then replace the query syntax near WHERE with * so it looks SELECT * FROM table WHERE column = *.
or:
if there is some character or character series that sql reconizes in a parameter as an asterisk.

thank you for your time!

View 37 Replies View Related

Query Merging/ Query Transformation ???

Sep 22, 2004

Hello,

Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.

Plz help.
Shigs.
=============================
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
=============================

View 7 Replies View Related

SQL Server 2012 :: Merging Two Tables Using Effective Start And Effective End Date Fields

Sep 10, 2015

I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.

Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.

Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit

[code]...

View 4 Replies View Related

Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)

Nov 15, 2006

Hi!

I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.

I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.

I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)

Any pointer most welcome!

best regards and thanks

Thibaut

View 1 Replies View Related

Query View Vs Query Tables Directly

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

Merging Db

Apr 19, 2007

hello
im working on a project with a friend and store source files in a subversion server. since we are both working on the same DB everytime one of us makes a change we discover a problem in merging our changes.

is there any tool that can help in merging and making diff??
is there any alternativa?

View 1 Replies View Related

Merging Database Log

Feb 3, 2008

 I wonder about the possibility of merging two identical databases on two different servers upon recovering from connection failure between them, using triggers. In order to create a simple synchronization

View 1 Replies View Related

Please Help In Merging Database.

Apr 8, 2008

I have a website wih about 50000 pageviews permonth. I am using multiple access database for each section. for example for photogallery there is a separate database, for jokes there is another one. Now I am thinking to convert my all access databases into MSSQL Server2005 databases.There are about 5 access databases  I want to merge them and convert them into 1 MDF file.How to do this?I am very new to SQL Server. Please Help I update access databases in MS ACCESS and upload them in server.When I will use SQL Server Databases How will I update them? From VS2008? Any other method? Or Should I think about creating WebBased control panel for my website like CP present in every CMS(joomla, dotnetnuke etc)? ....................................................................................................................... I have VS2008 and SQL Server 2005 Express. 

View 6 Replies View Related

Table Merging

Oct 18, 2004

I have two tables each with a date field. I have to combine these two tables and sort on the date. I want the date from each table to be on the same column though, not in seperate columns.


Ex:
Table A

ID
Log_Time
ETI

Table B
ID
Log_Time
Action
Description


Thanks for the help

Rock *

View 3 Replies View Related

Merging 2 Different SQL Server DB Into One

May 15, 2006

I have two sql server databases...one holds nothing but personalisation information. The other holds various other types of data. I would like to merge these two DBs into one (including all stored procedures,etc.), but being a relative noob to SQL Server, I can't seem to figure out exactly how to accomplish this. Neither database holds anything that the other holds; data-wise, stored procedure-wise or any other wise. ;-)
Can anyone point me in the right direction?

View 1 Replies View Related

REPLICATION MERGING

Aug 20, 2002

Hi, I want to make fusion replication.

We've got an incident database on our server and we want our
consultants could add, modify, delete incidents on our customers sites
with their laptop.
When they come back we can also synchronise our incidents database
with their modifications.

I defined the database on our server ( sql7 sp3) as distibutor and
publisher, the other computers coulb be only suscribers.

It would be very easy except for a case :

when a line is modified on our server database and the same line in
the same table is modified on the suscriber computer, the changes on
publisher (distiributor) seems to have priority on the suscribers.
But we would like to control this : we want the suscribers, when they
come back, if this case appears, could choose good merging or not if
it's possible, and if it is not, we would like at less they could have
a message saying they will lost that they change (incident by incident
if it's possible)

So what is possible to resolve it by the best way ?

Thanks to your answers

Best regards

Axel

View 1 Replies View Related

Merging Fields

Sep 4, 2001

Help!!, Does anyone know of a way that I can merge three seperate fields into one field in my sql statement.
EG.
SELECT catnumber, catnumber2,catnumber3
FROM Categories
WHERE Customer = 'xxxx' and itemnumber = 1 or 2 or 4

I want the catnumber, catnumber2, catnumber3 all in one field so I can sort by it.
If anybody could help me it would be greatly appreciated.
Thanks
Mike

View 1 Replies View Related

Merging On Export

Apr 2, 2008

Hi,

I need to figure out what is the easiest way to do this is. I have two tables that have the following columns:


Code:

TABLE 1
AcctNbr, GiftRef, Gdate, PayCode, Amt

Table 2
GiftRef, Amt, MotvCode, FundId


I need to merge the two tables with GiftRef being the common table between them. I have exported the tables from MSSQL to Access and am wondering what the easiest way to do this is. Is there an SQL Query that can merge the two tables on export by GiftRef? Is it easier to do this in Access now that I've exported the two Tables? Any help on this would be awesome. Thanks.

View 5 Replies View Related

Importing And Merging Csv Using Sql

Sep 20, 2004

Hi Gurus.

My client drops for me many files like this on a shared drive M: daily
1_Test.csv
2_Test.csv
3_Test.csv

I would like you to advice on how to write a SQL code (that can include DTS if possible) that will take this files, merge them into one (since they have same columns) and send them to another client as one file. But it must only take the files of the same date and must not resend files i have send already. I need this to be an automated process since the files are dumped into M:drive midnite and I need this code so that I can schedule it as a job and run around 4h00am.

View 3 Replies View Related

Merging Databases

Aug 17, 2006

I'm pretty new to SQL altogether. I kinda gave myself a crash-course when the software I was using, Papertrack, was having problems. It all started when I tried to move the database. Now there are two separate databases that I can see on SQL Enterprise manager. I picked up the book and figured some things out, but still don't know how to merge the databases. I called the support for the software and got to know them on a middle-name basis. Is there a way I can merge the two databases so that the software can pull queries from both and access the files?
To anyone that likes a challenge,

Thanks

View 6 Replies View Related

Merging Two Mssql Db

Feb 5, 2004

I have a problem that I need help on.

Right now, I have two MSSQL server and I am trying to merge them into one.

The problem is, I do not know the login Id and password for some of the users.

I know the login information are stored under syslogins at the master db.

How do I get the information out and append to the syslogins table of the second mssql db?

Any help would be appreciated.

---alucarrd

View 3 Replies View Related

Merging Data

Jun 3, 2008

I have 2 tables that have the exact same structure and have some common values.
T1 = a,b,c,d,e
T2 = e,f,g,h

I want to create a single distinct table which captures all unique in both tables. I have a common fields ID in both, I can match on. I don't want duplicates which is what's happening now..

CombinedT = a,b,c,d,e,f,g,h

Thanks.

View 3 Replies View Related

Help Merging Records?

Oct 15, 2005

Hello there. I am Completely new to SQL and this forum, and this problem that I have may appear to be very basic to you guys but still... I was wondering if I could get some help with a database I am trying to make in MS Access.

I have used the Access TransferText function to import data from a text file into a table with an ID attached to each line, eg.

ID Text
1 Hello world
2 This is an example
3 Of my database

I want to merge the data, or copy it into a field in a new table to get:

ID Text
1 Hello World
This is an example
Of my database
2 [more imported text from a different table]

and i have been advised that SQL is the best way to do this. Is it possible to have line breaks in a field within microsoft access, or would it have to be structured as

ID Text
1 Hello World This is an Example Of My Database
2 ...

And how would i make the SQL to do this?

Thanks,

Thom

View 2 Replies View Related

Merging Rows

Jun 28, 2006

Say I have a table with the columns (and example data):

CustomerNo, ContactNo, ActivityNo
null, null, 1
100, null, 1
null, 666, 1
null, null, 2
200, null, 2
null, 777, 2

From this I would like to get the result:

CustomerNo, ContactNo, ActivityNo
100, 666, 1
200, 777, 2

How do I solve this. Im getting grey hair here...

View 5 Replies View Related

Merging Data

Jan 12, 2007

I have a function that returns a range of numbers and a query that returns a varchar field and an int field. I would like to replace the int field in the query with unique ranged numbers from the function(one per row).

Any ideas?

Thanks,
Mark

-- use ifms

select *
from dbo.fnRange(5101,5152)


select --distinct s.OrgKeyId, 10420 TableNumber, 5100 DetailCodeNumber, s.NewStratalabel CodeDescription, s.NewStratalabel CodeAbbreviation, 1 CodeActiveFlag, Getdate(), 0
distinct s.NewStratalabel, 5100
from _StrataLabel_xref s
Left Outer Join(select * from org_Detail_Code where tablenumber = 10420 and orgkeyid in (Select distinct OrgkeyId from _StrataLabel_xref)) o
on o.OrgKeyId = s.OrgKeyId and o.CodeDescription = s.NewStratalabel
where o.Detailcodenumber is null
order by s.NewStratalabel


5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
5119
5120
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152

H3Bt5100
H3Cr5100
H4Bt5100
HS1Ar5100
HS1Cr5100
HS2Air5100
HS2Ar5100
HS2Br5100
HS2Cr5100
HS3Ai5100
HS3Bi5100
HS3Bit5100
HS3Cr5100
HS4Am5100
HS4Bi5100
HS4Bm5100
HS4Bt5100
HS4Ct5100
HS4Dr5100
NFrd5100
S2Ap5100
S2Ar5100
S2Ax5100
S2Bp5100
S2Br5100
S2Bw5100
S2Bx5100
S3Ap5100
S3Aw5100
S3Ax5100
S3Bw5100
S3Bx5100
SH1Api5100
SH1Br5100
SH1Cr5100
SH2Air5100
SH2Ap5100
SH2Ar5100
SH2Ax5100
SH2Bir5100
SH2Bix5100
SH2Bp5100
SH2Br5100
SH2Bw5100
SH3Ai5100
SH3Ap5100
SH3Api5100
SH3Bw5100
SH3Cr5100
SH3Di5100
SH4Bm5100
SH4Bwi5100

View 3 Replies View Related

Merging Two Database

Sep 10, 2007

Hi,
We have we had a database in 2006 with around 90 tables and later we created a new database in 2007 with more tables for same application. Now we want to have a database which will contain all records from the two above database. how should we go about this??

Bharath JrDBA

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved