• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

INSERT INTO tableA to tableB

Hi,

I have two tables that are almost identical.. the only difference is that tableB has additional field with Identity ON...

I tried to run this:

insert into tableB
select * from tableA

I want to avoid specifying all the fields... but I get this error:

An explicit value for the identity column in table 'tableB' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I also tried to run SET IDENTITY_INSERT TableB ON before running the query but same result...

is there any way to do this?

Thanks

0
ezzadin
Asked:
ezzadin
  • 9
  • 3
  • 2
  • +4
2 Solutions
 
Patrick MatthewsCommented:
Seems pretty clear: IDENTITY INSERT must be on, and you must specify the columns.In any event, using:INSERT INTO TableB SELECT * FROM TableAis a bad habit: you should be specifying your columns anyway...
0
 
agileblowfishCommented:
To do the insert and select in one statement like that, I think both tables have to have identical fields.  I think you are going to have to specify the fields.
You might try something like this:

insert into tableB (field1, field2, field3) values (
select field1, field2, '' as field3 from tableA)

or if field3 is a numerical:

insert into tableB (field1, field2, field3) values (
select field1, field2, 0 as field3 from tableA)
0
 
sammySeltzerCommented:
Tagileblowfish is correct.

The columns need to be identical but this post will solve your problem with a nifty workaround:

http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_26415853.html

Bottom solution

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
QPRCommented:
An Oracle URL?

Matthewspatrick hit the nail on the head.
You must specify all the column names in the statement.
When you do this the identity column will take care of itself
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, matthewspatrick is correct; however, just to add an interpretation of your question:

You say the tables are identical, except that the one has an additional column with identity specification.
If that is the case, then you should insert every column from TableA into TableB, but DO NOT list the identity column in the insert list.  This will NOT require IDENTITY INSERT to be ON and will allow the identity column to generate the appropriate id values on insert.

In other words, as matthewspatrick said, just spell out the colums:

insert into tableB(col2, col3, col4, col5)
select col1, col2, col3, col4 from tableA;

Imagine col1 in tableB is the identity.
0
 
QPRCommented:
which is exactly what has already been said :)
0
 
Kevin CrossChief Technology OfficerCommented:
Not quite QPR.  Please NOTE that if the difference between the tables is the additional column that is the identity then leaving it off the you DO NOT need IDENTITY INSERT on.  I see now you were trying to say that in your post, but it is not clear as simply specifying the columns in the INSERT INTO, I have seen folks list the ID and so have issues -- so I was making that very clear.  Sorry if this hit a nerve or something.  Notice I said in my post that matthewspatrick is correct as I think his first post answers the question and should be selected as the answer.  Not looking for points. *smile*
0
 
Kevin CrossChief Technology OfficerCommented:
"You must specify all the column names in the statement.
When you do this the identity column will take care of itself"

Think how someone unfamiliar with SQL will read the above and what will happen if you add ALL the columns of tableb to the insert into clause. :)
0
 
Kevin CrossChief Technology OfficerCommented:
Now if you want to, you can add ALL the columns and pass a NULL for that field in the select and then it is true that the identity column will take care of itself.

INSERT INTO TableB(id, col1, col2, col3, col4)
SELECT null, col1, col2, col3, col4
FROM TableA;

The details are what are most useful here since the Asker is clearly unfamiliar with SQL as much as you and I are.  Leaving off the details changes the answer, so again I say my statement was not already said. *smile*
0
 
ezzadinAuthor Commented:
Thanks all..

mwvisa1, I have been using SQL for the last 6 years on a daily bases as far of my job and do know a lot about it..the table has about 65 fields so I thought I'd ask the question and there might be a solution to it, instead of dealing with all the fields... So please do not judge people by the question they are asking...

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
*smile* was not judging you in bad way, I was making a point to another expert that my giving more detail was not the same thing as merely making a statement that someone without your experience may not understand.  

If you do, then you will see that I gave you another solution even though it is not the best practice way which is to pass NULL for the first column.

so you could use:
insert into tableB
select null as id, * from tableA;

As long as your columns were in the correct order otherwise.

Again not the best means, but if can't type in all the columns due to other constraints it is a way around the limitation.

Regards,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
Nevermind me, having one of those nights I think. @QPR, my apologies.
0
 
Kevin CrossChief Technology OfficerCommented:
I am mixing my SQL platforms.  For SQL server, the select null to default the identity won't work.
0
 
Kevin CrossChief Technology OfficerCommented:
I must be stuck in MySQL mode because I have been staring at that for work the last 2 days for about 10 hours each day.
0
 
Kevin CrossChief Technology OfficerCommented:
Alright last post from me.  Just wanted to make it up to you for my going off ranch there.  Patrick's answer is still valid and the primary answer to your question.  This is merely my peace offering.  Will allow you to update you table no matter how many columns you have ...

Will work for any tables that have similar structure where other field in the insert into table will default correctly or NULL is allowed.

To make this more robust, you can JOIN the information schema to itself with the second version being TABLE_NAME = 'tablea' then you will only generate list for columns in both tables.

Okay, I will remove myself from this thread. *smile*
declare @cols varchar(max), @sql varchar(max);

select @cols = coalesce(@cols+',', '') + '[' + COLUMN_NAME + ']'
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'tableb'
and COLUMN_NAME <> 'id'

set @sql = 'insert into tableb('+@cols+') select '+@cols+' from tablea;';

exec(@sql);

Open in new window

0
 
cyberkiwiCommented:
Despite all that has been said, what was originally posted in the question should have worked as is.


create table tablewith4fields (a int ,b int, c int, d int)
insert tablewith4fields values (1,2,3,4)
insert tablewith4fields values (5,2,7,4)
;
create table tablewith4fields_plus1 (id int identity, a int ,b int, c int, d int)
insert tablewith4fields_plus1
;
---- stop here, take a deep breath. Okay, now run the next statement

select * from tablewith4fields

==================

Okay, now when you say " the only difference is that tableB has additional field with Identity ON..." I'm sure you mean something other that how I read that statement.  Do you mean that tableA has the additional field, or do you mean that one of the fields (in tableA) has been turned into an identity (in tableB)?
0
 
cyberkiwiCommented:
Must have been away from EE for too long. Can't even make a proper post.
Please see correction below.

create table tablewith4fields (a int ,b int, c int, d int)
insert tablewith4fields values (1,2,3,4)
insert tablewith4fields values (5,2,7,4)
;
create table tablewith4fields_plus1 (id int identity, a int ,b int, c int, d int)
;
---- stop here, take a deep breath. Okay, now run the next statement

insert tablewith4fields_plus1
select * from tablewith4fields
0
 
ezzadinAuthor Commented:
Thanks cyberkiwi,

TableA has an additional field.

Thanks  
0
 
sammySeltzerCommented:
sorrry I am late for the party.

Yes, even though the url I posted is for an oracle solution, they used sql ansi standard and he could use it easily for his solution.

Perhaps I should have made that clear.

Infact, compare that url to cyberkiwi's solution.

See any difference?
0
 
ezzadinAuthor Commented:
Thanks All...

sammySeltzer, that solution actually worked... so as long as the fields are in the same order and identical then insert into tableB select * from tableA will do the trick..

Yes and I agree with all the experts that this method is not a good idea at all... always best to specify the fields...this was just a project for myself .. In real world I would specify all the fields...

Thanks again...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 9
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now