Go Premium for a chance to win a PS4. Enter to Win


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
  • 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?
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.


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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

963 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