Ejemplo de Bulk Insert con SQL Server 2008.
CREATE TABLE tmpCSV (cuenta varchar (24), p1 NUMERIC(12,2), p2 NUMERIC(12,2), p3 NUMERIC(12,2), p4 NUMERIC(12,2), p5 NUMERIC(12,2), p6 NUMERIC(12,2),
p7 NUMERIC(12,2), p8 NUMERIC(12,2), p9 NUMERIC(12,2), p10 NUMERIC(12,2), p11 NUMERIC(12,2), p12 NUMERIC(12,2))
BULK INSERT tmpCSV FROM 'D:\Desarrollo.NET\Presupuesto.csv' WITH (FIELDTERMINATOR = ',')
SELECT * FROM tmpCSV
DROP TABLE tmpCSV
Donde el archivo debe tener la estructura correcta.
310500607010102,100,100,100,100,100,100,100,100,100,100,100,100
310500607020101,100,100,100,100,100,100,100,100,100,100,100,100
310500607020102,100,100,100,100,100,100,100,100,100,100,100,100
310500607030101,100,100,100,100,100,100,100,100,100,100,100,100
miércoles, 11 de septiembre de 2013
miércoles, 7 de agosto de 2013
Ejemplos de UNPIVOT
-- Horizontal
SELECT cuenta, agua, saneamto, tratamto, otros, multa, recargos
FROM dbo.FACTURA
WHERE cuenta > 0
-- Vertical
SELECT CUENTA, CONCEPTO, IMPORTE
FROM
(SELECT cuenta, agua, saneamto, tratamto, otros, multa, recargos
FROM dbo.FACTURA
WHERE cuenta > 0) p
UNPIVOT
(IMPORTE FOR CONCEPTO IN
(agua, saneamto, tratamto, otros, multa, recargos)
)AS unpvt;
SELECT cuenta, agua, saneamto, tratamto, otros, multa, recargos
FROM dbo.FACTURA
WHERE cuenta > 0
-- Vertical
SELECT CUENTA, CONCEPTO, IMPORTE
FROM
(SELECT cuenta, agua, saneamto, tratamto, otros, multa, recargos
FROM dbo.FACTURA
WHERE cuenta > 0) p
UNPIVOT
(IMPORTE FOR CONCEPTO IN
(agua, saneamto, tratamto, otros, multa, recargos)
)AS unpvt;
Ejemplos de PIVOT
-- Agrupado por Meses
SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
-- Meses de Forma Horizontal
SELECT 'TOTALES' AS MES, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
[7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
FROM (
SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
) V PIVOT ( SUM(TOTAL) FOR MES IN ([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12]) ) as PT
-- Totales por Tarifa
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
-- Por tarifa Horizontal
SELECT 'TOTALES' AS TARIFA, [1] DOMESTICO, ISNULL([2],0) URBANO, ISNULL([3],0) RESIDENCIAL, [4] HOTELERA, [5] COMERCIAL, [6] INDUSTRIAL,
[7] SERVS
FROM (
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
) V PIVOT ( SUM(TOTAL) FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7]) ) as PT
SELECT 'TOTALES' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa) AS SourceTable
PIVOT
(
SUM(TOTAL)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
SELECT 'TARIFAS' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tipousuario AS TARIFA
,descripcion AS DESCRIPCION
FROM Padron.Cat_TiposUsuario
WHERE inactivo = 0) AS SourceTable
PIVOT
(MAX(DESCRIPCION)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
-- Meses de Forma Horizontal
SELECT 'TOTALES' AS MES, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
[7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
FROM (
SELECT MONTH(fecha) AS MES
,SUM(f_total) AS TOTAL
FROM Facturacion.Opr_Facturas
WHERE YEAR(fecha) = 2013
GROUP BY MONTH(fecha)
) V PIVOT ( SUM(TOTAL) FOR MES IN ([1], [2], [3], [4], [5],
[6], [7], [8], [9], [10], [11], [12]) ) as PT
-- Totales por Tarifa
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
-- Por tarifa Horizontal
SELECT 'TOTALES' AS TARIFA, [1] DOMESTICO, ISNULL([2],0) URBANO, ISNULL([3],0) RESIDENCIAL, [4] HOTELERA, [5] COMERCIAL, [6] INDUSTRIAL,
[7] SERVS
FROM (
SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa
) V PIVOT ( SUM(TOTAL) FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7]) ) as PT
SELECT 'TOTALES' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tarifa AS TARIFA
,COUNT(id_tarifa) AS TOTAL
FROM Padron.Cat_Padron
WHERE id_cuenta > 0
AND id_estatus = 1
GROUP BY id_tarifa) AS SourceTable
PIVOT
(
SUM(TOTAL)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
SELECT 'TARIFAS' AS TARIFA,
[1], [2], [3], [4], [5], [6], [7]
FROM
( SELECT id_tipousuario AS TARIFA
,descripcion AS DESCRIPCION
FROM Padron.Cat_TiposUsuario
WHERE inactivo = 0) AS SourceTable
PIVOT
(MAX(DESCRIPCION)
FOR TARIFA IN ([1], [2], [3], [4], [5], [6], [7])
) AS PivotTable;
jueves, 11 de abril de 2013
Imprimir desde LPT1 a USB
Se deberá agregar la impresora USB. Después ejecutar el comando siguiente como Administrador:
NET USE LPT1: \\PC\Impresora /persistent:yes
Donde PC es la maquina donde agregamos la Impresora e Impresora es el nombre de la impresora agregada, el parámetro /persistent:yes sirve para que quede registrador el recurso . Para probarlo podemos ejecutar un dir y redirigirlo al puerto LPT1:
dir > PRN
Para eliminar el recurso podemos ejecutar el siguiente comando:
NET USE LPT1 /delete
NET USE LPT1: \\PC\Impresora /persistent:yes
Donde PC es la maquina donde agregamos la Impresora e Impresora es el nombre de la impresora agregada, el parámetro /persistent:yes sirve para que quede registrador el recurso . Para probarlo podemos ejecutar un dir y redirigirlo al puerto LPT1:
dir > PRN
Para eliminar el recurso podemos ejecutar el siguiente comando:
NET USE LPT1 /delete
Suscribirse a:
Entradas (Atom)