copy data from table to table

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?
Who is Participating?
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.

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.
mattybrighAuthor Commented:
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?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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?
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
mattybrighAuthor Commented:
I've given up...nothing I've tried has worked and nothing anyone has posted here has helped.
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.
mattybrighAuthor Commented:
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...
It would really help it you posted the schema of Table A b & C !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.