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 *
Hi,
Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.
Is there better way to merge master table using staging table?
Thanks in advance.
DF.
thanks for reading.
kinda like like a UNION for columns maybe. i think that's what i'm looking for. take a look if you have a minute....
3 queries, each pretty clear (count the occurrences of a value in a column --> where someFlag='Y')...
SELECT Count(flag_A) as 'flag_A_confirmed', Year
FROM cases
WHERE flag_A = 'Y'
GROUP BY Year
SELECT Count(flag_B) as 'flag_B_confirmed', Year
FROM cases
WHERE flag_B = 'Y'
GROUP BY Year
SELECT Count(flag_C) as 'flag_C_confirmed', Year
FROM cases
WHERE flag_C = 'Y'
GROUP BY Year
but my desired output merges the columns together in one table ....
total confirmed flags for that year by type...
Yearflag_Aflag_Bflag_C
1998121533
1999161325
2000191421
2001... etc
i'm pretty sure i can do this using temp tables. any other ideas?
thanks for your input in advance. again.
I need to populate a table from several sources of raw data. For agiven security (stock) it is possible to only receive PARTS ofinformation from each of the different sources. It is also possibleto have conflicting data.I am looking to make a composite picture of a given security using thefollowing rules:1) The goal is to replace all NULL and Blank values with data2) Order of precedence (from highest to lowest) is Non-NULL Non-Blank--> Blank --> NULL3) In the case of Non-NULL Non-Blank values that conflict (aredifferent) leave existing value (even if NULL or Blank)For example:Given the following rows:Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 NULL NULL NULL NULLWSM 969904101 CUSIP XNYS Stock NULL WILLIAMSSONOMAWSM 969904101 NULL XNYS Stock NULLWILLIAMS-SONOMAWSM CUSIP XNYS Stock Common NULLWSM NULL CUSIP XASE Stock NULL WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONALI am looking for the following results ('*' indicates changed value)Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 *CUSIP *XNYS *Stock NULL *TV AZTECAWSM 969904101 CUSIP XNYS Stock *Common WILLIAMSSONOMAWSM 969904101 *CUSIP XNYS Stock *CommonWILLIAMS-SONOMAWSM *969904101 CUSIP NULL Stock Common NULLWSM *969904101 CUSIP XASE Stock *Common WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONAL
View 6 Replies View RelatedHi
I have a problem out here. I have two databases with the same table structure
and I want to merge data from both the tables . Please can anyone let me know.
Iam using Sql Server 7.0.
Thanks
Bye
I don't know the correct syntax to accomplish this. I've looked in the on-line help but am not coming up with anything.
Basically I have two identically structured tables in two separate databases.
I wanted to select anything from the dbo2.table that isn't in the dbo1.table and insert it into dbo1.table.
I thought I could do something like... quote:
Select INTO dbo1.table
and then add my select statement. But that throws an error:
quote:Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'bkupproceduresfeeschedule' in the database.
And the on-line documentation says an INTO is only for a new table.
How can I merge records from one table into another table?
I'm trying to avoid a large amount of manual data manipulation.
Here's the background: Legacy system that has (well let's call apples apples) pretty much no method of enforcing data integrity, which has caused a fairly decent amount of garbage data to be inserted in some tables. Pulling one of the [Individuals] table from within this Legacy system and inserting it into a production system, into the Table schema currently in place to track [Individuals] in this Production system.
Problem: Inserting the information is easy, how to deduplicate the records that exist within the staging table that the legacy [Individuals] table has been dumped into in production, prior to insertion. (Wanting to do this programmatically with SQL or SSIS preferably, so that I can alter it later to allow for updating existing/inserting new)
Staging Table Schema:
;
CREATE TABLE [dbo].[stage_Individuals](
[SysID] [int] NULL, --Unique, though it's not an index intended to identify the [Individuals]
[JJISID] [nvarchar](10) NULL,
[NameLast] [nvarchar](30) NULL,
[NameFirst] [nvarchar](30) NULL,
[NameMiddle] [nvarchar](30) NULL,
[code]....
Scenario: There are records that duplicate the JJISID, though this value is supposed to be unique for every individual. The SYSID is just a Clustered Index (I'm assuming) within the Legacy system and will be most likely dropped when inserted into the Production [Inviduals] table. There are records that are missing their JJISID, though this isn't supposed to happen either, but have valid information within SSN/DOB/Name/etc that can be merged into the correct record that has a JJISID assigned. There is really no data conformity, some records have NULLS for everything except JJISID, or some records will have all the [Individuals] information excluding the JJISID.
Currently I am running the following SQL just to get a list of the records that have a duplicate JJISID (I have other's that partition by Name/DOB/etc and will adapt whatever I come up with to be used for those as well):
;
select j.*
from (select ROW_NUMBER() OVER (PARTITION BY JJISID ORDER BY JJISID) as RowNum, stage_Individuals.*, COUNT(*) OVER (partition by jjisid) as cnt from stage_Individuals) as j
where cnt > 1 and j.JJISID is not nullNow, with SQL Server 2012 or later I could use LAG and LEAD w/ the RowNum value to do my data manipulation...but that won't work because we are on SQL Server 2008 in this environment.
[URL]
With, the following as a potential solution:
GSquared (3/16/2010)Here's a query that seems to do what you need. Try it, let me know if it works.
Performance on it will be a problem, but I can't fine tune that. You'll need to look at various method for getting this kind of data from the table and work out which variation will be best for your data. Without access to the actual table, I can't do that.
;
WITH CTE
AS (SELECT master_id,
MIN(ID) AS first_id,
MAX(Account_Expiry) AS latest_expiry
FROM #People
GROUP BY master_id)
SELECT P1.master_id,
[code].....
Unfortunately, I don't think that will accomplish what I'm looking for - I have some records that are duplicated 6 times, and I'm wanting to keep the values within these that aren't NULL.
Basically what I'm looking for, is to update any column with a NULL value to the corresponding Duplicate [Individuals] record value for that column.
**EDIT - Example, Record 1 has a JJISID with NULL NameFirst & NameLast BUT Record 2 has the same JJISID and values for NameFirst & NameLast. I'm wanting to propogate the NameFirst & NameLast from Record2 into Record1
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?
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 RelatedI 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 RelatedI 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 RelatedI 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?
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
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
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.
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.
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
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
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.
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
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...
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
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
Hi All,
I have a query that I'm working on, but instead of giving the query, I wanted to ask a basic syntax question. If more info is needed, let me know. If you have 2 rows that have a common relationship, but differing information in some fields, can you merge them all onto one row? I've done this with Sum(case) expressions, but I don't want to 'add' anything. In the following example, the ActivityID refers to a break. ActivityID can be:
0=Pick up
1=Drop Off
2=Lunch
3=Break
So if I wanted to see 2 breaks on 1 row in the following example, would this be possible:
Veh ActID ArrTime DepTime
1 3 7:00 8:00
1 3 10:00 11:00
Veh ActID ArrTime DepTime ArrTime DepTime
1 3 7:00 8:00 10:00 11:00
Thanks in advance for your help!
Craig
Hello everyone!
Maybe you can help me out:
I have 2 tables (A and B), Table A has 2 fields t1 and t2...each of them has a number that is related to the primary key of Table B.
What i want is to make a query that presents:
t1,t2, B.descriptionof_t1, B.description_t2
Anyone that can help me?
Thanks in advance...
Hello,
I have a question. I want to perform the following task:
1) UPDATE wdoctyp SET ldoclock=0 WHERE docType='LEAVE'
2) SELECT idoclastsn FROM wdoctyp WHERE docType='LEAVE'
3) UPDATE wdoctyp SET ldoclock=1, idoclastsn=idoclastsn+1 WHERE
docType='LEAVE'
can I perform it in one SQL statement? Actually the reason I want to do this is because I want to prevent ldoclock from locking forever due to computer failure.
Can somebody give me a hand?
Best regards,
Tee Song Yann
i have the following xml file :
<transport>
<train> 10
</train>
<plane>10
</plane>
<metro>10</metro>
<bm>10</bm>
<voiture><avion> 10 </avion></voiture> <voiture><train>10</train></voiture> <voiture><mercedesC230> 10 </mercedesC230></voiture>
<nissan>
<Z>10 </Z>
</nissan>
</transport>
**what i wanna do is to get the following result :
<transport>
<train> 10
</train>
<plane>10
</plane>
<metro>10</metro>
<bm>10</bm>
<voiture><avion> 10 </avion><train>10</train><mercedesC230> 10 </mercedesC230></voiture>
<nissan>
<Z>10 </Z>
</nissan>
</transport>
---------------------------------------------------------------------------------------------------------------------------------------------------------------
this means that i want to merge all the child nodes that belong to the same parent node.
thanks for any help
Hi all,
I'm facing the following problem:
TextData ObjectID SPID StartTime EndTime
------------------------------------------------------------------------------------------------------------
Select 1 111111111 52 2008-03-06 11:19:51.250 NULL
Select 1 111111111 52 NULL 2008-03-06 11:19:51.250
I want to achive this result by either an update statement or a select query:
TextData ObjectID SPID StartTime EndTime
------------------------------------------------------------------------------------------------------------
Select 1 111111111 52 2008-03-06 11:19:51.250 2008-03-06 11:19:51.250
Is this possible? There is no primary key
Thanks!
Rgds,
Worf
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?
Hi,
I Have 3 columns in source (ID1,ID2,ID3) ..i want make a lookup of 'Dnumber' column in the target table with each of the 3 columns.Has to map the unmatched ones(ID's) in the 'Dnumber'
Iam fine till getting the unmatched ID's from each of the column from source ..but getting problem in mapping these 3 columns in to 'Dnumber'
Ex: Lets say got the unmatched are like this ,
ID1 ID2 ID3
1 4 6
2 5 7
3 8
Output should be :
Dnumber
1
2
3
4
5
6
7
8
I Can't use UnionAll..bcoz 3 columns should map to the same target column.Not sure about Merge Join
Thanks
Niru
it doesn't appear possible to merge cells by col in RS 2005 ? am i missing some more advanced feature or is it a great big over sight on MS's part?
i basicly want a 3 column table, with the rows in the first column merged and the text turned on it's side showing bottom to top.
pretty ordinary kind of thing to do i would have thought
Yesterday I unintentionally removed some records from a database. I need to rebuild the database to the point just before I ran the DELETE query. My restore is 12 hours before that point. I'm pretty sure that's what the transaction log is for, right? Is there a tutorial somewhere on how to do this? Or maybe software I can buy to make it easy?
Thanks!
Hi Guys,
I need to merge database's from 2 different locations into a master database. I am concerned with the identities being duplicated, and the need to map/translate the ids in a sequential order. Also this needs to happen on a nightly basis , would i need to write a custom script ?? any starters , pointers would be appreciated
thanks
tribal