CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4334

How to create a table with multiple columns

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
        )

Upvotes: 0

Views: 361

Answers (3)

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 0

Metaphor
Metaphor

Reputation: 6415

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.

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

"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.

Upvotes: 2

Related Questions