====== SQL BULK ======

Formato par Bulk:
  * [[https://social.msdn.microsoft.com/Forums/es-ES/ec07a539-3abf-484b-b143-6e76e8c17a76/como-evitar-comillas-dobles-en-texto-al-usar-bulk-insert?forum=sqlserveres]]

ubigeo inei

  * [[https://itsalljustelectrons.blogspot.com/2016/01/Openrowset-Bulk-Rowset-Provider.html]]
  * [[https://social.msdn.microsoft.com/Forums/es-ES/ec07a539-3abf-484b-b143-6e76e8c17a76/como-evitar-comillas-dobles-en-texto-al-usar-bulk-insert]]
  * [[https://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/]]
  * [[https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server?view=sql-server-ver15]]


<code sql>

CREATE TABLE #UbigeoIneiMigra(
	cod_dep_inei  VARCHAR(1000),
	desc_dep_inei VARCHAR(1000),
	cod_prov_inei VARCHAR(1000),
	desc_prov_inei VARCHAR(1000),
	cod_ubigeo_inei VARCHAR(1000),
	desc_ubigeo_inei VARCHAR(1000),
	cod_dep_reniec VARCHAR(1000),
	desc_dep_reniec VARCHAR(1000),
	cod_prov_reniec VARCHAR(1000),
	desc_prov_reniec VARCHAR(1000),
	cod_ubigeo_reniec VARCHAR(1000),
	desc_ubigeo_reniec VARCHAR(1000),
	cod_dep_sunat VARCHAR(1000),
	desc_dep_sunat VARCHAR(1000),
	cod_prov_sunat VARCHAR(1000),
	desc_prov_sunat VARCHAR(1000),
	cod_ubigeo_sunat VARCHAR(1000),
	desc_ubigeo_sunat VARCHAR(1000)
)

BULK INSERT #UbigeoIneiMigra
FROM 'C:\equivalencia-ubigeos-oti-concytec.csv'
WITH ( FIELDTERMINATOR =',', CODEPAGE = 'ACP', rowterminator = '0x0a',FIRSTROW = 2 )

-- Limpio comillas
SELECT * FROM #UbigeoIneiMigra
--UPDATE #UbigeoIneiMigra
	SET cod_dep_inei = REPLACE(cod_dep_inei,'"',''),
		desc_dep_inei = REPLACE(desc_dep_inei,'"',''),
		cod_prov_inei = REPLACE(cod_prov_inei,'"',''),
		desc_prov_inei = REPLACE(desc_prov_inei,'"',''),
		cod_ubigeo_inei = REPLACE(cod_ubigeo_inei,'"',''),
		desc_ubigeo_inei = REPLACE(desc_ubigeo_inei,'"',''),
		cod_dep_reniec = REPLACE(cod_dep_reniec,'"',''),
		desc_dep_reniec = REPLACE(desc_dep_reniec,'"',''),
		cod_prov_reniec = REPLACE(cod_prov_reniec,'"',''),
		desc_prov_reniec = REPLACE(desc_prov_reniec,'"',''),
		cod_ubigeo_reniec = REPLACE(cod_ubigeo_reniec,'"',''),
		desc_ubigeo_reniec = REPLACE(desc_ubigeo_reniec,'"',''),
		cod_dep_sunat = REPLACE(cod_dep_sunat,'"',''),
		desc_dep_sunat = REPLACE(desc_dep_sunat,'"',''),
		cod_prov_sunat = REPLACE(cod_prov_sunat,'"',''),
		desc_prov_sunat = REPLACE(desc_prov_sunat,'"',''),
		cod_ubigeo_sunat = REPLACE(cod_ubigeo_sunat,'"',''),
		desc_ubigeo_sunat = REPLACE(desc_ubigeo_sunat,'"','')


-- Maximo de columna
SELECT
		MAX(LEN(cod_dep_inei)),
		MAX(LEN(desc_dep_inei)),
		MAX(LEN(cod_prov_inei)),
		MAX(LEN(desc_prov_inei)),
		MAX(LEN(cod_ubigeo_inei)),
		MAX(LEN(desc_ubigeo_inei)),
		MAX(LEN(cod_dep_reniec)),
		MAX(LEN(desc_dep_reniec)),
		MAX(LEN(cod_prov_reniec)),
		MAX(LEN(desc_prov_reniec)),
		MAX(LEN(cod_ubigeo_reniec)),
		MAX(LEN(desc_ubigeo_reniec)),
		MAX(LEN(cod_dep_sunat)),
		MAX(LEN(desc_dep_sunat)),
		MAX(LEN(cod_prov_sunat)),
		MAX(LEN(desc_prov_sunat)),
		MAX(LEN(cod_ubigeo_sunat)),
		MAX(LEN(desc_ubigeo_sunat))
FROM #UbigeoIneiMigra

-- segun su maximo de length modifico la pagina.
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_dep_inei  VARCHAR(2)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_dep_inei VARCHAR(13)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_prov_inei VARCHAR(4)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_prov_inei VARCHAR(25)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_ubigeo_inei VARCHAR(6)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_ubigeo_inei VARCHAR(36)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_dep_reniec VARCHAR(2)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_dep_reniec VARCHAR(23)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_prov_reniec VARCHAR(4)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_prov_reniec VARCHAR(25)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_ubigeo_reniec VARCHAR(6)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_ubigeo_reniec VARCHAR(30)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_dep_sunat VARCHAR(2)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_dep_sunat VARCHAR(23)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_prov_sunat VARCHAR(4)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_prov_sunat VARCHAR(25)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN cod_ubigeo_sunat VARCHAR(6)
ALTER TABLE #UbigeoIneiMigra ALTER COLUMN desc_ubigeo_sunat VARCHAR(36)



-- Si es que se quiere crear( ojo con las comillas al insert desde csv)
CREATE TABLE #UbigeoIneiMigra(
	cod_dep_inei  VARCHAR(2),
	desc_dep_inei VARCHAR(13),
	cod_prov_inei VARCHAR(4),
	desc_prov_inei VARCHAR(25),
	cod_ubigeo_inei VARCHAR(6),
	desc_ubigeo_inei VARCHAR(36),
	cod_dep_reniec VARCHAR(2),
	desc_dep_reniec VARCHAR(23),
	cod_prov_reniec VARCHAR(4),
	desc_prov_reniec VARCHAR(25),
	cod_ubigeo_reniec VARCHAR(6),
	desc_ubigeo_reniec VARCHAR(30),
	cod_dep_sunat VARCHAR(2),
	desc_dep_sunat VARCHAR(23),
	cod_prov_sunat VARCHAR(4),
	desc_prov_sunat VARCHAR(25),
	cod_ubigeo_sunat VARCHAR(6),
	desc_ubigeo_sunat VARCHAR(36)
)



</code>

Si no se define correctamente el CODEPAGE aparecerán errores 
<code>
LA UNIÊN - LA UNIÓN
FERREÐAFE - FERREÑAFE
P+UCAR DEL SARA SARA 
</code>

===== Creando la tablas =====

Tomado como ejemplo de ubigeos que hice en postgresql

<code sql>


--#### UBIGEOS DEPARTAMENTOS
CREATE TABLE ubigeos_dep(
	cod_dep_inei CHAR(2) PRIMARY KEY,
	desc_dep_inei VARCHAR(13)
);
--INSERT INTO ubigeos_dep( cod_dep_inei, desc_dep_inei )
SELECT RIGHT('00'+cod_dep_inei,2) AS cod_dep_inei, desc_dep_inei FROM #UbigeoIneiMigra
WHERE desc_dep_inei <> 'NA'
GROUP BY cod_dep_inei, desc_dep_inei
ORDER BY cod_dep_inei ASC


--#### UBIGEOS PROVINCIAS
CREATE TABLE ubigeos_prov(
	cod_prov_inei CHAR(4) PRIMARY KEY,
	cod_dep_inei CHAR(2),
	desc_prov_inei VARCHAR(25)
);
--INSERT INTO ubigeos_prov(cod_prov_inei,cod_dep_inei,desc_prov_inei)
SELECT 
	RIGHT('0000'+cod_prov_inei,4) AS cod_prov_inei,
	RIGHT('00'+cod_dep_inei,2) AS cod_dep_inei,
	desc_prov_inei
FROM #UbigeoIneiMigra
WHERE desc_prov_inei <> 'NA'
GROUP BY cod_prov_inei,
		 cod_dep_inei,
		 desc_prov_inei
ORDER BY cod_prov_inei ASC


--#### UBIGEOS DISTRITOS
CREATE TABLE ubigeos_dist(
	cod_ubigeo_inei CHAR(6) PRIMARY KEY,
	cod_prov_inei CHAR(4),
	cod_dep_inei CHAR(2),
	desc_ubigeo_inei VARCHAR(36)
);
INSERT INTO ubigeos_dist(
	cod_ubigeo_inei,
	cod_prov_inei,
	cod_dep_inei,
	desc_ubigeo_inei
)
SELECT 
	RIGHT('000000'+cod_ubigeo_inei,6) AS cod_ubigeo_inei,
	RIGHT('0000'+cod_prov_inei,4) AS cod_prov_inei,
	RIGHT('00'+cod_dep_inei,2) AS cod_dep_inei, 
	desc_ubigeo_inei
FROM #UbigeoIneiMigra
WHERE desc_ubigeo_inei <> 'NA'
ORDER BY cod_ubigeo_inei ASC


--## UBIGEOS VISTA
CREATE VIEW ubigeos AS

SELECT	dis.cod_dep_inei,
	dep.desc_dep_inei,
	dis.cod_prov_inei,
	pro.desc_prov_inei,
	dis.cod_ubigeo_inei,
	dis.desc_ubigeo_inei
FROM ubigeos_dist AS dis
LEFT JOIN ubigeos_dep AS dep
	ON dep.cod_dep_inei = dis.cod_dep_inei
LEFT JOIN ubigeos_prov AS pro
	ON pro.cod_prov_inei = dis.cod_prov_inei


</code>

correción de Acentos del csv
<code sql>
SELECT * FROM ubigeos_prov WHERE ( desc_prov_inei LIKE '%À%' OR desc_prov_inei LIKE '%Á%' ) OR
				( desc_prov_inei LIKE '%È%' OR desc_prov_inei LIKE '%É%' ) OR
				( desc_prov_inei LIKE '%Ì%' OR desc_prov_inei LIKE '%Í%' ) OR
				( desc_prov_inei LIKE '%Ò%' OR desc_prov_inei LIKE '%Ó%' ) OR
				( desc_prov_inei LIKE '%Ù%' OR desc_prov_inei LIKE '%Ú%' )


SELECT * FROM ubigeos_prov
-- UPDATE ubigeos_prov SET desc_prov_inei = 'PÁUCAR DEL SARA SARA'
WHERE desc_prov_inei = 'PÀUCAR DEL SARA SARA'


SELECT * FROM ubigeos_prov
-- UPDATE ubigeos_prov SET desc_prov_inei = 'LA UNIÓN'
WHERE desc_prov_inei = 'LA UNIÒN'
</code>

Busqueda con acentos insensible
<code sql>

SELECT * FROM ubigeos_prov
WHERE desc_prov_inei COLLATE Latin1_general_CI_AI LIKE '%PAUCAR%' COLLATE Latin1_general_CI_AI

-- EL collate puede ir en cualquiera COLLATE Latin1_general_CI_AI

--ALTER TABLE ubigeos_prov ALTER COLUMN desc_prov_inei VARCHAR(25) COLLATE Modern_Spanish_CI_AI

</code>