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.
I am using Column Groups:
And I am getting:
Note how Today is being repeated three times. What I want is Today appearing once and centered (merged cell). Is this possible?
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI 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 RelatedHello 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?
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.
This SQL query has been driving me nuts, I am sure there is a simpleway of doing this but I am afraid it is eluding me at the moment.I have two tables:Table1X Y----------- -----------1 122 416 149 12Table2X Y----------- -----------1 102 123 125 126 32What I am after is a list of the maximum Y values for each value of Xfrom the tables:X Y----------- -----------1 122 413 125 126 329 12Any help would be greatly appreciatedThanks - Jim
View 5 Replies View RelatedI 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 RelatedHow to merge the data from one database to another if we have identity column on both the database. If we are merging two companies,we need employee table of 2 database and insert them into first database and corresponding fkey tables say some 7 tables.how to merge if the table is having identity column.
DatabaseA has 7 Tables
Namely T1,T2,....T7
DatabaseB has 7 Tables
Namely T1,T2,....T7
T1 is master
T2 is Child
DatabaseA
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
DatabaseB
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
All the tables have interrelationship as shown pkey and Fkey
All the T1...T7 have Pkey with identity column starting from 1 and corresponding Fkey column in their child tables
Database A Table information Rows
T1-10000
T2-5000
T3-5000
T4-5000
T5-5000
T6-5000
T7-5000
Database B Table information Rows
T1-20000
T2-10000
T3-10000
T4-10000
T5-10000
T6-10000
T7-10000
Now i want to merge all the data from Database B to DatabaseA
How can i merge since in DatabaseA id for T1 starts from 1,2,3,4...and so on...
DatabaseB id for T1 also starts from 1,2,3,4...and so on...
and the fkey tables also have same 1,2,3,4...and so on... with reference of parent table
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.
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.
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] .....
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]...
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
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedI have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p
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?
How can I find all the tables with a specific column name?
View 3 Replies View RelatedIs there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
hello I am trying to get a distinct on one column and 2 tables but it doesnt work
Table1
ID_Table1
Name1
Number1
Table2
ID_Table2
ID_Table1
Name2
I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'
how can I do it ?
thank you
I have a requirement where one column of a table is supposed to have two foreign key constraints i.e. it can be referring to two different columns in two tables.
View 1 Replies View RelatedHi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
If we want to get the changes happened in database we will get through sys.objects or sys.tables .but we are only getting it table name . But how to get by table name and what the column added to that table or what column has been modified (datatype or constraint) .
For example if i added employee table column called deptid it should be shown in changes in geiven date
SELECT
modify_date
,type_desc
,name
FROM sys.objects
WHERE is_ms_shipped=0
--AND modify_date>='yyyy/mm/dd' <--optionally put in your date here
ORDER BY 1 DESC
How to get column name of a table which is modified...
I have 2 tables to join.
select ID,FirstName,LastName,Gender from tableA
select ID,BabyFirstName,BabyLastName from tableB
how can I put the babyfirstname into the same row under Firstname from tableA after joining?
i tried this but the babyfirstname appear in new column.
select ID,FirstName,LastName,Gender, babyfirstname
from tableA a
join tableB b on a.id=b.id
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 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 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 *
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?
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.