August 13, 2013 at 4:50 am
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
August 13, 2013 at 5:06 am
INSERT INTO device
SELECT *
FROM WhatsUp_Assyst_replicatie2.dbo.Device;
John
August 13, 2013 at 5:07 am
You can only use that syntax once. The first time it will create the table.
The second time it already exists, so you will get an error.
For the 2nd till 4th table, you have to use the INSERT INTO ... SELECT syntax.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 5:14 am
I get the following error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'WhatsUp_Assyst_replicatie_All.dbo.device' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I just the following syntax
INSERT INTO WhatsUp_Assyst_replicatie_All.dbo.device
SELECT *
FROM WhatsUp_Assyst_replicatie2.dbo.Device;
I have following databases;
WhatsUp_Assyst_replicatie (Finish)
WhatsUp_Assyst_replicatie2
WhatsUp_Assyst_replicatie3
WhatsUp_Assyst_replicatie4
August 13, 2013 at 5:16 am
In that case, you either have to SET IDENTITY INSERT ON, or omit the identity column from your SELECT list.
John
August 13, 2013 at 5:20 am
What do you mean can you do it with a example?!
August 13, 2013 at 5:22 am
I can do better than that. If you post the DDL for your two tables, I'll give you the exact script.
John
August 13, 2013 at 5:29 am
How can i get the DDL file? Im not so favorite of shortcuts. Step by Step
August 13, 2013 at 5:33 am
Right-click on the table, choose Script Table as -> CREATE To -> New Query Editor window.
John
August 13, 2013 at 5:38 am
I want this databases with the table device;
WhatsUp_Assyst_replicatie
WhatsUp_Assyst_replicatie2
WhatsUp_Assyst_replicatie3
WhatsUp_Assyst_replicatie4
in the database;
WhatsUp_Assyst_replicatie_All
:::HERE JOHN:::
USE [WhatsUp_Assyst_replicatie_All]
GO
/****** Object: Table [dbo].[device] Script Date: 13-8-2013 13:37:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[device](
[nDeviceID] [int] IDENTITY(1,1) NOT NULL,
[nDefaultNetworkInterfaceID] [int] NOT NULL
) ON [PRIMARY]
GO
August 13, 2013 at 5:45 am
Either:
SET IDENTITY INSERT ON
INSERT INTO WhatsUp_Assyst_replicatie_All.dbo.device
(nDeviceID, nDefaultNetworkInterfaceID)
SELECT nDeviceID, nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie.dbo.device
SET IDENTITY INSERT OFF
or:
INSERT INTO WhatsUp_Assyst_replicatie_All.dbo.device
(nDefaultNetworkInterfaceID)
SELECT nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie.dbo.device
I would recommend the second method, since you are likely to get duplicates in the identity column with the first, which you probably don't want.
John
August 13, 2013 at 6:57 am
When i start with a new database and begin with the first database to put into the device table it works...
When i going to databse 2 its saying this again;
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'device' when IDENTITY_INSERT is set to OFF.
when i going to use the syntax;
SET IDENTITY INSERT ON
INSERT INTO WhatsUp_Assyst_replicatie_merge.dbo.device
(nDeviceID, nDefaultNetworkInterfaceID)
SELECT nDeviceID, nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie2.dbo.device
SET IDENTITY INSERT OFF
it gives the error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IDENTITY'.
August 13, 2013 at 7:03 am
You can check the syntax for IDENTITY INSERT here:
SET IDENTITY_INSERT (Transact-SQL)
I'd like to add that most of your issues can easily be solved by just reading the relevant pages on MSDN/Technet.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 7:04 am
John Mitchell-245523 (8/13/2013)
Either:
SET IDENTITY INSERT ONINSERT INTO WhatsUp_Assyst_replicatie_All.dbo.device
(nDeviceID, nDefaultNetworkInterfaceID)
SELECT nDeviceID, nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie.dbo.device
SET IDENTITY INSERT OFF
or:
INSERT INTO WhatsUp_Assyst_replicatie_All.dbo.device(nDefaultNetworkInterfaceID)
SELECT nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie.dbo.device
I would recommend the second method, since you are likely to get duplicates in the identity column with the first, which you probably don't want.
John
I Fix it problem;
SET IDENTITY_INSERT device ON
INSERT INTO WhatsUp_Assyst_replicatie_merge.dbo.device
(nDeviceID, nDefaultNetworkInterfaceID)
SELECT nDeviceID, nDefaultNetworkInterfaceID
FROM WhatsUp_Assyst_replicatie2.dbo.device
SET IDENTITY_INSERT device OFF
August 14, 2013 at 11:50 am
Rather than inserting into one table, another option would be to create a virtual table using a partitioned view.
create view v_WhatsUp_Assyst
as
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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply