SQL Server - Llenar una tabla con un procedimeinto almacenado

Hola Todos:

Tengo un procedimiento almacenado el cual me llena una tabla temporal, y me funciona perfectamente, me piden crear una tabla con los campos que aparecen en la temporal y que el procedimeinto almacenado me llene esa tabla, cree una base de datos llamada servers y en ella cree una tabla llamada uat, la verdad no se como llenar esa tabla uat desde el procedimeinto almacenado, adjunto el procedimiento almacenado para que me ayuden a modificarlo, tambien adjunto la creacion de la tabla, la tabla tiene dos campos que son el nombre del servidor y la fecha que estan setiados, no se como hacer para que me aprezcan en la tabla uat, por favor sino me entendieron la descripcion por favor preguntan para hacer mas claridad


CREACION PROCEDIMIENTO

CREATE PROCEDURE uat

AS

BEGIN

create table #temp1 (loginname varchar(200),--denylogin varchar(20),haaccess varchar(20),

sysadmin varchar(20),securityadmin varchar(20),serveradmin varchar(20),

setupadmin varchar(20),processadmin varchar(20),diskadmin varchar(20),dbcreator varchar(20), bulkadmin varchar(20), ROLES varchar(400)

)

insert into #temp1

select convert (varchar (200),loginname) as loginname, convert(varchar(20),sysadmin ) as sysadmin,

convert(varchar(20),securityadmin ) as securityadmin,convert(varchar(20),serveradmin ) as serveradmin,

convert(varchar(20),setupadmin ) as setupadmin , convert(varchar(20),processadmin ) as processadmin ,

convert(varchar(20),diskadmin ) as diskadmin,convert(varchar(20), dbcreator ) as dbcreator,

convert(varchar(20), bulkadmin ) as bulkadmin,''

from syslogins

------------------------------------

update #temp1 set sysadmin='sysadmin' where sysadmin='1'

update #temp1 set sysadmin='' where sysadmin='0'

--

update #temp1 set securityadmin='securityadmin'where securityadmin='1'

update #temp1 set securityadmin='' where securityadmin='0'

--

update #temp1 set serveradmin='serveradmin'where serveradmin='1'

update #temp1 set serveradmin='' where serveradmin='0'

--

update #temp1 set setupadmin='setupadmin'where setupadmin='1'

update #temp1 set setupadmin='' where setupadmin='0'

--

update #temp1 set processadmin='processadmin'where processadmin='1'

update #temp1 set processadmin='' where processadmin='0'

--

update #temp1 set diskadmin='diskadmin'where diskadmin='1'

update #temp1 set diskadmin='' where diskadmin='0'

--

update #temp1 set dbcreator='dbcreator'where dbcreator='1'

update #temp1 set dbcreator='' where dbcreator='0'

--

update #temp1 set bulkadmin='bulkadmin'where bulkadmin='1'

update #temp1 set bulkadmin='' where bulkadmin='0'

--

update #temp1

set ROLES= sysadmin + ','+ securityadmin + ','+ serveradmin + ','+ setupadmin + ','+ processadmin + ','+ diskadmin

+ ','+ dbcreator+ ','+ bulkadmin

--

update #temp1

set ROLES =replace(ROLES,',,','')

update #temp1

set ROLES =''

where ROLES =','


update #temp1

set ROLES ='public'

where roles =''


update #temp1

set ROLES =substring(roles,2,len(roles))

where substring(roles,len(roles)-len(roles),2) =','


update #temp1

set roles = substring(roles,1,len(roles)-1)

where substring(roles,len(roles),1)=','


select cliente='TELEFONICA', plataforma='MICROSOFT SQL SERVER', convert(varchar(100), serverproperty('servername')) as instancia ,Loginname,

case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'Usuario del sistema'

else 'Usuario Personal' end as Tipo_usuario,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'IBM'

else 'TELEFONICA' end as owner1,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'JAVIER ANDRES RUEDA'

else 'TELEFONICA' end as ownername,



case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'D72714'

else 'NULL' end as ownercod,


Roles, Country='661' ,

getdate() as fecha

from #temp1

drop table #temp1

END

GO

******************************************************************************************************************

CREACION DE LA TABLA UAT

USE [Servers]
GO
/****** Object: Table [dbo].[Repor_Uat] Script Date: 07/17/2012 15:10:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Repor_Uat](
[Contador] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Cliente] [varchar](50) NULL,
[Plataforma] [varchar](50) NULL,
[Instancia] [varchar](50) NULL,
[Loginname] [varchar](50) NULL,
[Tipo_Usuario] [varchar](50) NULL,
[Owner1] [varchar](50) NULL,
[Ownwercod] [varchar](50) NULL,
[Roles] [varchar](50) NULL,
[Country] [numeric](18, 0) NULL,
[Fecha] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

No se si te entendi, pero vamos a ver si esto es lo que ocupas:

AL final de hacer todo lo que ocupas en la tabla temporal, simplemente haces un insert/select desde la tabla temporal a la tabla permanente, algo asi:

Insert into [Repor_Uat] (lista de campos)
select (lista de campos)
from #temp1


NOTA:

La tabla temporal DEBE dropearse DESPUES de esta operacion.

Espero haberte ayudado.

La verdad no me funciono realice lo siguiente:

CREATE PROCEDURE uat

AS

BEGIN

create table #temp1 (loginname varchar(200),--denylogin varchar(20),haaccess varchar(20),

sysadmin varchar(20),securityadmin varchar(20),serveradmin varchar(20),

setupadmin varchar(20),processadmin varchar(20),diskadmin varchar(20),dbcreator varchar(20), bulkadmin varchar(20), ROLES varchar(400)

)

insert into #temp1

select convert (varchar (200),loginname) as loginname, convert(varchar(20),sysadmin ) as sysadmin,

convert(varchar(20),securityadmin ) as securityadmin,convert(varchar(20),serveradmin ) as serveradmin,

convert(varchar(20),setupadmin ) as setupadmin , convert(varchar(20),processadmin ) as processadmin ,

convert(varchar(20),diskadmin ) as diskadmin,convert(varchar(20), dbcreator ) as dbcreator,

convert(varchar(20), bulkadmin ) as bulkadmin,''

from syslogins

------------------------------------

update #temp1 set sysadmin='sysadmin' where sysadmin='1'

update #temp1 set sysadmin='' where sysadmin='0'

--

update #temp1 set securityadmin='securityadmin'where securityadmin='1'

update #temp1 set securityadmin='' where securityadmin='0'

--

update #temp1 set serveradmin='serveradmin'where serveradmin='1'

update #temp1 set serveradmin='' where serveradmin='0'

--

update #temp1 set setupadmin='setupadmin'where setupadmin='1'

update #temp1 set setupadmin='' where setupadmin='0'

--

update #temp1 set processadmin='processadmin'where processadmin='1'

update #temp1 set processadmin='' where processadmin='0'

--

update #temp1 set diskadmin='diskadmin'where diskadmin='1'

update #temp1 set diskadmin='' where diskadmin='0'

--

update #temp1 set dbcreator='dbcreator'where dbcreator='1'

update #temp1 set dbcreator='' where dbcreator='0'

--

update #temp1 set bulkadmin='bulkadmin'where bulkadmin='1'

update #temp1 set bulkadmin='' where bulkadmin='0'

--

update #temp1

set ROLES= sysadmin + ','+ securityadmin + ','+ serveradmin + ','+ setupadmin + ','+ processadmin + ','+ diskadmin

+ ','+ dbcreator+ ','+ bulkadmin

--

update #temp1

set ROLES =replace(ROLES,',,','')

update #temp1

set ROLES =''

where ROLES =','


update #temp1

set ROLES ='public'

where roles =''


update #temp1

set ROLES =substring(roles,2,len(roles))

where substring(roles,len(roles)-len(roles),2) =','

update #temp1

set roles = substring(roles,1,len(roles)-1)

where substring(roles,len(roles),1)=','


select cliente='TELEFONICA', plataforma='MICROSOFT SQL SERVER', convert(varchar(100), serverproperty('servername')) as instancia ,Loginname,

case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'Usuario del sistema'

else 'Usuario Personal' end as Tipo_usuario,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'IBM'

else 'TELEFONICA' end as owner1,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'JAVIER ANDRES RUEDA'

else 'TELEFONICA' end as ownername,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'D72714'

else 'NULL' end as ownercod,


Roles, Country='661' ,

getdate() as fecha

Insert into [Uat] (cliente,plataforma,instancia,Loginname,Tipo_usuario,owner1,ownername,ownercod,Roles,Country,fecha)
select cliente,plataforma,instancia,Loginname,Tipo_usuario,owner1,ownername,ownercod,Roles,Country,fecha

from #temp1



drop table #temp1

END

GO

Y me aprecen los siguientes errores, no se como me puedes ayudar

Msg 207, Level 16, State 1, Procedure uat, Line 130
Invalid column name 'Loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 142
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 144
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 146
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 171
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 173
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 175
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 200
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 202
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 204
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 232
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 234
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 236
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 245
Invalid column name 'Roles'.

La verdad no me funciono realice lo siguiente:

CREATE PROCEDURE uat

AS

BEGIN

create table #temp1 (loginname varchar(200),--denylogin varchar(20),haaccess varchar(20),

sysadmin varchar(20),securityadmin varchar(20),serveradmin varchar(20),

setupadmin varchar(20),processadmin varchar(20),diskadmin varchar(20),dbcreator varchar(20), bulkadmin varchar(20), ROLES varchar(400)

)

insert into #temp1

select convert (varchar (200),loginname) as loginname, convert(varchar(20),sysadmin ) as sysadmin,

convert(varchar(20),securityadmin ) as securityadmin,convert(varchar(20),serveradmin ) as serveradmin,

convert(varchar(20),setupadmin ) as setupadmin , convert(varchar(20),processadmin ) as processadmin ,

convert(varchar(20),diskadmin ) as diskadmin,convert(varchar(20), dbcreator ) as dbcreator,

convert(varchar(20), bulkadmin ) as bulkadmin,''

from syslogins

------------------------------------

update #temp1 set sysadmin='sysadmin' where sysadmin='1'

update #temp1 set sysadmin='' where sysadmin='0'

--

update #temp1 set securityadmin='securityadmin'where securityadmin='1'

update #temp1 set securityadmin='' where securityadmin='0'

--

update #temp1 set serveradmin='serveradmin'where serveradmin='1'

update #temp1 set serveradmin='' where serveradmin='0'

--

update #temp1 set setupadmin='setupadmin'where setupadmin='1'

update #temp1 set setupadmin='' where setupadmin='0'

--

update #temp1 set processadmin='processadmin'where processadmin='1'

update #temp1 set processadmin='' where processadmin='0'

--

update #temp1 set diskadmin='diskadmin'where diskadmin='1'

update #temp1 set diskadmin='' where diskadmin='0'

--

update #temp1 set dbcreator='dbcreator'where dbcreator='1'

update #temp1 set dbcreator='' where dbcreator='0'

--

update #temp1 set bulkadmin='bulkadmin'where bulkadmin='1'

update #temp1 set bulkadmin='' where bulkadmin='0'

--

update #temp1

set ROLES= sysadmin + ','+ securityadmin + ','+ serveradmin + ','+ setupadmin + ','+ processadmin + ','+ diskadmin

+ ','+ dbcreator+ ','+ bulkadmin

--

update #temp1

set ROLES =replace(ROLES,',,','')

update #temp1

set ROLES =''

where ROLES =','


update #temp1

set ROLES ='public'

where roles =''


update #temp1

set ROLES =substring(roles,2,len(roles))

where substring(roles,len(roles)-len(roles),2) =','

update #temp1

set roles = substring(roles,1,len(roles)-1)

where substring(roles,len(roles),1)=','


select cliente='TELEFONICA', plataforma='MICROSOFT SQL SERVER', convert(varchar(100), serverproperty('servername')) as instancia ,Loginname,

case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'Usuario del sistema'

else 'Usuario Personal' end as Tipo_usuario,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'IBM'

else 'TELEFONICA' end as owner1,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'JAVIER ANDRES RUEDA'

else 'TELEFONICA' end as ownername,


case

when loginname in (

'sa',

'AVIANCA\MSSQL2K',

'AVIANCA\G SQLADMINS',

'LATAM\grupo adm sql server',

'TELECOM\Grupo Bases datos IBM',

'Builtin\administrators')

OR loginname like '%SQLServer2%'

OR loginname like '%##MS%'

OR loginname like '%NT AUTHORITY%'

then 'D72714'

else 'NULL' end as ownercod,


Roles, Country='661' ,

getdate() as fecha

Insert into [Uat] (cliente,plataforma,instancia,Loginname,Tipo_usuario,owner1,ownername,ownercod,Roles,Country,fecha)
select cliente,plataforma,instancia,Loginname,Tipo_usuario,owner1,ownername,ownercod,Roles,Country,fecha

from #temp1



drop table #temp1

END

GO

Y me aprecen los siguientes errores, no se como me puedes ayudar

Msg 207, Level 16, State 1, Procedure uat, Line 130
Invalid column name 'Loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 134
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 142
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 144
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 146
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 157
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 171
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 173
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 175
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 186
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 200
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 202
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 204
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 218
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 232
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 234
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 236
Invalid column name 'loginname'.
Msg 207, Level 16, State 1, Procedure uat, Line 245
Invalid column name 'Roles'.