WIKI de apuntes de Billy Rondón - informatica:base_de_datos:sql_server
http://wiki.billyrondon.com/
2026-06-04T19:23:54+00:00WIKI de apuntes de Billy Rondón
http://wiki.billyrondon.com/
http://wiki.billyrondon.com/lib/exe/fetch.php/wiki:dokuwiki.svgtext/html2025-04-07T23:26:24+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_agrupacion_rank_avanzado
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_agrupacion_rank_avanzado?rev=1744068384&do=diff
Agrupacion Sacando otro dato
-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME,
player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT …text/html2025-04-08T00:43:06+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_bulk
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_bulk?rev=1744072986&do=diff
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-ser…text/html2025-04-07T23:19:48+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_datos_geoespaciales
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_datos_geoespaciales?rev=1744067988&do=diff
Datos Espaciales
anotaciones:
* <https://www.compose.com/articles/geofile-pgadmin-4-and-the-geometry-viewer/>
* <https://dbeaver.com/product/version.xml>
* <https://github.com/dbeaver/dbeaver/releases/tag/6.0.2>
google y sql server 2008:
* <https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9645b2dc-bc64-46a6-9efc-4ed5fc91b235/integrate-sql-server-2008-spatial-with-google-maps?forum=sqlspatial>
MAPAS para report service:
* <http://blog.datainspirations.com/2010/08/31/do-it-…text/html2025-04-07T23:13:52+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_excel
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_excel?rev=1744067632&do=diff
SQL SERVER Excel
Configuraciones
-- Ver todos los Proveedores:
EXEC master.dbo.sp_MSset_oledb_prop
-- Otras configuraciones:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GOtext/html2025-04-07T23:11:57+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_indentity
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_indentity?rev=1744067517&do=diff
* <https://stackoverflow.com/questions/42648/sql-server-best-way-to-get-identity-of-inserted-row>
* @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
*text/html2025-04-07T23:16:25+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_output
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_output?rev=1744067785&do=diff
SQL MERGE y OUTPUT
CREATE TABLE #tmp
(id int identity(1,1) primary key,
someval int not null,
someguid uniqueidentifier not null default newid()
);
INSERT #tmp( someval )
OUTPUT inserted.id, inserted.someval, inserted.someguid
VALUES (4),(5),(6)text/html2025-04-07T23:07:02+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_pivot
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_pivot?rev=1744067222&do=diff
SQL SERVER PIVOT
excelente explicacion:
<https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/>
WITH Src AS
(
SELECT * FROM (VALUES
('1/10/2016', 'abc11', 'teamA', 'ID_1 ', 292, 3),
('1/11/2016', 'abc11', 'teamA', 'ID_1 ', 300, 0),
('1/10/2016', 'abc11', 'teamA', 'ID_10', 100, 0),
('1/11/2016', 'abc11', 'teamA', 'ID_10', 84, 0),
('1/10/2016', 'abc11', 'teamA', 'ID_11', 11, 0),
('1/11/2016', 'abc11', 'teamA', 'ID_11', 12…text/html2025-04-07T23:10:43+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_split
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_split?rev=1744067443&do=diff
SPLIT en SQL SERVER
No existe antes de SQL SERVER 2016 (a partir de nivel de compatibilidad 130), por eso se debe crear una función en SQL, estos dos tipos de split pueden ayudar, él que devuelve una tabla
<https://augustohumire.wordpress.com/2013/08/22/split-in-sql-server/>
CREATE FUNCTION bsl_Split
(
@cadena VARCHAR(MAX)
,@separator VARCHAR(MAX)
)
RETURNS @tabla TABLE
(
value VARCHAR(MAX)
)
BEGIN
DECLARE @individual varchar(max) = null
WHILE LEN(@cadena) > 0
BEGIN
…text/html2025-04-18T05:50:23+00:00Anonymous (anonymous@undisclosed.example.com)sql_server_xml
http://wiki.billyrondon.com/doku.php/informatica:base_de_datos:sql_server:sql_server_xml?rev=1744955423&do=diff
SQL SERVER 2012 XML
<https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server?view=sql-server-ver15>
Consultas al XML <https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column>
<https://www.red-gate.com/simple-talk/sql/database-administration/manipulating-xml-data-in-sql-server/>
<https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/>
<https://www.sqlshack.com/working-with-xml-data-in-sql-server/…