How to create a table with multiple columns

Question

Struggling with a create table statement which is based on this select into statement below:

@MaxAPDRefundAmount     money = 13.00 

...

    select pkd.*,  pd.ProviderReference, per.FirstName, per.Surname, @MaxAPDRefundAmount [MaxAPDRefundAmount],commission.Type [Commission] into #StagedData from CTE_PackageData pkd
                        inner join J2H.dbo.Package pk on pkd.Reference = pk.Reference
                        inner join J2H.dbo.Product pd on pk.PackageId = pd.PackageId
                        inner join J2H.dbo.FlightReservation fr on pd.ProductId  = fr.ProductId
                                and fr.FlightBoundID = 1
                        inner join J2H.dbo.ProductPerson pp on pd.ProductId = pp.ProductID
                            and pp.StatusId < 7
                        inner join J2H.dbo.Flight f on fr.FlightId = f.FlightID
                        inner join J2H.dbo.Person per on pk.PackageId = per.PackageId
                                    and per.PersonId = pp.PersonId
                        inner join J2H.dbo.PersonType pt on per.PersonTypeId = pt.PersonTypeID

We are changing a select into to just normal insert and select, so need a create table (we are going to create a temp (hash tag table) and not declaring a variable table. Also there is a pkd.* at the start as well so I am confused in knowing which fields to include in the create table. Do I include all the fields in the select statement into the create statement?

Update:

So virtually I know I need to include the data types below but I can just do:

create table #StagedData
        (
                pkd.*,  
                pd.ProviderReference, 
                per.FirstName, 
                per.Surname, 
                @MaxAPDRefundAmount [MaxAPDRefundAmount],
                commission
        )

If it's worth doing, it's worth doing well. Replace pkd.* with a list of just the fields you actually need. You only have to do it once. Plus it may have taken less time to do that than it did to ask your question. — Dan Bracuk
– Dan Bracuk, Commented Dec 18, 2015 at 12:08
If possible (if pkd is not frequently changed), I would only use pkd.id (or whetever unique ID you have in pkd table) in temporary table - you could afterwards always join temp table to pkd. This makes your procedure simpler to maintain, when pkd structure changes and so on. — Arvo
– Arvo, Commented Dec 18, 2015 at 12:17

HoneyBadger · Accepted Answer · 2015-12-18 12:08:46Z

2

"Do I include all the fields in the select statement into the create statement?" Well, that depends, if you need them, than yes, if not than no. It's impossible for us to say whether you need them... If you're running this exact query as insert, than yes.

As for the create statement, you can run the query you have, but replace into #StagedData with something like into TEMP_StagedData. In management studio you can let sql server build the create query for you: right-click the newly created TEMP_StagedData table in the object explorer (remember to refresh), script Table as, CREATE To and select New Query Editor Window.

edited Dec 18, 2015 at 12:08

answered Dec 18, 2015 at 12:03

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HoneyBadger Over a year ago

I'm not really sure what you mean with the update. As you say you need datatypes, also, columnnames like pkd.* are illegal if not within [], and definitly not recommended. If with pkd.* you mean "create columns as in table pkd", than no, you can't do that. I'd stick to my original answer, if I were you

Tab Alleman · Accepted Answer · 2015-12-18 14:13:52Z

0

The documentation of the CREATE TABLE statement is pretty straightforward.

No. Clearly, you cannot use pkd.* in a create table statement.

What you can do is run your old SELECT INTO statement as a straight SELECT (remove the INTO #stagedata) part, and look at the columns that get returned by the SELECT.

Then write a CREATE TABLE statement that includes those columns.

answered Dec 18, 2015 at 14:13

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Comments

Metaphor · Accepted Answer · 2015-12-18 12:23:37Z

0

To create a table from a SELECT without inserting data, add a WHERE clause that never returns True.

Like this:

SELECT * INTO #TempTable FROM Table WHERE 1=0

Once the table with the columns for your SELECT, you can add additional columns with ALTER TABLE.

ALTER TABLE #TempTable ALL ExtraColumn INT

Then do your INSERT/SELECT.

edited Dec 18, 2015 at 12:23

answered Dec 18, 2015 at 12:16

Metaphor

6,42512 gold badges58 silver badges81 bronze badges

1 Comment

HoneyBadger Over a year ago

If you look closely, you'll see that's the structure of the query OP already has, he wants to transform it into a insert into structure, with the table already present.

Collectives™ on Stack Overflow

How to create a table with multiple columns

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related