Avatar of Aleks
Aleks
Flag for United States of America 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 ?
SQL

Avatar of undefined
Last Comment
Umar Topia

8/22/2022 - Mon
SOLUTION
Habib Pourfard

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Aleks

ASKER
It wont work, the first statement copies the ID's, and as part of the the design of the table it sets the first id to be autoincrement. When i run the second one it fails

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.
SOLUTION
Habib Pourfard

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Aleks

ASKER
Yeah, I wanted to avoid having to specify each column name :-/  thats why i am using select a.* in my select statements.
SOLUTION
Umar Topia

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Aleks

ASKER
This is what i ran:

SET IDENTITY_INSERT BDotSheppard.dbo.Addresses ON
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Umar Topia

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Aleks

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.
ASKER CERTIFIED SOLUTION
Habib Pourfard

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Umar Topia

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