Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


copy data from table to table

Posted on 2004-09-05
Medium Priority
Last Modified: 2010-08-05
Let me preface this by saying I hardly anything about SQL...

I have two tables, one will be the "Master" table and will be called "Table A".  I want to import the data (but NOT the structure) from Table B, Table C, etc. into Table A (so that table will eventually have around 4,000 records).

What's the command to that?
Question by:mattybrigh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 15

Expert Comment

ID: 11985099
A common technique is like this:

SELECT b.Column1, b.Column2, c.Column1
FROM TableB b INNER JOIN TableC c ON b.PrimaryKeyColumn = c.ForeignKeyColumn

This creates a new TableA based on the columns in the SELECT clause. "b" and "c" are aliases for the table names so you don't have to retype the whole names over and over.

If tables B & C have the same kind of data, you could do something like this:

SELECT Column1, Column2, Column3 INTO TableA FROM TableB --this creates TableA with data from TableB
INSERT TableA SELECT Column1, Column2, Column3 FROM TableC --this adds the data from TableC to TableA

If you want us to give you the precise queries, you'll need to describe your tables in detail.

Author Comment

ID: 11985136
Well, the essential problem is this.  I've already got the table I need.  I need to make the id table an identity (it's already a primary key).  But, my storage space is an issue and because of the size of the DB, it won't let me alter it in the table design.

Ideally, I'd like to know if there's any way, using SQL, to turn the reviewid field's identity to "Yes" (as it is now, it's no).

I've created a new table called "newreviews2" (the one with all of my current data is "newreviews") and I just want to port the data from my old table to my new table (the new table has all of the same fields and the reviewid field is set to Identity "Yes").

Does that make any sense?
LVL 17

Expert Comment

ID: 11985502
in table design in Enterprise Manager, it does exactly ewhat you are trying to do when you change a column to an identity - it creates a new table, copies all the data across, then drops the original table. If you don't have space to do this thorugh EM, you are not likely to be able to do this using any other method. If you really are stuck for space, probably the best thing to do is to create the new table, and copy a small number of rows at a time into it, deleting as soon as they are copied.
However you say the table has 4000 rows? sounds strange that you don't have enough space to create a table with 4000 rows. How big is each row? Is it possible that the max size of the DB has been set to a very small number, and the database has been set to not grow automatically? In Enterprise Manager, right-click your database, and select properties. Select Data Files, and ensure the "Automatic Grow File" is selected. Ensure this is set for both the data file, and the log file.
Also, check the size of your log file. If it is very large, there may be a problem - have you got regular backups happening etc?
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.


Assisted Solution

Minna earned 1000 total points
ID: 11986710
You can specify which columns of TableA you want to populate when you want to import data from different tables.
Use syntax

INSERT INTO TableA (<col2>, <col3>, <col5>)
SELECT <col1>, <col2>, <col3>

eg if TableA has ID, Title FirstName, Surname, Position, Company and TableB has FirstName, Surname, Company

the command becomes
INSERT INTO TableA (FirstName, Surname, Company)
SELECT FirstName, Surname, Company

Author Comment

ID: 11986732
I've given up...nothing I've tried has worked and nothing anyone has posted here has helped.

Expert Comment

ID: 11986769
Can you tell us what fields are in your tables?  And what kind of data from which table needs to go into Table A?  Your question was a bit vague.

Author Comment

ID: 11986827
My apologies if my description was vague, but if so - it's because I'm not familar with SQL and I prefaced this by saying "I hardly anything about SQL...".

In any case.

I have a table already in my SQL database.  It's called "newreviews".  This is the heart of my site.  I uploaded this table from an Access DB (that was downcoverted from another SQL DB).  The field, reviewid (the autonumber field) got "lost in translation" and is now just a plain number field.  Somehow I had enough storage space to change it to a primary key, but when I try and change it to an autonumber field (i.e. "turn identity from 'No' to 'Yes') it gives me an error saying that I don't have enough free memory to do so.

This is the fault of my provider, Interland, and I'm hoping they'll get off their duffs and fix it.

What I'm tyring to do is figure out another way to either A) Import the data into a new table with the data and "reviewid" field intact and being a primary key/identity "yes" field or B) Find a way to change the "reviewid" field to an identity "yes" field without SQL giving me the error saying I've run out of space.

FYI, I've not been able to truncate the logs, back up the database or do anything else because evidently I need "SA" privileges on the DB to do any of this.

Anyone out there feel my frustration?

I'm happy to give some kind soul access to my SQL database and if it's against EE procedures, I don't care.  I just want this over and done with.

Ok, that's all (and I mean it) I know...
LVL 17

Accepted Solution

BillAn1 earned 1000 total points
ID: 11987882
I think the main problem you need to resolve with your service provider. They need to provide you enough space to be able to make changes like this, plus they should be backing up / maintaining your database properly - after all that's what you are paying them for!!

There is no real alternative, if you don't have the space. An identity column is different to a regular integer, so in order to change from one to the other, you need to make a new table, copy the data across, then drop the old table - this is what Enterprise Mangaer tries to do automatically for you. However, you don't appear to have the space to do this.

LVL 18

Expert Comment

ID: 11988740
It would really help it you posted the schema of Table A b & C !

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 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