CODEWITHSUNDEEP
sqlsql-servert-sql
Hemesh Patel
Hemesh Patel

Reputation: 25

creating a new table, from various tables in sql

I have created a select query using joins but know I want to create a table from it. But sql keeps saying I have an error next to the '(', but all the examples I have seen have a similar syntax

create table [dbo].[PoliceDataNor] as
(
select
    pc.[CrimeID],
    ct.[CrimeTypeID],
    m.[MonthID],
    fw.FallsWithinID,
    ln.[LSOANameID],
    lc.[LSOACodeID]
from [dbo].[PoliceCrime] as pc, [dbo].[CrimeTypes] as ct, [dbo].[FallsWithins] as fw,
[dbo].[LSAOCodes] as lc, [dbo].[LSAONames] as ln, [dbo].[Months] as m
where
pc.[Crime type]= ct.[Crime type]
and
pc.[Falls within]= fw.[Falls within]
and 
pc.[LSOA code] = lc.[LSOA code]
and
pc.[LSOA name] = ln.[LSOA name]
and
pc.[Month] = m.[Month]
)

Upvotes: 1

Views: 75

Answers (2)

serverSentinel
serverSentinel

Reputation: 994

This can be done like so using modern joins. Be aware that insert into doesn't copy indexes. If you need to query you may want to consider adding them. 

select
    pc.[CrimeID],
    ct.[CrimeTypeID],
    m.[MonthID],
    fw.FallsWithinID,
    ln.[LSOANameID],
    lc.[LSOACodeID]
into [dbo].[PoliceDataNor]
from [dbo].[PoliceCrime] as pc
join [dbo].[CrimeTypes] as ct
   on pc.[Crime type]= ct.[Crime type]
join [dbo].[FallsWithins] as fw
   on pc.[Falls within]= fw.[Falls within]
join [dbo].[LSAOCodes] as lc
   on pc.[LSOA code] = lc.[LSOA code]
join [dbo].[LSAONames] as ln
   pc.[LSOA name] = ln.[LSOA name]
join [dbo].[Months] as m
   pc.[Month] = m.[Month]

Upvotes: 1

Joe C
Joe C

Reputation: 3993

You want to select into:

select
    pc.[CrimeID],
    ct.[CrimeTypeID],
    m.[MonthID],
    fw.FallsWithinID,
    ln.[LSOANameID],
    lc.[LSOACodeID]
into [dbo].[PoliceDataNor]
from [dbo].[PoliceCrime] as pc, [dbo].[CrimeTypes] as ct, [dbo].[FallsWithins] as fw,
[dbo].[LSAOCodes] as lc, [dbo].[LSAONames] as ln, [dbo].[Months] as m
where
pc.[Crime type]= ct.[Crime type]
and
pc.[Falls within]= fw.[Falls within]
and 
pc.[LSOA code] = lc.[LSOA code]
and
pc.[LSOA name] = ln.[LSOA name]
and
pc.[Month] = m.[Month]

Upvotes: 2

Related Questions