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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah, I wanted to avoid having to specify each column name :-/ thats why i am using select a.* in my select statements.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.