Create a table from Column Data in another Table

Question

Post reply

Create a table from Column Data in another Table

Cleech

Mr or Mrs. 500

Points: 513
More actions
October 7, 2010 at 8:46 am

#241383

Hello All:
I have a table that lists column names that I would like to use for a new table. The table I have is basically a list of column names from one table matched to the new fields I would like to use for the new table. I could also add data types to this table to use in a create table statement. If this is a good approach.
It looks something like this
ColumnName DataType
strLastName nvarchar(50)
strFirstName navarchar(50)
The table is obviously much larger than this.
I hope this makes sense and there is an easy way to accomplish this. Thanks in advance for any help you can give. Please let me know if I can provide any additional information.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

look at this this sample example and generate your SQL statement for creating table using COALESCE

People Table

---------------

Mary

Joe

Fernandez

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

The result would be Mary, Joe, Fernandez

---------------------------------------------------------------------------------------
It begins by taking the first step.

Cleech Mr or Mrs. 500 Points: 513 More actions · Answer 2

Thanks. This works, but I'm still unsure how to use this to create the new table.

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 3

If this is a time one thing then you can get the output of the string and execute the SQL statement

If this needs to happen dynamically then you can do it via stored procedure

CREATE PROCEDURE sp_MyFirstDynamicSP

AS

EXEC(@yourSQLStmt)

---------------------------------------------------------------------------------------
It begins by taking the first step.

Jeff Moden SSC Guru Points: 1003908 More actions · Answer 4

Cleech (10/7/2010)
Thanks. This works, but I'm still unsure how to use this to create the new table.

Here's an example without the COALESCE...

SELECT *

INTO dbo.NewTable

FROM dbo.OldTable

Of course, you can make it so no rows actually move by using WHERE 1=0 and it will still create the new table. You can also change the "*" to a list of columns AND new columns using something like CAST(NULL AS INT) AS NewColumn.

If you want NOT NULL columns, you can use ISNULL(SomeColumn, 0) AS SomeColumn even when you use the WHERE 1=0 trick.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

If I correctly understand what you are trying to do, the attached dynamic SQL code would do the trick for you. But I'm not sure this is easier than just scripting out the CREATE TABLE for your existing table and then editing it to create the schema for your new table. The only advantage I see to your approach is perhaps being able to cross-reference the new table back against the old one.

Fill us in on the big picture here. Why are you wanting to do this?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills