Solved

INSERT INTO tableA to tableB

Posted on 2010-09-17
20
685 Views
Last Modified: 2012-05-10
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
Comment
Question by:ezzadin
  • 9
  • 3
  • 2
  • +4
20 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 2

Expert Comment

by:agileblowfish
Comment Utility
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
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 350 total points
Comment Utility
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
 
LVL 29

Expert Comment

by:QPR
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 29

Expert Comment

by:QPR
Comment Utility
which is exactly what has already been said :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
"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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:ezzadin
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
*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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Nevermind me, having one of those nights I think. @QPR, my apologies.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
I am mixing my SQL platforms.  For SQL server, the select null to default the identity won't work.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 150 total points
Comment Utility
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
 

Author Comment

by:ezzadin
Comment Utility
Thanks cyberkiwi,

TableA has an additional field.

Thanks  
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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
 

Author Comment

by:ezzadin
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now