copy data from table to table

Posted on 2004-09-05
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?

Assisted Solution

Minna earned 250 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


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 250 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

929 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

10 Experts available now in Live!

Get 1:1 Help Now