April 7, 2013 at 7:12 am
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
April 7, 2013 at 10:36 pm
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.
April 9, 2013 at 8:28 am
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
Consultant - Straight Path Solutions
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
May 25, 2013 at 6:57 am
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)
May 25, 2013 at 11:18 am
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.SabtHazineleft 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);
May 25, 2013 at 11:22 am
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);
May 26, 2013 at 1:19 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply