CODEWITHSUNDEEP
sqlsql-server-2005
user2266515
user2266515

Reputation: 21

sql select into subquery

I'm doing a data conversion between systems and have prepared a select statement that identifies the necessary rows to pull from table1 and joins to table2 to display a pair of supporting columns. This select statement also places blank columns into the result in order to format the result for the upload to the destination system.

Beyond this query, I will also need to update some column values which I'd like to do in a separate statement operation in a new table. Therefore, I'm interested in running the above select statement as a subquery inside a SELECT INTO that will essentially plop the results into a staging table. 

SELECT 
    dbo_tblPatCountryApplication.AppId, '', 
    dbo_tblPatCountryApplication.InvId,
    'Add', dbo_tblpatinvention.disclosurestatus, ...
FROM 
    dbo_tblPatInvention 
INNER JOIN 
    dbo_tblPatCountryApplication ON dbo_tblPatInvention.InvId = dbo_tblPatCountryApplication.InvId
ORDER BY 
    dbo_tblpatcountryapplication.invid;

I'd like to execute the above statement so that the results are dumped into a new table. Can anyone please advise how to embed the statement into a subquery that will play nicely with a SELECT INTO?

Upvotes: 2

Views: 6820

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15261

You can simply add an INTO clause to your existing query to create a new table filled with the results of the query:

SELECT ...
INTO MyNewStagingTable -- Creates a new table with the results of this query
FROM MyOtherTable
    JOIN ...

However, you will have to make sure each column has a name, as in:

SELECT dbo_tblPatCountryApplication.AppId, -- Cool, already has a name
    '' AS Column2, -- Given a name to create that new table with select...into
    ...
INTO MyNewStagingTable
FROM dbo_tblPatInvention INNER JOIN ...

Also, you might like to use aliases for your tables, too, to make code a little more readable;

SELECT a.AppId,
    '' AS Column2,
    ...
INTO MyNewStagingTable
FROM dbo_tblPatInvention AS i
    INNER JOIN dbo_tblPatCountryApplication AS a ON i.InvId = a.InvId
ORDER BY a.InvId

One last note is that it looks odd to have named your tables dbo_tblXXX as dbo is normally the schema name and is separated from the table name with dot notation, e.g. dbo.tblXXX. I'm assuming that you already have a fully working select query before adding the into clause. Some also consider using Hungarian notation in your database (tblName) to be a type of anti-pattern to avoid.

Upvotes: 1

peterm
peterm

Reputation: 92835

Try

INSERT INTO stagingtable (AppId, ...)
SELECT ... --your select goes here

Upvotes: 0

mickfold
mickfold

Reputation: 2003

If the staging table doesn't exist and you want to create it on insert then try the following:

SELECT dbo_tblPatCountryApplication.AppId,'', dbo_tblPatCountryApplication.InvId,
       'Add', dbo_tblpatinvention.disclosurestatus .......
INTO StagingTable
FROM dbo_tblPatInvention 
INNER JOIN dbo_tblPatCountryApplication 
              ON dbo_tblPatInvention.InvId = dbo_tblPatCountryApplication.InvId;

If you want to insert them in a specific order then use try using a sub-query in the from clause:

SELECT *
INTO StagingTable
FROM 
(
   SELECT dbo_tblPatCountryApplication.AppId, '', dbo_tblPatCountryApplication.InvId, 
          'Add', dbo_tblpatinvention.disclosurestatus .......
   FROM dbo_tblPatInvention 
   INNER JOIN dbo_tblPatCountryApplication ON 
              dbo_tblPatInvention.InvId = dbo_tblPatCountryApplication.InvId
    order by dbo_tblpatcountryapplication.invid
) a;

Upvotes: 0

Related Questions