SQL Server 2000: Creating a table based on an insert?

[TheElephantMan]

Hi, all.

Is there a way to create a table based on the contents of an insert statement? Something like...

MAKE TABLE (VARCHAR NAME, INTEGER ID) FROM SELECT * FROM USERS;

Pardon my rusty pseudocode; I've been away from traditional RDBMS' for too long. :-/

Thanks in advance!

TEM

 

[Pattycake245]

There are two ways to do this

create the table first then insert into it based on a select statement:

create table ([name] varchar(30), [id] int)

insert into table
select * from users

(although select * is bad practice as you should type out all fields instead)

OR

create the table on the fly(less desirable)

select [name], [id]
into table from users.

Again, method 1 is better.

Tim

 

[Catadmin]

Do a Select Into statement such as

Select *
Into <newtablename>
from Users


You can also use aggregate functions, WHERE / ORDER BY / GROUP BY and HAVING statements along with this (everything a normal SELECT statement uses) to quantify your data.




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[TheElephantMan]

Hi, all.

I think I misworded my question!

Unfortunately, I will not actually know the column with and contents of what I'd be inserting into a table. It would be akin to how Access allows creating a table based on copy and paste or entering data. Can something like that be done in SQL Server?

MAKE TABLE ITEMS FROM INSERT ("Dog", "Tuesday", "9/29/80", "4.50");

Thanks!

TEM

 

[Catadmin]

So, if I understand you correctly, you just want to take all information from one table and copy into a new table, right?

If so, just follow the Select statement I listed in my post before I mentioned the other stuff.

Or is this not a data issue so much as a structure copy issue?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[TheElephantMan]

Hi, again.

Well, ultimately what I'd like to do is copy the contents of a table in Oracle into a new table, created dynamically, in SQL Server. I'm somewhat limited in my accessibility to both environments, so I'd planned to use a Java program as a shim between the two databases, and connect to each of them through the JDBC-ODBC bridge.

My Java program would store each row of the table in a String array, which would then be placed into a Java Vector. From there, I'd hoped to be able to create a table based on inserting the String array.

Maybe, though, I should scrap the idea and come up with something else, instead?

Thanks!

TEM

 

[Catadmin]

DTS is a *wonderful* thing. So is Import Data.

If your Oracle DB and your SQL DB are on the same network, you can literally use Import Data to grab the entire Oracle (source DB) table, do your data transformations on datatypes as necessary, and put it into SQL (destination DB) all in a quick wizardling few steps.

Look up Import Data on Books Online. You can access the command in Enterprise Manager by right clicking the DB and going to All Tasks -> Import Data. It's pretty intuitive, but I recommend playing with it in a test lab first (and doing a backup of SQL before testing the import) so you can get the hang of the tool.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!