[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

How to put the results in to a table

How do I take the results of this statement and insert them into a table in sql 2005/2008


with c as (
  select
     '/'+cast([PERSON_ID] as varchar(249)) ParentPath
     ,b.[PERSON_ID]
     ,b.[BADGE_NUM]
     ,b.[FIRST_NAME]
     ,b.[MIDDLE_INIT]
     ,b.[LAST_NAME]
     ,b.[FACILITY_ID]
     ,b.[JOB_TITLE_ID]
     ,b.[FACILITY_NAME]
     ,b.[JOB_TITLE_NAME]
     ,b.[SUPERVISOR_ID]
  from [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b
  where b.[SUPERVISOR_ID] is null
union all
  select
       cast(c.ParentPath+'/'+CAST(b.[PERSON_ID] as varchar(7)) as varchar(250)) ParentPath
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]  
  from [databse].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b join c on b.[SUPERVISOR_ID] = c.[PERSON_ID])
select ParentPath = c.ParentPath+'/'
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]
from c inner join [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b on c.[PERSON_ID] = b.[PERSON_ID]
order by [LAST_NAME];
0
Steve Samson
Asked:
Steve Samson
  • 3
  • 2
1 Solution
 
Steve SamsonAuthor Commented:
This is the table it needs to go into:
CREATE TABLE [dbo].[t1_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR](
      [PERSON_ID] [int] NOT NULL,
      [parentpath] [nvarchar](250) NOT NULL,
      [BADGE_NUM] [nvarchar](50) NULL,
      [FIRST_NAME] [nvarchar](30) NULL,
      [MIDDLE_INIT] [nvarchar](1) NULL,
      [LAST_NAME] [nvarchar](30) NOT NULL,
      [FACILITY_ID] [nvarchar](50) NULL,
      [JOB_TITLE_ID] [nvarchar](50) NULL,
      [FACILITY_NAME] [nvarchar](250) NULL,
      [JOB_TITLE_NAME] [nvarchar](250) NULL,
      [SUPERVISOR_ID] [int] NULL
)
0
 
hyphenpipeCommented:
Into a table that does not exist:

with c as (
  select
     '/'+cast([PERSON_ID] as varchar(249)) ParentPath
     ,b.[PERSON_ID]
     ,b.[BADGE_NUM]
     ,b.[FIRST_NAME]
     ,b.[MIDDLE_INIT]
     ,b.[LAST_NAME]
     ,b.[FACILITY_ID]
     ,b.[JOB_TITLE_ID]
     ,b.[FACILITY_NAME]
     ,b.[JOB_TITLE_NAME]
     ,b.[SUPERVISOR_ID]
  from [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b
  where b.[SUPERVISOR_ID] is null
union all
  select
       cast(c.ParentPath+'/'+CAST(b.[PERSON_ID] as varchar(7)) as varchar(250)) ParentPath
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]  
  from [databse].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b join c on b.[SUPERVISOR_ID] = c.[PERSON_ID])
select ParentPath = c.ParentPath+'/'
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID] into table_name
from c inner join [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b on c.[PERSON_ID] = b.[PERSON_ID]
order by [LAST_NAME];

Into a table that does exist:

with c as (
  select
     '/'+cast([PERSON_ID] as varchar(249)) ParentPath
     ,b.[PERSON_ID]
     ,b.[BADGE_NUM]
     ,b.[FIRST_NAME]
     ,b.[MIDDLE_INIT]
     ,b.[LAST_NAME]
     ,b.[FACILITY_ID]
     ,b.[JOB_TITLE_ID]
     ,b.[FACILITY_NAME]
     ,b.[JOB_TITLE_NAME]
     ,b.[SUPERVISOR_ID]
  from [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b
  where b.[SUPERVISOR_ID] is null
union all
  select
       cast(c.ParentPath+'/'+CAST(b.[PERSON_ID] as varchar(7)) as varchar(250)) ParentPath
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]  
  from [databse].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b join c on b.[SUPERVISOR_ID] = c.[PERSON_ID])
insert table_name (parent_path, person_id,........list out the rest of the columns)
select ParentPath = c.ParentPath+'/'
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]
from c inner join [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b on c.[PERSON_ID] = b.[PERSON_ID]
order by [LAST_NAME];
0
 
Imran Javed ZiaCommented:
Please try this

 
with c as (
  select 
     '/'+cast([PERSON_ID] as varchar(249)) ParentPath
     ,b.[PERSON_ID]
     ,b.[BADGE_NUM]
     ,b.[FIRST_NAME]
     ,b.[MIDDLE_INIT]
     ,b.[LAST_NAME]
     ,b.[FACILITY_ID]
     ,b.[JOB_TITLE_ID]
     ,b.[FACILITY_NAME]
     ,b.[JOB_TITLE_NAME]
     ,b.[SUPERVISOR_ID]
  from [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b 
  where b.[SUPERVISOR_ID] is null
union all 
  select 
       cast(c.ParentPath+'/'+CAST(b.[PERSON_ID] as varchar(7)) as varchar(250)) ParentPath
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]   
  from [databse].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b join c on b.[SUPERVISOR_ID] = c.[PERSON_ID])
Insert into tableName
select ParentPath = c.ParentPath+'/'
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]
from c inner join [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b on c.[PERSON_ID] = b.[PERSON_ID]
order by [LAST_NAME];

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Steve SamsonAuthor Commented:
Ok I guess i must have pasted twice the statement i am using to build the recordset.
I need to take the results of the query and insert it into this table

CREATE TABLE [dbo].[t1_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR](
      [PERSON_ID] [int] NOT NULL,
      [parentpath] [nvarchar](250) NOT NULL,
      [BADGE_NUM] [nvarchar](50) NULL,
      [FIRST_NAME] [nvarchar](30) NULL,
      [MIDDLE_INIT] [nvarchar](1) NULL,
      [LAST_NAME] [nvarchar](30) NOT NULL,
      [FACILITY_ID] [nvarchar](50) NULL,
      [JOB_TITLE_ID] [nvarchar](50) NULL,
      [FACILITY_NAME] [nvarchar](250) NULL,
      [JOB_TITLE_NAME] [nvarchar](250) NULL,
      [SUPERVISOR_ID] [int] NULL
)
0
 
hyphenpipeCommented:
Exact code.
with c as (
  select
     '/'+cast([PERSON_ID] as varchar(249)) ParentPath
     ,b.[PERSON_ID]
     ,b.[BADGE_NUM]
     ,b.[FIRST_NAME]
     ,b.[MIDDLE_INIT]
     ,b.[LAST_NAME]
     ,b.[FACILITY_ID]
     ,b.[JOB_TITLE_ID]
     ,b.[FACILITY_NAME]
     ,b.[JOB_TITLE_NAME]
     ,b.[SUPERVISOR_ID]
  from [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b
  where b.[SUPERVISOR_ID] is null
union all
  select
       cast(c.ParentPath+'/'+CAST(b.[PERSON_ID] as varchar(7)) as varchar(250)) ParentPath
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]  
  from [databse].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b join c on b.[SUPERVISOR_ID] = c.[PERSON_ID])
  insert t1_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR ([parentpath],[PERSON_ID],[BADGE_NUM],[FIRST_NAME],[MIDDLE_INIT],[LAST_NAME],[FACILITY_ID],[JOB_TITLE_ID],[FACILITY_NAME],[JOB_TITLE_NAME],[SUPERVISOR_ID])
select ParentPath = c.ParentPath+'/'
       ,b.[PERSON_ID]
       ,b.[BADGE_NUM]
       ,b.[FIRST_NAME]
       ,b.[MIDDLE_INIT]
       ,b.[LAST_NAME]
       ,b.[FACILITY_ID]
       ,b.[JOB_TITLE_ID]
       ,b.[FACILITY_NAME]
       ,b.[JOB_TITLE_NAME]
       ,b.[SUPERVISOR_ID]
from c inner join [database].[dbo].[t_ACTIVE_EMPLOYEE_FACILITY_SUPERVISOR] b on c.[PERSON_ID] = b.[PERSON_ID]
order by [LAST_NAME];

Open in new window

0
 
Steve SamsonAuthor Commented:
This solution worked like a charm!!!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now