Solved

copy data from table to table

Posted on 2004-09-05
9
2,538 Views
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?
0
Comment
Question by:mattybrigh
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11985099
A common technique is like this:

SELECT b.Column1, b.Column2, c.Column1
INTO TableA
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.
0
 

Author Comment

by:mattybrigh
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?
0
 
LVL 17

Expert Comment

by:BillAn1
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?
0
 
LVL 3

Assisted Solution

by:Minna
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>
FROM TableB

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
FROM TableB
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:mattybrigh
ID: 11986732
I've given up...nothing I've tried has worked and nothing anyone has posted here has helped.
0
 
LVL 3

Expert Comment

by:Minna
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.
0
 

Author Comment

by:mattybrigh
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...
0
 
LVL 17

Accepted Solution

by:
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.

0
 
LVL 18

Expert Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

758 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

21 Experts available now in Live!

Get 1:1 Help Now