SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Confused selecting Info From Two DB


Confused selecting Info From Two DB

Author
Message
vahid.arr
vahid.arr
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 180
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


Mansfield
Mansfield
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 394
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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18380 Visits: 14893
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
vahid.arr
vahid.arr
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 180
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)


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39089 Visits: 38518
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);




Cool
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)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62020 Visits: 19101
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
My Blog: www.voiceofthedba.com
vahid.arr
vahid.arr
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 180
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search