Create Table from another''s definition
-
Forgive me if this has been answered before, I searched around this site and others but couldn't find an answer. It could be that I didn't know exactly how to ask the question.
I was wondering if there was a way to create a table (temp or otherwise) using the fields of other tables. I remember doing something similar within SAP a long time ago and was wondering if it was possible with SQL 2000 (or 2005).
Here's an example:
Create table TestTbl (
record_id int,
customer_name like customers.customer_name,
address like addresses.address_name,
order_number like salesorders.sales_order
)
Any thoughts? Thanks in advance!
-
You can do it ... sort of with temp tables:
...
select 1 as record_id, c.costumer_name, a.address, s.order_number
into
#T1
FROM salesorders s, addressess a, customers c
where 1=2
* Noel -
You can script the table and then use find/replace to replace it with a new table name..that way you'd have an exact copy! Is that what you're asking ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Not exactly. That would provide a complete copy of an existing table. I would like to make a new table using parts of other existing tables.
So (using my example) if the definition of customers.customer_name changed from varchar(25) to varchar(35), my table definition code for TestTbl would not need to be changed. It would refer to the customers table to get the definition of customer_name.
-
why don't you just create a view then ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
You also could try selecting into a new table by joining the tables of interest and scripting that new table?
I wasn't born stupid - I had to study.
-
Isn't that what I just posted ?
* Noel -
**ASCII stupid question, get a stupid ANSI !!!** -
aaahhh..., no. Well, er, yes...
I wasn't born stupid - I had to study.
-
To make an exact duplicate of a table use SELECT * INTO...
--Copy all rows and column
USE PUBS
SELECT * INTO
dbo.COPYOF_authors
FROM dbo.authors
-- If you want no data in table...
SELECT * INTO
dbo.COPY2OF_authors
FROM dbo.authors
WHERE 1=2
--To Add a column on the fly
USE PUBS
SELECT *
,NewColum='NewText'
,NewNumber=1
INTO dbo.COPY3OF_authors
FROM dbo.authors
--You can even add a a new ID Key on the fly
SELECT
New_ID=IDENTITY (int,1,1)
,*
INTO dbo.COPY3OF_authors
FROM dbo.authors
-
It looks like there isn't something exactly like I was hoping, but I think this solution would work best if I was using a temp table. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply