asked on Dump data into new database issue
I am making selections from database A and want to dump the results into database B.
Today i am running this statement:
USE BDotSheppard
SELECT *
INTO dbo.Addresses
FROM BDotDev.dbo.Addresses
WHERE UserId = 15304
Later on ill run
USE BDotSheppard
SELECT *
INTO dbo.Addresses
FROM BDotDev.dbo.Addresses
WHERE UserId = 15555
The problem is that when I run the second one it says that the object already exists and won't dump the data on top of the data I already have.
How can i simply add the data to this already existing object ?
Today i am running this statement:
USE BDotSheppard
SELECT *
INTO dbo.Addresses
FROM BDotDev.dbo.Addresses
WHERE UserId = 15304
Later on ill run
USE BDotSheppard
SELECT *
INTO dbo.Addresses
FROM BDotDev.dbo.Addresses
WHERE UserId = 15555
The problem is that when I run the second one it says that the object already exists and won't dump the data on top of the data I already have.
How can i simply add the data to this already existing object ?
SQL
Last Comment
Umar Topia
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yeah, I wanted to avoid having to specify each column name :-/ thats why i am using select a.* in my select statements.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This is what i ran:
SET IDENTITY_INSERT BDotSheppard.dbo.Addresses
INSERT INTO dbo.Addresses
SELECT *
FROM BDotDev.dbo.Addresses
WHERE UserId = 15555
SET IDENTITY_INSERT dbo.Addresses OFF
This is the error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.Addresses' can only be specified when a column list is used and IDENTITY_INSERT is ON.
SET IDENTITY_INSERT BDotSheppard.dbo.Addresses
INSERT INTO dbo.Addresses
SELECT *
FROM BDotDev.dbo.Addresses
WHERE UserId = 15555
SET IDENTITY_INSERT dbo.Addresses OFF
This is the error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'dbo.Addresses' can only be specified when a column list is used and IDENTITY_INSERT is ON.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Addresses' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'dbo.Addresses' can only be specified when a column list is used and IDENTITY_INSERT is ON.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yup... My Bad
If you want to Enable/Disable Identity then you will have to explicitly specify the Column Names
The other alternative could be....
Drop Identity by Alter Table
Delete Existing Data
Insert Data
Create Identity by Alter Table
If you want to Enable/Disable Identity then you will have to explicitly specify the Column Names
The other alternative could be....
Drop Identity by Alter Table
Delete Existing Data
Insert Data
Create Identity by Alter Table
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.Addresses' can only be specified when a column list is used and IDENTITY_INSERT is ON.