August 14, 2013 at 10:06 pm
karim.boulahfa (8/13/2013)
I have just make a new database and im trying to put 4 tables in 1 table. I heard you most use the INTO syntax...So i was doing firts this:
SELECT *
INTO device
FROM WhatsUp_Assyst_replicatie2.dbo.Device;
So the rows are in the table (Device) but when im trying to put the other table form other datebase in the same table it gives a error;
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'device' in the database
how to fix that and make sure that the tables comes all in 1 table.. :S
tnx
Based on what Eric just posted, I have to ask... do you actually need the data in a single table or do you just need to be able to select from the 4 tables as if they were 1?
Also, do the identity columns have overlapping ranges of numbers between the 4 tables?
--Jeff Moden
August 14, 2013 at 10:20 pm
Eric M Russell (8/14/2013)
Rather than inserting into one table, another option would be to create a virtual table using a partitioned view.
create view v_WhatsUp_Assystas
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie2.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie3.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie4.dbo.device
GO
Just to be technically accurate, just creating a view using UNION ALL doesn't make a partitioned view and won't give you the advantages that a partitioned view can have. You have to have a partitioning column with a CHECK constraint on it to isolate it to a range in the table for it to actually be a partitioned view.
There are also disadvantages to partitioned views. For example, while they can be updateable, they require that all columns must be inserted even if you need to insert a NULL. That also means that you can't have an IDENTITY column in the tables because you can't insert into an IDENTITY column unless you SET IDENTITY INSERT ON... which you can't do in a view. You could, however, insert into the underlying tables if there's an IDENTITY column and still use the partitioned view for SELECTs to get "table elimination" during certain SELECTs (which help performance).
A non-partitioned UNION ALL view as you have can still be updateable but it won't do "table elimination" like you can do with a partitioned view. It's certainly a trade off.
--Jeff Moden
August 15, 2013 at 1:05 am
The problem what i have is that i can use these qeurys but it overlapping when i make this qeury a job to update my table everytime..
Like when i do the qeury again it gives me 2 columns with the same values that not what I want....
This table will get the tables from a replication table:
First: Put the 4 tables in 1 table
Second: Update the table every time with the 4 tables (like a job or something)
...
I think mabye I do something wrong with the qeury that he overlapping or copy the valuaes and i see 2 time or more times the same qeury...
August 15, 2013 at 1:18 am
Eric M Russell (8/14/2013)
Rather than inserting into one table, another option would be to create a virtual table using a partitioned view.
create view v_WhatsUp_Assystas
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie2.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie3.dbo.device
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID FROM WhatsUp_Assyst_replicatie4.dbo.device
GO
When i use this and then (i see its on the views) but is it useable (because i need the values in a normal table)
August 15, 2013 at 1:53 am
First, take a step back and clearly think about your requirements and purpose.
- Like Jeff mentioned: do you really need a duplicate of all the data in the four tables, or do you only need to access the data from the four tables at once in a combined way?
- What are the business rules to handle any duplicate values across the four tables? Should you ignore duplicate values, should you combine the values, should you ...
- What are the business rules to handle the duplicate Identity values across the four tables? Should you ignore duplicate values, should you assign new identity values, do you need the identity values, should you ...
- If you really need a duplicate of the data:
== do you need to apply all changes in the underlaying four tables to the combined table as well?
== do you need to keep track of historical data in the combined table?
The option Eric mentioned is most probably the easiest and best solution. Just create a view with a UNION ALL of the SELECT of all four tables. There will be no need to update the values in the view if the values in one of the four originating tables are changing. The data in view will always be up-to-date. You will also have no error for trying to insert a duplicate identity, because you don't insert anything (just selecting data).
You can use this view in any query you like. It is just not possible to update the values through this view. Updates must be done directly in the underlaying tables.
August 15, 2013 at 3:51 am
HanShi (8/15/2013)
First, take a step back and clearly think about your requirements and purpose.- Like Jeff mentioned: do you really need a duplicate of all the data in the four tables, or do you only need to access the data from the four tables at once in a combined way?
- What are the business rules to handle any duplicate values across the four tables? Should you ignore duplicate values, should you combine the values, should you ...
- What are the business rules to handle the duplicate Identity values across the four tables? Should you ignore duplicate values, should you assign new identity values, do you need the identity values, should you ...
- If you really need a duplicate of the data:
== do you need to apply all changes in the underlaying four tables to the combined table as well?
== do you need to keep track of historical data in the combined table?
The option Eric mentioned is most probably the easiest and best solution. Just create a view with a UNION ALL of the SELECT of all four tables. There will be no need to update the values in the view if the values in one of the four originating tables are changing. The data in view will always be up-to-date. You will also have no error for trying to insert a duplicate identity, because you don't insert anything (just selecting data).
You can use this view in any query you like. It is just not possible to update the values through this view. Updates must be done directly in the underlaying tables.
You right and its the easy and best choice to do that. I just tweak the qeury and i have the following qeury.
create view v_WhatsUp_Assyst
as
SELECT nDeviceID, nDefaultNetworkInterfaceID, item_id, item_keya, nDeviceGroupID, sGroupName, nDefaultNetworkInterfaceID, sNetworkName FROM WhatsUp_Assyst_replicatie.dbo.device, WhatsUp_Assyst_replicatie.dbo.item, WhatsUp_Assyst_replicatie.dbo.DeviceGroup, WhatsUp_Assyst_replicatie.dbo.NetworkInterface
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID, item_id, item_keya, nDeviceGroupID, sGroupName, nDefaultNetworkInterfaceID, sNetworkName FROM WhatsUp_Assyst_replicatie2.dbo.device, WhatsUp_Assyst_replicatie2.dbo.item, WhatsUp_Assyst_replicatie2.dbo.DeviceGroup, WhatsUp_Assyst_replicatie2.dbo.NetworkInterface
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID, item_id, item_keya, nDeviceGroupID, sGroupName, nDefaultNetworkInterfaceID, sNetworkName FROM WhatsUp_Assyst_replicatie3.dbo.device, WhatsUp_Assyst_replicatie3.dbo.item, WhatsUp_Assyst_replicatie3.dbo.DeviceGroup, WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
union all
SELECT nDeviceID, nDefaultNetworkInterfaceID, item_id, item_keya, nDeviceGroupID, sGroupName, nDefaultNetworkInterfaceID, sNetworkName FROM WhatsUp_Assyst_replicatie44.dbo.device, WhatsUp_Assyst_replicatie44.dbo.item, WhatsUp_Assyst_replicatie44.dbo.DeviceGroup, WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
GO
But get the following error;
Msg 451, Level 16, State 1, Procedure v_WhatsUp_Assyst, Line 3
Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 4.
Msg 451, Level 16, State 1, Procedure v_WhatsUp_Assyst, Line 3
Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 6.
Msg 451, Level 16, State 1, Procedure v_WhatsUp_Assyst, Line 3
Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 8.
..
I see this before and I was always using a this syntax:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
COLLATE DATABASE_DEFAULT
but now I cant do that.. because he will say that the create function must first beginning.
August 15, 2013 at 4:02 am
You have collation differences between your four databases. You need to put a [font="Courier New"]COLLATE collation_name[/font] clause after every column specification that does not have the collation you want to use.
August 15, 2013 at 4:52 am
John Mitchell-245523 (8/15/2013)
You have collation differences between your four databases. You need to put a [font="Courier New"]COLLATE collation_name[/font] clause after every column specification that does not have the collation you want to use.
August 15, 2013 at 4:56 am
CREATE VIEW v_WhatsUp_Assyst
AS
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie.dbo.device
, WhatsUp_Assyst_replicatie.dbo.item
, WhatsUp_Assyst_replicatie.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie2.dbo.device
, WhatsUp_Assyst_replicatie2.dbo.item
, WhatsUp_Assyst_replicatie2.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie2.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie3.dbo.device
, WhatsUp_Assyst_replicatie3.dbo.item
, WhatsUp_Assyst_replicatie3.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie44.dbo.device
, WhatsUp_Assyst_replicatie44.dbo.item
, WhatsUp_Assyst_replicatie44.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
GO
August 15, 2013 at 5:10 am
HanShi (8/15/2013)
CREATE VIEW v_WhatsUp_AssystAS
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie.dbo.device
, WhatsUp_Assyst_replicatie.dbo.item
, WhatsUp_Assyst_replicatie.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie2.dbo.device
, WhatsUp_Assyst_replicatie2.dbo.item
, WhatsUp_Assyst_replicatie2.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie2.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie3.dbo.device
, WhatsUp_Assyst_replicatie3.dbo.item
, WhatsUp_Assyst_replicatie3.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, item_id COLLATE Latin1_General_CI_AS
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID COLLATE Latin1_General_CI_AS
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID COLLATE Latin1_General_CI_AS
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie44.dbo.device
, WhatsUp_Assyst_replicatie44.dbo.item
, WhatsUp_Assyst_replicatie44.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
GO
getting error about a INT!!
August 15, 2013 at 5:17 am
Sorry, I was too much copying...
Remove the COLLATE commands from all ID fields. Only leave the COLLATE commands on the VARCHAR fields.
August 15, 2013 at 5:31 am
HanShi (8/15/2013)
Sorry, I was too much copying...Remove the COLLATE commands from all ID fields. Only leave the COLLATE commands on the VARCHAR fields.
August 15, 2013 at 5:36 am
Just a heads up, Karmin... posting code as a graphic does absolutely nothing for anyone. We can't copy it and modify it for you.
It would also REALLY be a big help readability-wise if you'd post your code between the two SQL code tags. When you're building a message, select the code="sql" tags (just double click on them) in the IFCode Shortcuts that will appear to the left of the window that you build your message in and then paste your code between the two tags that appear the message window.
--Jeff Moden
August 15, 2013 at 5:37 am
Does this statement work?
CREATE VIEW v_WhatsUp_Assyst
AS
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS as 'item_keya'
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS as 'sGroupName'
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS as 'sNetworkName'
FROM WhatsUp_Assyst_replicatie.dbo.device
, WhatsUp_Assyst_replicatie.dbo.item
, WhatsUp_Assyst_replicatie.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie2.dbo.device
, WhatsUp_Assyst_replicatie2.dbo.item
, WhatsUp_Assyst_replicatie2.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie2.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie3.dbo.device
, WhatsUp_Assyst_replicatie3.dbo.item
, WhatsUp_Assyst_replicatie3.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie44.dbo.device
, WhatsUp_Assyst_replicatie44.dbo.item
, WhatsUp_Assyst_replicatie44.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
GO
August 15, 2013 at 5:40 am
HanShi (8/15/2013)
Does this statement work?
CREATE VIEW v_WhatsUp_AssystAS
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie.dbo.device
, WhatsUp_Assyst_replicatie.dbo.item
, WhatsUp_Assyst_replicatie.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie2.dbo.device
, WhatsUp_Assyst_replicatie2.dbo.item
, WhatsUp_Assyst_replicatie2.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie2.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie3.dbo.device
, WhatsUp_Assyst_replicatie3.dbo.item
, WhatsUp_Assyst_replicatie3.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie3.dbo.NetworkInterface
UNION ALL
SELECT nDeviceID
, nDefaultNetworkInterfaceID
, item_id
, item_keya COLLATE Latin1_General_CI_AS
, nDeviceGroupID
, sGroupName COLLATE Latin1_General_CI_AS
, nDefaultNetworkInterfaceID
, sNetworkName COLLATE Latin1_General_CI_AS
FROM WhatsUp_Assyst_replicatie44.dbo.device
, WhatsUp_Assyst_replicatie44.dbo.item
, WhatsUp_Assyst_replicatie44.dbo.DeviceGroup
, WhatsUp_Assyst_replicatie44.dbo.NetworkInterface
GO
I would hope not. There's no join criteria in the FROM clauses causing HUGE 4 way Cartesian Products.
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply