Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Confused selecting Info From Two DB Expand / Collapse
Author
Message
Posted Sunday, April 7, 2013 7:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:12 AM
Points: 53, Visits: 165
i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in both DB(but there is no relationship in AgencyBackupDB between Tables).this is my question:
I want select all info from AgencyBackupDB in tables SabtHazine and Hazine and HazineGroup.i want if HazineID from Hazine table Doesnt Exist in AgencyBackupDB select it From AgencyDB and do same for HazineGroup.
this is script of All Tabel With Test Data

CREATE TABLE [dbo].[HazineGroup](
[HazineGroupID] [smallint] IDENTITY(1,1) NOT NULL,
[HazineName] [nvarchar](150) NOT NULL,
CONSTRAINT [PK_HazineGroup] PRIMARY KEY CLUSTERED
(
[HazineGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_HazineGroup] UNIQUE NONCLUSTERED
(
[HazineName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[HazineGroup] ON
INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (6, N'برق')
INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (1, N'شستشویی')
INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (2, N'نظافتی')
SET IDENTITY_INSERT [dbo].[HazineGroup] OFF
/****** Object: Table [dbo].[Hazine] Script Date: 04/07/2013 17:34:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hazine](
[HazineID] [int] IDENTITY(1,1) NOT NULL,
[HazineGroupID] [smallint] NOT NULL,
[Mablagh] [int] NOT NULL,
[HazineName] [nvarchar](250) NOT NULL,
CONSTRAINT [PK_Hazine] PRIMARY KEY CLUSTERED
(
[HazineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Hazine] ON
INSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (1, 1, 20000, N'صابون گل')
INSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (3, 2, 10000, N'بروس')
SET IDENTITY_INSERT [dbo].[Hazine] OFF
/****** Object: Table [dbo].[SabtHazine] Script Date: 04/07/2013 17:34:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SabtHazine](
[SabtHazineID] [int] IDENTITY(1,1) NOT NULL,
[HazineID] [int] NOT NULL,
[EndUserName] [nvarchar](20) NOT NULL,
[Tedad] [smallint] NOT NULL,
[sabtHazineDate] [date] NOT NULL,
[Describtion] [nvarchar](500) NULL,
CONSTRAINT [PK_SabtHazine] PRIMARY KEY CLUSTERED
(
[SabtHazineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SabtHazine] ON
INSERT [dbo].[SabtHazine] ([SabtHazineID], [HazineID], [EndUserName], [Tedad], [sabtHazineDate], [Describtion]) VALUES (6, 3, N'SA', 3, CAST(0xC2360B00 AS Date), N'
')
SET IDENTITY_INSERT [dbo].[SabtHazine] OFF
/****** Object: Default [DF_SabtHazine_Tedad] Script Date: 04/07/2013 17:34:59 ******/
ALTER TABLE [dbo].[SabtHazine] ADD CONSTRAINT [DF_SabtHazine_Tedad] DEFAULT ((1)) FOR [Tedad]
GO
/****** Object: ForeignKey [FK_Hazine_HazineGroup] Script Date: 04/07/2013 17:34:59 ******/
ALTER TABLE [dbo].[Hazine] WITH CHECK ADD CONSTRAINT [FK_Hazine_HazineGroup] FOREIGN KEY([HazineGroupID])
REFERENCES [dbo].[HazineGroup] ([HazineGroupID])
GO
ALTER TABLE [dbo].[Hazine] CHECK CONSTRAINT [FK_Hazine_HazineGroup]
GO
/****** Object: ForeignKey [FK_SabtHazine_Hazine] Script Date: 04/07/2013 17:34:59 ******/
ALTER TABLE [dbo].[SabtHazine] WITH CHECK ADD CONSTRAINT [FK_SabtHazine_Hazine] FOREIGN KEY([HazineID])
REFERENCES [dbo].[Hazine] ([HazineID])
GO
ALTER TABLE [dbo].[SabtHazine] CHECK CONSTRAINT [FK_SabtHazine_Hazine]
GO

Post #1439640
Posted Sunday, April 7, 2013 10:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
From what I understand of your question, a simple UNION ALL will get you what you need. The example answer assumes that both databases are in the same server. If not, you'll need to create a linked server.

use AgencyBackupDB
go

-- All HazineGroup rows from both databases
select HazineGroupID, HazineName from dbo.HazineGroup
union all
select HazineGroupID, HazineName from AgencyDB.dbo.HazineGroup
where HazineGroupID not in (select HazineGroupID from dbo.HazineGroup)

-- All Hazine rows from both databases
select HazineID, HazineGroupID, Mablagh, HazineName from dbo.Hazine
union all
select HazineID, HazineGroupID, Mablagh, HazineName from AgencyDB.dbo.Hazine
where HazineID not in (select HazineID from dbo.Hazine)

-- All SabtHazine rows + any SabtHazine rows in AgencyDB
select SabtHazine_ID, HazineID, EndUserNAme, Tedad, sabtHazineDate, Describtion from dbo.SabtHazine
union all
select SabtHazine_ID, HazineID, EndUserNAme, Tedad, sabtHazineDate, Describtion from AgencyDB.dbo.SabtHazine
where SabtHazineID not in (select SabtHazineID from dbo.SabtHazine)

The WHERE conditions are probably unnecessary. But just trying to interpret the requirements at face value.
Post #1439681
Posted Tuesday, April 9, 2013 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
I'm not sure I exactly understand what you want, but I think I do and the UNION ALL will work with the WHERE conditions, if you don't include the WHERE conditions then you could get duplicates with the UNION ALL if for some reason there are items that exist in both databases, even though it seems like that shouldn't happen. You could also just use UNION as that will eliminate duplicates without the WHERE conditions, but I'd think you'd want to include a column saying where the data came from so you'd still need the WHERE condition then.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1440364
Posted Saturday, May 25, 2013 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:12 AM
Points: 53, Visits: 165
i write this query but it give me this error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


select SabtHazineID, SabtHazine.HazineID, EndUserNAme, Tedad, sabtHazineDate, Describtion from dbo.SabtHazine
left JOIN dbo.Hazine ON dbo.Hazine.HazineID = dbo.SabtHazine.HazineID
union all
select Hazine.HazineName,Hazine.Mablagh from AgencyDB.dbo.Hazine
where HazineID not in (select HazineID from dbo.Hazine)

Post #1456783
Posted Saturday, May 25, 2013 11:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
vahid.arr (5/25/2013)
i write this query but it give me this error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


select SabtHazineID, SabtHazine.HazineID, EndUserNAme, Tedad, sabtHazineDate, Describtion from dbo.SabtHazine
left JOIN dbo.Hazine ON dbo.Hazine.HazineID = dbo.SabtHazine.HazineID
union all
select Hazine.HazineName,Hazine.Mablagh from AgencyDB.dbo.Hazine
where HazineID not in (select HazineID from dbo.Hazine)



The error message is telling you exactly what the problem is.

In the first query you have 6 columns. In the second query you only have 2 columns. Both queries must have the same number of columns (or literals) with matching data types when you use UNION, UNION ALL, INTERSECT or EXCEPT.

Code reformatted to make it easier to read:


select
SabtHazineID,
SabtHazine.HazineID,
EndUserNAme,
Tedad,
sabtHazineDate,
Describtion
from
dbo.SabtHazine
left JOIN dbo.Hazine
ON dbo.Hazine.HazineID = dbo.SabtHazine.HazineID
union all
select
Hazine.HazineName,
Hazine.Mablagh
from
AgencyDB.dbo.Hazine
where
HazineID not in (select HazineID from dbo.Hazine);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456799
Posted Saturday, May 25, 2013 11:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:13 PM
Points: 33,100, Visits: 15,210
To build on Lynn's point, if you don't have data for the second query, you can put in blank scalars like this:

select
SabtHazineID,
SabtHazine.HazineID,
EndUserNAme,
Tedad,
sabtHazineDate,
Describtion
from
dbo.SabtHazine
left JOIN dbo.Hazine
ON dbo.Hazine.HazineID = dbo.SabtHazine.HazineID
union all
select
Hazine.HazineName,
Hazine.Mablagh,
'',
'',
'',
'',
from
AgencyDB.dbo.Hazine
where
HazineID not in (select HazineID from dbo.Hazine);








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1456800
Posted Sunday, May 26, 2013 1:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 6, 2014 6:12 AM
Points: 53, Visits: 165
ok. i change query this way and it is work.is it optimize query??

select 
BsH.SabtHazineId,
BsH.HazineID,
BsH.EndUserNAme,
BsH.Tedad,
BsH.sabtHazineDate,
BsH.Describtion,
Coalesce( BH.HazineName, H.HazineName ) as [HazineName],
Coalesce( BH.Mablagh, H.Mablagh ) as [Mablagh]
from AgencyBackupDB.dbo.SabtHazine as BSH
left outer join AgencyBackupDB.dbo.Hazine as BH on BH.HazineID = BSH.HazineID
left outer join dbo.Hazine as H on H.HazineID = BSH.HazineID

Post #1456908
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse