Otros Comandos Transact SQL 2014.pdf

Otros Comandos

Este documento se proporciona "tal cual". La información y los puntos de vista expresados en este documento, incluyendo las referencias a sitios web deInternet y direcciones URL, está sujeta a cambios sin aviso. Este documento no implica ningún derecho legal respecto a ninguna propiedad intelectual de ningún

nombre de producto o producto de Microsoft. Puede copiar y utilizar este documento con fines internos y de referencia. Se permite que modifique este

documento para sus fines internos y de referencia. © 2013 Microsoft. Reservados todos los derechos. Términos de uso(http://msdn.microsoft.com/cc300389.aspx) | Marcas comerciales (http://www.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspx)

http://msdn.microsoft.com/

Table Of ContentsOtros Comandos

BULK INSERT (Transact-SQL)

MERGE (Transact-SQL)

OUTPUT ﴾cláusula de Transact‐SQL﴿READTEXT (Transact-SQL)

Condiciones de búsqueda ﴾Transact‐SQL﴿Constructor con valores de tabla (Transact-SQL)

TOP (Transact-SQL)

UPDATETEXT (Transact-SQL)

WITH common_table_expression (Transact-SQL)

WRITETEXT (Transact-SQL)

http://msdn.microsoft.com/es-es/library/ms188365.aspx

http://msdn.microsoft.com/es-es/library/bb510625.aspx




http://msdn.microsoft.com/es-es/library/dd776382.aspx





Otros Comandos

BULK INSERT (Transact-SQL)

Importa un archivo de datos en una tabla o vista de base de datos con un formato especificado por el usuario en SQL Server

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual).

Convenciones de sintaxis de Transact-SQL (Transact-SQL)

Sintaxis

Argumentos

database_name

Es el nombre de la base de datos en la que reside la tabla o vista especificada. Si no se especifica, es la base de datos actual.

schema_name

Es el nombre del esquema de la tabla o vista. schema_name es opcional si el esquema predeterminado para el usuario que realiza la operación de importaciónmasiva es el esquema de la tabla o vista especificada. Si no se especifica schema y el esquema predeterminado del usuario que realiza la operación deimportación masiva es diferente de la tabla o vista especificada, SQL Server devuelve un mensaje de error y se cancela la operación de importación masiva.

table_name

Es el nombre de la tabla o vista en la que se va a realizar una importación masiva de datos. Solo se pueden utilizar vistas en las que todas las columnas hagan

referencia a la misma tabla base. Para obtener más información acerca de las restricciones para la carga de datos en vistas, vea INSERT (Transact-SQL).

' data_file '

Es la ruta de acceso completa al archivo de datos que contiene los datos que se van a importar en la tabla o vista especificada. BULK INSERT puede importar

datos desde un disco ﴾incluidos una ubicación de red, disquete, disco duro, etc.﴿.

data_file debe especificar una ruta de acceso válida del servidor en el que se ejecuta SQL Server. Si data_file es un archivo remoto, especifique un nombre UNC

﴾Convención de nomenclatura universal﴿. Un nombre UNC tiene el formato \\Systemname\ShareName\Path\FileName. Por ejemplo,

\\SystemX\DiskZ\Sales\update.txt.

BATCHSIZE =batch_size

Especifica el número de filas de un lote. Cada lote se copia en el servidor como una transacción. Si no ocurre así, SQL Server confirma o revierte la transacciónde cada lote. De forma predeterminada, todos los datos del archivo de datos especificado componen un lote. Para obtener información acerca deconsideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

CHECK_CONSTRAINTS

Especifica que deben comprobarse todas las restricciones de la tabla o vista de destino durante la operación de importación masiva. Sin la opciónCHECK_CONSTRAINTS, se omiten las restricciones CHECK y FOREIGN KEY, y, después de la operación, la restricción sobre la tabla se marca como de noconfianza.

Nota

Las restricciones UNIQUE y PRIMARY KEY se aplican siempre. Cuando se importa en una columna de caracteres definida con la restricción NOT NULL, BULKINSERT inserta una cadena vacía cuando no hay valor en el archivo de texto.

En algún momento, debe examinar las restricciones de toda la tabla. Si la tabla no estaba vacía antes de la operación de importación masiva, el costo derevalidar la restricción puede superar del costo de aplicar restricciones CHECK a los datos incrementales.

SQL Server 2014

BULK INSERT

[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]

FROM 'data_file'

[ WITH

(

[ [ , ] BATCHSIZE = batch_size ]

[ [ , ] CHECK_CONSTRAINTS ]

[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]

[ [ , ] DATAFILETYPE =

{ 'char' | 'native'| 'widechar' | 'widenative' } ]

[ [ , ] FIELDTERMINATOR = 'field_terminator' ]

[ [ , ] FIRSTROW = first_row ]

[ [ , ] FIRE_TRIGGERS ]

[ [ , ] FORMATFILE = 'format_file_path' ]

[ [ , ] KEEPIDENTITY ]

[ [ , ] KEEPNULLS ]

[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]

[ [ , ] LASTROW = last_row ]

[ [ , ] MAXERRORS = max_errors ]

[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]

[ [ , ] ROWS_PER_BATCH = rows_per_batch ]

[ [ , ] ROWTERMINATOR = 'row_terminator' ]

[ [ , ] TABLOCK ]

[ [ , ] ERRORFILE = 'file_name' ]

)]

http://go.microsoft.com/fwlink/p/?LinkId=299658



Una situación en la que quizá desee que las restricciones estén deshabilitadas ﴾comportamiento predeterminado﴿ se produce cuando los datos de entradacontienen filas que infringen las restricciones. Con las restricciones CHECK deshabilitadas, puede importar los datos y utilizar después instrucciones Transact‐SQLpara quitar los datos no válidos.

Nota

La opción MAXERRORS no se aplica a la comprobación de restricciones.

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

Especifica la página de códigos de los datos incluidos en el archivo de datos. CODEPAGE solo es pertinente si los datos contienen columnas de tipo char,

varchar o text con valores de caracteres mayores que 127 o menores que 32.

Nota

Microsoft recomienda especificar un nombre de intercalación para cada columna de un archivo de formato.

Valor de CODEPAGE Descripción

ACP Las columnas con el tipo de datos char, varchar o text se convierten de la página de códigos ANSI/Microsoft Windows ﴾ISO 1252﴿ ala página de códigos de SQL Server.

OEM (valor

predeterminado)

Las columnas con los tipos de datos char, varchar o text se convierten de la página de códigos OEM del sistema a la página decódigos de SQL Server.

RAW No se realiza ninguna conversión de una página de códigos a otra; se trata de la opción más rápida.

code_page Número específico de una página de códigos; por ejemplo, 850.

Importante

SQL Server no admite la página de códigos 65001 ﴾codificación UTF‐8﴿.

DATAFILETYPE = { 'char' | 'native' | 'widechar' | 'widenative' }

Especifica que BULK INSERT realiza la operación de importación con el valor de tipo de archivo de datos especificado.

Valor de

DATAFILETYPETodos los datos representados en:

char (valor

predeterminado)

Formato de caracteres.

Para obtener más información, vea Usar el formato de caracteres para importar o exportar datos (SQL Server).

native Tipos de datos nativos (base de datos). Cree el archivo de datos nativos mediante la importación masiva de datos desde SQL Server conla utilidad bcp.

El valor native ofrece una alternativa de mayor rendimiento al valor char.

Para obtener más información, vea Usar el formato nativo para importar o exportar datos (SQL Server).

widechar Caracteres Unicode.

Para obtener más información, vea Usar el formato de caracteres Unicode para importar o exportar datos (SQL Server).

widenative Tipos de datos nativos (base de datos), salvo en las columnas char, varchar y text en las que los datos se almacenan como datos

Unicode. Cree el archivo de datos de widenative mediante la importación masiva de datos desde SQL Server con la utilidad bcp.

El valor widenative ofrece una alternativa de mayor rendimiento a widechar. Si el archivo de datos contiene caracteres extendidos ANSI,

especifique widenative.

Para obtener más información, vea Usar el formato nativo Unicode para importar o exportar datos (SQL Server).

FIELDTERMINATOR ='field_terminator'

Especifica el terminador de campo que se va a utilizar para archivos de datos de tipo char y widechar. El terminador de campo predeterminado es \t

(tabulador). Para obtener más información, vea Especificar terminadores de campo y de fila (SQL Server).

FIRSTROW =first_row

Especifica el número de la primera fila que se va a cargar. El valor predeterminado es la primera fila del archivo de datos especificado. FIRSTROW comienza en

1.

Nota

El atributo FIRSTROW no está pensado para saltar los encabezados de columna. La instrucción BULK INSERT no permite omitir los encabezados. Al omitir

filas, Motor de base de datos de SQL Server solo analiza los terminadores de campo y no valida los datos en los campos de las filas omitidas.

FIRE_TRIGGERS

Especifica que se ejecutarán todos los desencadenadores de inserción definidos en la tabla de destino durante la operación de importación masiva. Si se

definen desencadenadores para operaciones INSERT en la tabla de destino, se activan para cada lote completado.

Si no se especifica FIRE_TRIGGERS, no se ejecuta ningún desencadenador de inserción.

FORMATFILE ='format_file_path'

Especifica la ruta de acceso completa de un archivo de formato. Un archivo de formato describe el archivo de datos que contiene respuestas almacenadas

creado con la utilidad bcp en la misma tabla o vista. Se debe usar el archivo de formato si:







El archivo de datos contiene un número de columnas mayor o menor que la tabla o vista.

Las columnas están en un orden diferente.

Los delimitadores de columna varían.

Hay otros cambios en el formato de los datos. Los archivos de formato se suelen crear con la utilidad bcp y se modifican con un procesador de texto si

es necesario. Para obtener más información, vea bcp (utilidad).

KEEPIDENTITY

Especifica que se utilizará el valor o valores de identidad del archivo de datos importado para la columna de identidad. Si no se especifica KEEPIDENTITY, los

valores de identidad de esta columna se comprueban pero no se importan y SQL Server asigna automáticamente valores únicos basados en los valores deinicialización y de incremento especificados durante la creación de la tabla. Si el archivo de datos no contiene valores para la columna de identidad de la tabla o

vista, utilice un archivo de formato para especificar que se debe omitir la columna de identidad de la tabla o vista cuando se importen los datos; SQL Server

asigna automáticamente valores únicos para la columna. Para obtener más información, vea DBCC CHECKIDENT (Transact-SQL).

Para obtener más información acerca de cómo mantener los valores de identidad, vea Mantener valores de identidad al importar datos de forma masiva (SQL

Server).

KEEPNULLS

Especifica que las columnas vacías deben conservar un valor NULL durante la operación de importación masiva, en lugar de tener valores predeterminados paralas columnas insertadas. Para obtener más información, vea Mantener valores NULL o usar valores predeterminados durante la importación masiva ﴾SQL Server﴿.

KILOBYTES_PER_BATCH = kilobytes_per_batch

Especifica el número aproximado de kilobytes ﴾KB﴿ de datos por lote como kilobytes_per_batch. De forma predeterminada, el valor de KILOBYTES_PER_BATCH es

desconocido. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

LASTROW=last_row

Especifica el número de la última fila que se va a cargar. El valor predeterminado es 0, que indica la última fila del archivo de datos especificado.

MAXERRORS = max_errors

Especifica el número máximo de errores de sintaxis permitidos en los datos antes de cancelar la operación de importación masiva. Cada fila que no se puede

importar con la operación de importación masiva se omite y se considera un error. Si no se especifica max_errors, el valor predeterminado es 10.

Nota

La opción MAX_ERRORS no se aplica para comprobar restricciones ni para convertir tipos de datos money y bigint.

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Especifica la forma en que están ordenados los datos del archivo de datos. El rendimiento de la importación masiva mejora si los datos importados se ordenansegún el índice clúster de la tabla, si lo hay. Si el archivo de datos se ordena siguiendo otro criterio que no sea el orden de una clave de índice clúster, o si nohay ningún índice clúster en la tabla, la cláusula ORDER se pasa por alto. Los nombres de columna facilitados deben ser nombres válidos en la tabla de destino.De forma predeterminada, la operación de inserción masiva presupone que los datos del archivo no están ordenados. Para optimizar las operaciones de

importación masiva, SQL Server también se valida que los datos importados estén ordenados.

n

Es un marcador de posición que indica que se pueden especificar varias columnas.

ROWS_PER_BATCH =rows_per_batch

Indica el número aproximado de filas de datos del archivo de datos.

De forma predeterminada, todos los datos del archivo de datos se envían al servidor en una sola transacción y el optimizador de consultas desconoce elnúmero de filas del lote. Si especifica ROWS_PER_BATCH ﴾con el valor > 0﴿ el servidor utiliza este valor para optimizar la operación de importación masiva. El

valor especificado para ROWS_PER_BATCH debe ser aproximadamente el mismo que el número real de filas. Para obtener información acerca deconsideraciones de rendimiento, vea la sección "Comentarios" más adelante en este tema.

ROWTERMINATOR ='row_terminator'

Especifica el terminador de fila que se va a utilizar para archivos de datos de tipo char y widechar. El terminador de fila predeterminado es \r\n ﴾carácter denueva línea﴿. Para obtener más información, vea Especificar terminadores de campo y de fila (SQL Server).

TABLOCK

Especifica que se obtiene un bloqueo de tabla durante la operación de importación masiva. Varios clientes pueden cargar una tabla simultáneamente si ésta notiene índices y se especifica TABLOCK. De forma predeterminada, el comportamiento del bloqueo viene determinado por la opción de tabla table lock on bulk

load. Al mantener un bloqueo durante la operación de importación masiva, se reduce la contención por bloqueos de la tabla y en algunos casos puedemejorarse notablemente el rendimiento. Para obtener información acerca de consideraciones de rendimiento, vea la sección "Comentarios" más adelante en estetema.

ERRORFILE ='file_name'

Especifica el archivo utilizado para recopilar filas que tienen errores de formato y no pueden convertirse en un conjunto de filas OLE DB. Estas filas se copian en

este archivo de errores desde el archivo de datos "tal cual".

El archivo de errores se crea cuando se ejecuta el comando. Se produce un error si el archivo ya existe. Además, se crea un archivo de control con la extensión.ERROR.txt. Este archivo hace referencia a cada fila del archivo de errores y proporciona diagnósticos de errores. Tan pronto como se corrigen los errores, se

pueden cargar los datos.

Compatibilidad

BULK INSERT aplica una estricta validación y comprobación de los datos leídos de un archivo que pueden dar lugar a errores en los scripts existentes cuando seejecutan en datos no válidos. Por ejemplo, BULK INSERT comprueba que:

Las representaciones nativas de los tipos de datos float o real son válidas.






Los datos Unicode tienen una longitud de bytes uniforme.

Tipos de datos

Conversiones de tipos de datos de cadena a decimal

Las conversiones de tipos de datos de cadena a decimal utilizadas en BULK INSERT siguen las mismas reglas que la función CONVERT de Transact-SQL, que rechaza las

cadenas que representan valores numéricos con notación científica. Por lo tanto, BULK INSERT trata esas cadenas como valores no válidos y genera errores deconversión.

Para solucionar este comportamiento, use un archivo de formato para la importación masiva de datos de tipo float con notación científica en una columna con valoresdecimales. En el archivo de formato, describa explícitamente la columna como de datos real o float. Para obtener más información acerca de estos tipos de datos, veafloat y real (Transact-SQL).

Nota

Los archivos de formato representan los datos real como el tipo de datos SQLFLT4 y los datos float como el tipo de datos SQLFLT8. Para obtener informaciónacerca de los archivos de formato no XML, vea Especificar el tipo de almacenamiento en archivo mediante bcp (SQL Server).

Ejemplo de importación de un valor numérico que utiliza notación científicaEn este ejemplo se utiliza la siguiente tabla:

El usuario desea importar masivamente datos en la tabla t_float. El archivo de datos (C:\t_float-c.dat) contiene datos float con notación científica; por ejemplo:

No obstante, BULK INSERT no puede importar estos datos directamente en t_float, ya que su segunda columna, c2, utiliza el tipo de datos decimal. Por lo tanto, es

necesario un archivo de formato. El archivo de formato debe asignar los datos float con notación científica al formato decimal de la columna c2.

El siguiente archivo de formato utiliza el tipo de datos SQLFLT8 para asignar el segundo campo de datos a la segunda columna:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

Para utilizar este archivo de formato (con el nombre de archivo C:\t_floatformat-c-xml.xml) para importar los datos de prueba en la tabla de prueba, emita la

siguiente instrucción Transact‐SQL:

Tipos de datos para importar o exportar masivamente documentos SQLXML

Para importar o exportar de forma masiva datos SQLXML, utilice uno de los tipos de datos siguientes en el archivo de formato:

Tipo de datos Efecto

SQLCHAR o

SQLVARCHAR

Los datos se envían en la página de códigos del cliente o en la página de códigos implícita en la intercalación﴿. El efecto es el mismo que si se

especifica DATAFILETYPE ='char' sin especificar un archivo de formato.

SQLNCHAR o

SQLNVARCHAR

Los datos se envían como Unicode. El efecto es el mismo que si se especifica DATAFILETYPE = 'widechar' sin especificar un archivo de

formato.

SQLBINARY o

SQLVARBIN

Los datos se envían sin realizar ninguna conversión.

Comentarios generales

CREATE TABLE t_float(c1 float, c2 decimal (5,4));

8.0000000000000002E-28.0000000000000002E-2

BULK INSERT bulktest..t_float

FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');

GO





Para obtener una comparación de la instrucción BULK INSERT, la instrucción INSERT ... SELECT * FROM OPENROWSET(BULK...) y el comando bcp, vea Importar y

exportar datos de forma masiva (SQL Server).

Para obtener información sobre cómo preparar los datos para la importación masiva, vea Preparar los datos para exportar o importar de forma masiva (SQL Server).

La instrucción BULK INSERT se puede ejecutar en una transacción definida por el usuario para importar datos en una tabla o una vista. Opcionalmente, para utilizar

varias coincidencias para la importación masiva de datos, una transacción puede especificar la cláusula BATCHSIZE en la instrucción BULK INSERT. Si una transacción devarios lotes se revierte, cada lote que la transacción ha enviado a SQL Server se revierte.

Interoperabilidad

Importar datos desde un archivo CSV

Las operaciones de importación masiva de SQL Server no admiten los archivos de valores separados por comas ﴾CSV﴿. Sin embargo, en algunos casos se puede utilizar

un archivo de valores separados por comas ﴾CSV﴿ como archivo de datos para una importación masiva de datos en SQL Server. Para obtener información acerca delos requisitos para importar datos de un archivo de datos CSV, vea Preparar los datos para exportar o importar de forma masiva (SQL Server).

Comportamiento del registro

Para obtener información acerca de cuándo se registran en el registro de transacciones las operaciones de inserción de filas que se efectúan durante la importaciónmasiva, vea Requisitos previos para el registro mínimo durante la importación masiva.

Restricciones

Cuando se usa un archivo de formato con BULK INSERT, solo se puede especificar un máximo de 1024 campos. Es el mismo número máximo de columnas permitido enuna tabla. Si usa BULK INSERT con un archivo de datos que contenga más de 1024 campos, BULK INSERT genera el error 4822. La utilidad bcp no tiene esta limitación,por lo que para los archivos de datos que contengan más de 1024 campos use el comando bcp.

Consideraciones de rendimiento

Si el número de páginas que van a vaciarse en un único lote supera un umbral interno, podría producirse un examen completo del grupo de búferes para identificarqué páginas se han de vaciar cuando el lote se confirme. Este examen completo puede afectar de forma desfavorable al rendimiento de la importación masiva. Un caso

en el que es probable que se supere el umbral interno se produce cuando un grupo de búferes grande se combina con un subsistema de E/S lento. Para evitar los

desbordamientos del búfer en equipos grandes, no utilice la sugerencia TABLOCK ﴾que quita la optimización masiva﴿ o use un tamaño de lote menor ﴾que la preserva﴿.

Dado que los equipos varían, es recomendable que pruebe varios tamaños de lote con la carga de datos para averiguar lo que funciona mejor en su caso.

Seguridad

Delegación de cuentas de seguridad ﴾suplantación﴿Si un usuario utiliza un inicio de sesión de SQL Server, se utilizará el perfil de seguridad de la cuenta de proceso de SQL Server. Un inicio de sesión mediante laautenticación de SQL Server no puede autenticarse fuera del motor de base de datos. Por tanto, cuando un inicio de sesión mediante la autenticación de SQL Serverinicia un comando BULK INSERT, la conexión con los datos se realiza utilizando el contexto de seguridad de la cuenta de proceso de SQL Server ﴾la cuenta que utiliza elservicio del motor de base de datos de SQL Server). Para leer correctamente los datos de origen, debe conceder acceso a los datos de origen a la cuenta utilizada por

el motor de base de datos de SQL Server. En cambio, si un usuario de SQL Server inicia sesión con la autenticación de Windows, el usuario solo puede leer los archivosa los que tiene acceso la cuenta de usuario, independientemente del perfil de seguridad del proceso de SQL Server.

Si al ejecutar la instrucción BULK INSERT utiliza sqlcmd u osql desde un equipo e inserta datos en SQL Server en un segundo equipo y especifica data_file en un tercer

equipo con una ruta de acceso UNC, es posible que reciba el error 4861.

Para resolver este error, utilice la autenticación de SQL Server y especifique un inicio de sesión de SQL Server, que utiliza el perfil de seguridad de la cuenta del procesode SQL Server, o bien configure Windows para habilitar la delegación de la cuenta de seguridad. Para obtener información acerca de cómo habilitar una cuenta deusuario para que sea de confianza para la delegación, vea la Ayuda de Windows.

Para obtener más información acerca de esta y otras consideraciones de seguridad en el uso de BULK INSERT, vea Importar de forma masiva datos mediante BULK

INSERT u OPENROWSET(BULK...) (SQL Server).

Permisos

Se requieren los permisos INSERT y ADMINISTER BULK OPERATIONS. Además, es necesario el permiso ALTER TABLE si se da una o varias de las siguientescircunstancias:

Existen restricciones y no se ha especificado la opción CHECK_CONSTRAINTS.

Nota

El comportamiento predeterminado es deshabilitar las restricciones. Para comprobar las restricciones CHECK explícitamente, utilice la opciónCHECK_CONSTRAINTS.

Existen desencadenadores y no se ha especificado la opción FIRE_TRIGGER.

Nota







De manera predeterminada no se activan los desencadenadores. Para activar los desencadenadores explícitamente, use la opción FIRE_TRIGGER.

Se utiliza la opción KEEPIDENTITY para importar el valor de identidad de un archivo de datos.

Ejemplos

A.Usar canalizaciones para importar datos de un archivo

En el siguiente ejemplo se importa información detallada de pedidos en la tabla AdventureWorks2012.Sales.SalesOrderDetail desde un archivo de datos

especificado utilizando una canalización ﴾ | ) como terminador de campo y |\n como terminador de fila.

B.Usar el argumento FIRE_TRIGGERS

En el ejemplo siguiente se especifica el argumento FIRE_TRIGGERS.

C.Usar el salto de línea como terminador de filaEn el siguiente ejemplo se importa un archivo que utiliza el salto de línea como terminador de fila, igual que en una salida de UNIX:

Nota

Debido al modo en que Microsoft Windows trata los archivos de texto, (\n se reemplaza automáticamente por \r\n).

Otros ejemplos

Se proporcionan otros ejemplos de uso de BULK INSERT en los temas siguientes:

Ejemplos de importación y exportación de forma masiva documentos XML ﴾SQL Server﴿

Mantener valores de identidad al importar datos de forma masiva (SQL Server)

Mantener valores NULL o usar valores predeterminados durante la importación masiva ﴾SQL Server﴿

Especificar terminadores de campo y de fila (SQL Server)

Usar un archivo de formato para importar datos de forma masiva (SQL Server)

Usar el formato de caracteres para importar o exportar datos (SQL Server)

Usar el formato nativo para importar o exportar datos (SQL Server)

Usar el formato de caracteres Unicode para importar o exportar datos (SQL Server)

Usar el formato nativo Unicode para importar o exportar datos (SQL Server)

Usar un archivo de formato para omitir una columna de tabla (SQL Server)

Usar un archivo de formato para asignar columnas de tabla a campos de un archivo de datos (SQL Server)

Vea también

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail

FROM 'f:\orders\lineitem.tbl'

WITH

(

FIELDTERMINATOR =' |',

ROWTERMINATOR =' |\n'

);

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail

FROM 'f:\orders\lineitem.tbl'

WITH

(

FIELDTERMINATOR =' |',

ROWTERMINATOR = ':\n',

FIRE_TRIGGERS

);

DECLARE @bulk_cmd varchar(1000);

SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail

FROM ''<drive>:\<path>\<filename>''

WITH (ROWTERMINATOR = '''+CHAR(10)+''')';

EXEC(@bulk_cmd);












Referenciabcp (utilidad)

INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

sp_tableoption (Transact-SQL)

ConceptosImportar y exportar datos de forma masiva (SQL Server)

Archivos de formato para importar o exportar datos (SQL Server)

Preparar los datos para exportar o importar de forma masiva (SQL Server)

© 2014 Microsoft









Realiza operaciones de inserción, actualización o eliminación en una tabla de destino según los resultados de una combinación con una tabla de origen. Por ejemplo,

puede sincronizar dos tablas insertando, actualizando o eliminando las filas de una tabla según las diferencias que se encuentren en la otra.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

SQL Server 2014

[ WITH <common_table_expression> [,...n] ]

MERGE

[ TOP ( expression ) [ PERCENT ] ]

[ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

USING <table_source>

ON <merge_search_condition>

[ WHEN MATCHED [ AND <clause_search_condition> ]

THEN <merge_matched> ] [ ...n ]

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

THEN <merge_not_matched> ]

[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

THEN <merge_matched> ] [ ...n ]

[ <output_clause> ]

[ OPTION ( <query_hint> [ ,...n ] ) ]

;

<target_table> ::=

{

[ database_name . schema_name . | schema_name . ]

target_table

}

<merge_hint>::=

{

{ [ <table_hint_limited> [ ,...n ] ]

[ [ , ] INDEX ( index_val [ ,...n ] ) ] }

}

<table_source> ::=

{

table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]

[ WITH ( table_hint [ [ , ]...n ] ) ]

| rowset_function [ [ AS ] table_alias ]

[ ( bulk_column_alias [ ,...n ] ) ]

| user_defined_function [ [ AS ] table_alias ]

| OPENXML <openxml_clause>

| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

| <joined_table>

| <pivoted_table>

| <unpivoted_table>

}

<merge_search_condition> ::=

<search_condition>

<merge_matched>::=

{ UPDATE SET <set_clause> | DELETE }

<set_clause>::=

SET

{ column_name = { expression | DEFAULT | NULL }

| { udt_column_name.{ { property_name = expression

| field_name = expression }

| method_name ( argument [ ,...n ] ) }

}

| column_name { .WRITE ( expression , @Offset , @Length ) }

| @variable = expression

| @variable = column = expression

| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression

| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression

| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression

} [ ,...n ]

<merge_not_matched>::=

{

INSERT [ ( column_list ) ]

{ VALUES ( values_list )

| DEFAULT VALUES }




Argumentos

WITH <common_table_expression>

Especifica la vista o el conjunto de resultados temporal indicado, que también se conoce como expresión de tabla común, definido en el ámbito de la instrucciónMERGE. El conjunto de resultados se deriva de una consulta simple. La instrucción MERGE hace referencia al conjunto de resultados. Para obtener másinformación, vea WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]

Especifica el número o porcentaje de filas afectadas. expression puede ser un número o un porcentaje de filas. Las filas a las que se hace referencia en la

expresión TOP no están organizadas en ningún orden. Para obtener más información, vea TOP (Transact-SQL).

La cláusula TOP se aplica después de que se combinen toda la tabla de origen y toda la tabla de destino, y se quiten las filas combinadas que no reúnan lascondiciones para las acciones de inserción, actualización o eliminación. La cláusula TOP reduce aún más el número de filas combinadas al valor especificado y seaplican las acciones de inserción, actualización o eliminación a las filas combinadas restantes de una manera desordenada. Es decir, no hay ningún orden en elque las filas se distribuyan entre las acciones definidas en las cláusulas WHEN. Por ejemplo, cuando se especifica TOP (10) afecta a 10 filas; de estas filas, 7 se

pueden actualizar y 3 insertar, o se pueden eliminar 1, actualizar 5 e insertar 4, etc.

Dado que la instrucción MERGE realiza un recorrido completo de ambas tablas, de destino y de origen, el rendimiento de E/S puede verse afectado al utilizar lacláusula TOP para modificar una tabla grande mediante la creación de varios lotes. En este escenario, es importante asegurase de que todos los lotes sucesivos

tengan como destino nuevas filas.

database_name

Es el nombre de la base de datos donde se encuentra target_table.

schema_name

Es el nombre del esquema al que pertenece target_table.

target_table

Es la tabla o la vista con la que las filas de datos de <table_source> se hacen coincidir según la <clause_search_condition>. target_table es el destino de las

operaciones de inserción, actualización o eliminación que las cláusulas WHEN de la instrucción MERGE especifican.

Si target_table es una vista, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas. Para obtener más información, vea Modificar datos

mediante una vista.

target_table no puede ser una tabla remota. target_table no puede tener ninguna regla definida.

[ AS ] table_alias

Es un nombre alternativo que se utiliza para hacer referencia a una tabla.

USING <table_source>

Especifica el origen de datos que se hace coincidir con las filas de datos en target_table según <merge_search condition>. El resultado de esta coincidencia dicta

las acciones que tomarán las cláusulas WHEN de la instrucción MERGE. <table_source> puede ser una tabla remota o una tabla derivada que tengan acceso a

las tablas remotas.

<table_source> puede ser una tabla derivada que use el constructor con valores de tabla de Transact-SQL para construir una tabla especificando varias filas.

}

<clause_search_condition> ::=

<search_condition>

<search condition> ::=

{ [ NOT ] <predicate> | ( <search_condition> ) }

[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]

[ ,...n ]

<predicate> ::=

{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression

| string_expression [ NOT ] LIKE string_expression

[ ESCAPE 'escape_character' ]

| expression [ NOT ] BETWEEN expression AND expression

| expression IS [ NOT ] NULL

| CONTAINS

( { column | * } , '< contains_search_condition >' )

| FREETEXT ( { column | * } , 'freetext_string' )

| expression [ NOT ] IN ( subquery | expression [ ,...n ] )

| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }

{ ALL | SOME | ANY} ( subquery )

| EXISTS ( subquery ) }

<output_clause>::=

{

[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }

[ (column_list) ] ]

[ OUTPUT <dml_select_list> ]

}

<dml_select_list>::=

{ <column_name> | scalar_expression }

[ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=

{ DELETED | INSERTED | from_table_name } . { * | column_name }

| $action






Para obtener más información acerca de la sintaxis y los argumentos de esta cláusula, vea FROM (Transact-SQL).

ON <merge_search_condition>

Especifica las condiciones en las que <table_source> se combina con target_table para determinar dónde coinciden.

Advertencia

Es importante especificar solamente las columnas de la tabla de destino que se utilizan para los propósitos de la coincidencia. Es decir, especifique las

columnas de la tabla de destino que se comparan con la correspondiente columna de la tabla de origen. No intente mejorar el rendimiento de las consultas

filtrando las filas de la tabla de destino en la cláusula ON, según se especifica con AND NOT target_table.column_x = value. Si se hace esto, se pueden

devolver resultados inesperados e incorrectos.

WHEN MATCHED THEN <merge_matched>

Especifica que todas las filas de target_table que coinciden con las filas que devuelve <table_source> ON <merge_search_condition> y que satisfacen alguna

condición de búsqueda adicional se actualizan o eliminan según la cláusula <merge_matched>.

La instrucción MERGE puede tener a lo sumo dos cláusulas WHEN MATCHED. Si se especifican dos cláusulas, la primera debe ir acompañada de una cláusulaAND <search_condition>. Para una fila determinada, la segunda cláusula WHEN MATCHED se aplica solamente si no se aplica la primera. Si hay dos cláusulasWHEN MATCHED, una debe especificar una acción UPDATE y la otra una acción DELETE. Si se especifica UPDATE en la cláusula <merge_matched> y más de unafila de <table_source> coincide con una fila en target_table según la <merge_search_condition>, SQL Server devuelve un error. La instrucción MERGE no puedeactualizar la misma fila más de una vez, ni actualizar o eliminar la misma fila.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

Especifica que una fila se inserta en target_table para cada fila que devuelve <table_source> ON <merge_search_condition> que no coincide con una fila de

target_table, pero satisface una condición de búsqueda adicional, si está presente. La cláusula <merge_not_matched> especifica los valores que insertar. La

instrucción MERGE puede tener solamente una cláusula WHEN NOT MATCHED.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

Especifica que todas las filas de target_table que no coinciden con las filas que devuelve <table_source> ON <merge_search_condition> y que satisfacen alguna

condición de búsqueda adicional se actualizan o eliminan según la cláusula <merge_matched>.

La instrucción MERGE puede tener a lo sumo dos cláusulas WHEN NOT MATCHED BY SOURCE. Si se especifican dos cláusulas, la primera debe ir acompañadade una cláusula AND <clause_search_condition>. Para una fila determinada, la segunda cláusula WHEN NOT MATCHED BY SOURCE se aplica solamente si no seaplica la primera. Si hay dos cláusulas WHEN NOT MATCHED BY SOURCE, una debe especificar una acción UPDATE y la otra una acción DELETE. Solamente se

puede hacer referencia a las columnas de la tabla de destino en <clause_search_condition>.

Cuando <table_source> no devuelve ninguna fila, no se puede tener acceso a las columnas de la tabla de origen. Si la acción de actualización o eliminaciónespecificada en la cláusula <merge_matched> hace referencia a las columnas de la tabla de origen, se devuelve el error 207 ﴾nombre de columna no válido﴿. La

cláusula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 puede hacer que la instrucción genere un error porqueCol1 en la tabla de origen es inaccesible.

AND <clause_search_condition>

Especifica cualquier condición de búsqueda válida. Para obtener más información, vea Condiciones de búsqueda ﴾Transact‐SQL﴿.

<table_hint_limited>

Especifica una o más sugerencias de tabla que se aplican en la tabla de destino para cada una de las acciones de inserción, actualización o eliminación querealiza la instrucción MERGE. La palabra clave WITH y los paréntesis son obligatorios.

No se permiten NOLOCK ni READUNCOMMITTED. Para obtener más información acerca de las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).

Especificar la sugerencia TABLOCK en una tabla que es el destino de una instrucción INSERT tiene el mismo efecto que especificar la sugerencia TABLOCKX. Se

realiza un bloqueo exclusivo en la tabla. Cuando se especifica FORCESEEK, se aplica a la instancia implícita de la tabla de destino combinada con la tabla deorigen.

Advertencia

Si se especifica READPAST con WHEN NOT MATCHED [ BY TARGET ] THEN INSERT, pueden producirse operaciones INSERT que infrinjan las restricciones

UNIQUE.

INDEX ( index_val [ ,...n ] )

Especifica el nombre o identificador de uno o más índices de la tabla de destino para realizar una combinación implícita con la tabla de origen. Para obtener

más información, vea Sugerencias de tabla (Transact-SQL).

<output_clause>

Devuelve una fila para cada fila de target_table que se actualiza, inserta o elimina, en ningún orden en concreto. $action se puede especificar en la cláusula desalida. $action es una columna de tipo nvarchar(10) que devuelve uno de estos tres valores por cada fila: 'INSERT', 'UPDATE' o 'DELETE', según la acciónrealizada en dicha fila. Para obtener más información acerca de los argumentos de esta cláusula, vea OUTPUT ﴾cláusula de Transact‐SQL﴿.

OPTION ( <query_hint> [ ,...n ] )

Especifica que se utilizan las sugerencias del optimizador para personalizar el modo en que el motor de base de datos procesa la instrucción. Para obtener másinformación, vea Sugerencias de consulta (Transact-SQL).

<merge_matched>

Especifica la acción de actualización o eliminación que se aplica a todas las filas de target_table que no coinciden con las filas que devuelve <table_source> ON

<merge_search_condition>, y que satisfacen cualquier condición de búsqueda adicional.

UPDATE SET <set_clause>

Especifica la lista de nombres de columna o de variable que se van a actualizar en la tabla de destino y los valores con los que se actualizan.

Para obtener más información acerca de los argumentos de esta cláusula, vea UPDATE (Transact-SQL). No se puede establecer una variable con el mismo

valor que una columna.

DELETE

Especifica que las filas coincidentes de las filas de target_table se eliminan.








<merge_not_matched>

Especifica los valores que insertar en la tabla de destino.

(column_list)

Es una lista de una o varias columnas de la tabla de destino en la que insertar los datos. Las columnas se deben especificar como un nombre de una sola

parte o, de lo contrario, se producirá un error en la instrucción MERGE. column_list se debe agregar entre paréntesis y delimitarse mediante comas.

VALUES ( values_list)

Es una lista separada por comas de constantes, variables o expresiones que devuelve los valores que se insertarán en la tabla de destino. Las expresiones

no pueden contener una instrucción EXECUTE.

DEFAULT VALUES

Hace que la fila insertada contenga los valores predeterminados definidos para cada columna.

Para obtener más información sobre esta cláusula, vea INSERT (Transact-SQL).

<search condition>

Especifica las condiciones de búsqueda utilizadas para especificar <merge_search_condition> o <clause_search_condition>. Para obtener más informaciónacerca de los argumentos de esta cláusula, vea Condiciones de búsqueda ﴾Transact‐SQL﴿.

Comentarios

Al menos se debe especificar una de las tres cláusulas MATCHED, pero se pueden especificar en cualquier orden. Una variable no puede actualizarse más de una vezen la misma cláusula MATCHED.

Cualquier acción de inserción, actualización o eliminación especificada en la tabla de destino por la instrucción MERGE está limitada por las restricciones definidas enella, incluidas las restricciones de integridad referencial en cascada. Si IGNORE_DUP_KEY se establece en ON para algún índice único de la tabla de destino, MERGEomite este valor.

La instrucción MERGE requiere un punto y coma ﴾;﴿ como terminador. Se genera el error 10713 cuando una instrucción MERGE se ejecuta sin el terminador.

Cuando se utiliza después de MERGE, @@ROWCOUNT (Transact-SQL) devuelve el número total de filas insertadas, actualizadas y eliminadas al cliente.

MERGE es una palabra clave totalmente reservada cuando el nivel de compatibilidad de la base de datos se establece en 100 o superior. La instrucción MERGE tambiénestá disponible en los niveles de compatibilidad 90 y 100 de la base de datos; sin embargo, la palabra clave no se reserva completamente cuando el nivel decompatibilidad se establece en 90.

La instrucción MERGE no se debe usar cuando se emplea la replicación de actualización en cola. MERGE y el desencadenador de actualización en cola no soncompatibles. Reemplace la instrucción MERGE con una instrucción de inserción o de actualización.

Implementación de desencadenadoresPara cada acción de inserción, actualización o eliminación especificada en la instrucción MERGE, SQL Server activa los desencadenadores AFTER correspondientesdefinidos en la tabla de destino, pero no garantiza qué acción activará los desencadenadores primero o último. Los desencadenadores definidos para la misma accióncumplen el orden que especifique. Para obtener más información sobre cómo establecer el orden de activación de los desencadenadores, vea Especificar el primer y el

último desencadenador.

Si la tabla de destino tiene habilitado un desencadenador INSTEAD OF definido en ella para una acción de inserción, actualización o eliminación realizada por unainstrucción MERGE, debe tener habilitado un desencadenador INSTEAD OF para todas las acciones especificadas en la instrucción MERGE.

Si hay desencadenadores INSTEAD OF UPDATE o INSTEAD OF DELETE definidos en target_table, las operaciones de actualización o eliminación no se realizan. En su

lugar, se activan los desencadenadores y las tablas inserted y deleted se rellenan en consecuencia.

Si hay definidos desencadenadores INSTED OF INSERT en target_table, la operación de inserción no se realiza. En su lugar, se activan los desencadenadores y la tabla

inserted se rellena en consecuencia.

Permisos

Requiere el permiso SELECT en la tabla de origen y los permisos INSERT, UPDATE o DELETE en la tabla de destino. Para obtener información adicional, consulte lasección Permisos de los temas SELECT, INSERT, UPDATE o DELETE.

Ejemplos

A.Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla en una sola instrucciónUn escenario común es la actualización de una o varias columnas de una tabla si una fila coincidente existe, o la inserción de datos como una fila nueva si no existeninguna fila coincidente. Normalmente, para hacer esto se pasan los parámetros a un procedimiento almacenado que contiene las instrucciones INSERT y UPDATEadecuadas. Con la instrucción MERGE puede realizar ambas tareas en una sola instrucción. En el ejemplo siguiente se muestra un procedimiento almacenado de la base

de datos AdventureWorks2012 que contiene una instrucción INSERT y una instrucción UPDATE. A continuación, el procedimiento se modifica para realizar lasoperaciones equivalentes utilizando una sola instrucción MERGE.

CREATE PROCEDURE dbo.InsertUnitMeasure

@UnitMeasureCode nchar(3),

@Name nvarchar(25)

AS

BEGIN









B.Usar MERGE para realizar operaciones UPDATE y DELETE en una tabla en una sola instrucciónEn el siguiente ejemplo se usa MERGE para actualizar diariamente la tabla ProductInventory de la base de datos de ejemplo AdventureWorks2012, en función de lospedidos procesados en la tabla SalesOrderDetail. La columna Quantity de la tabla ProductInventory se actualiza restando el número de pedidos realizados cadadía para cada producto de la tabla SalesOrderDetail. Si el número de pedidos de un producto baja el nivel de inventario del mismo hasta 0 o un valor menor, la filacorrespondiente a ese producto se elimina de la tabla ProductInventory.

SET NOCOUNT ON;

-- Update the row if it exists.

UPDATE Production.UnitMeasure

SET Name = @Name

WHERE UnitMeasureCode = @UnitMeasureCode

-- Insert the row if the UPDATE statement failed.

IF (@@ROWCOUNT = 0 )

BEGIN

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)

VALUES (@UnitMeasureCode, @Name)

END

END;

GO

-- Test the procedure and return the results.

EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name FROM Production.UnitMeasure

WHERE UnitMeasureCode = 'ABC';

GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.

-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.

CREATE TABLE #MyTempTable

(ExistingCode nchar(3),

ExistingName nvarchar(50),

ExistingDate datetime,

ActionTaken nvarchar(10),

NewCode nchar(3),

NewName nvarchar(50),

NewDate datetime

);

GO

ALTER PROCEDURE dbo.InsertUnitMeasure

@UnitMeasureCode nchar(3),

@Name nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

MERGE Production.UnitMeasure AS target

USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)

ON (target.UnitMeasureCode = source.UnitMeasureCode)

WHEN MATCHED THEN

UPDATE SET Name = source.Name

WHEN NOT MATCHED THEN

INSERT (UnitMeasureCode, Name)

VALUES (source.UnitMeasureCode, source.Name)

OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

END;

GO

-- Test the procedure and return the results.

EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';

EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';

EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup

DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');

DROP TABLE #MyTempTable;

GO

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;

GO

CREATE PROCEDURE Production.usp_UpdateInventory

@OrderDate datetime

AS

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

AND soh.OrderDate = @OrderDate

GROUP BY ProductID) AS source (ProductID, OrderQty)

ON (target.ProductID = source.ProductID)

WHEN MATCHED AND target.Quantity - source.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,

target.ModifiedDate = GETDATE()

OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,

C.Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla de destino mediante una tabla de origen derivada

En el ejemplo siguiente se usa MERGE para modificar la tabla SalesReason de la base de datos AdventureWorks2012, actualizando o insertando las filas. Cuando el

valor de NewName de la tabla de origen coincide con un valor de la columna Name de la tabla de destino, (SalesReason), la columna ReasonType se actualiza en la tabla

de destino. Cuando el valor de NewName no coincide, la fila del origen se inserta en la tabla de destino. La tabla de origen es una tabla derivada que usa la característicade constructor con valores de tabla de Transact-SQL para especificar varias filas en la tabla de origen. Para obtener más información acerca de cómo usar elconstructor de valores de tabla en una tabla derivada, vea Constructor con valores de tabla (Transact-SQL). El ejemplo también muestra cómo almacenar los resultadosde la cláusula OUTPUT en una variable de tabla y, a continuación, resumir los resultados de la instrucción MERGE realizando una sencilla operación SELECT quedevuelve el recuento de las filas insertadas y actualizadas.

D.Insertar los resultados de la instrucción MERGE en otra tablaEn el ejemplo siguiente se capturan los datos devueltos por la cláusula OUTPUT de una instrucción MERGE y se insertan en otra tabla. La instrucción MERGE actualizadiariamente la columna Quantity de la tabla ProductInventory de la base de datos AdventureWorks2012, en función de los pedidos procesados en la tablaSalesOrderDetail. En el ejemplo se capturan las filas actualizadas y se insertan en otra tabla que se usa para realizar el seguimiento de los cambios del inventario.

Vea también

ReferenciaSELECT (Transact-SQL)


UPDATE (Transact-SQL)

DELETE (Transact-SQL)

OUTPUT ﴾cláusula de Transact‐SQL﴿FROM (Transact-SQL)

Constructor con valores de tabla (Transact-SQL)

ConceptosMERGE en paquetes de Integration Services

© 2014 Microsoft

Deleted.Quantity, Deleted.ModifiedDate;

GO

EXECUTE Production.usp_UpdateInventory '20030501'

-- Create a temporary table variable to hold the output actions.

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target

USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))

AS Source (NewName, NewReasonType)

ON Target.Name = Source.NewName

WHEN MATCHED THEN

UPDATE SET ReasonType = Source.NewReasonType

WHEN NOT MATCHED BY TARGET THEN

INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.

SELECT Change, COUNT(*) AS CountPerChange

FROM @SummaryOfChanges

GROUP BY Change;

CREATE TABLE Production.UpdatedInventory

(ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,

CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));

GO

INSERT INTO Production.UpdatedInventory

SELECT ProductID, LocationID, NewQty, PreviousQty

FROM

( MERGE Production.ProductInventory AS pi

USING (SELECT ProductID, SUM(OrderQty)

FROM Sales.SalesOrderDetail AS sod



AND soh.OrderDate BETWEEN '20030701' AND '20030731'

GROUP BY ProductID) AS src (ProductID, OrderQty)

ON pi.ProductID = src.ProductID

WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0

THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

THEN DELETE

OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)

AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';

GO









http://msdn.microsoft.com/es-es/library/cc280522.aspx

OUTPUT ﴾cláusula de Transact‐SQL﴿

Devuelve información de las filas afectadas por una instrucción INSERT, UPDATE, DELETE o MERGE, o expresiones basadas en esas filas. Estos resultados se pueden

devolver a la aplicación de procesamiento para que los utilice en mensajes de confirmación, archivado y otros requisitos similares de una aplicación. Los resultados

también se pueden insertar en una tabla o variable de tabla. Además, puede capturar los resultados de una cláusula OUTPUT en una instrucción anidada INSERT, UPDATE,DELETE o MERGE, e insertar los resultados en una tabla de destino o vista.

Nota

Una instrucción UPDATE, INSERT o DELETE que tenga una cláusula OUTPUT devolverá filas al cliente aunque la instrucción encuentre errores y se revierta. El resultado

no se debe usar si se produce algún error al ejecutar la instrucción.

Se utiliza en:

DELETE

INSERT

UPDATE

MERGE

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).


Sintaxis

Argumentos

@table_variable

Especifica una variable table en la que se insertan las filas devueltas en lugar de devolverse al autor de la llamada. Debe declararse @table_variable antes de la

instrucción INSERT, UPDATE, DELETE o MERGE.

Si no se especifica column_list, la variable table debe tener el mismo número de columnas que el conjunto de resultados OUTPUT. Las excepciones son las

columnas de identidad y calculadas, que deben omitirse. Si se especifica column_list, las columnas omitidas deben aceptar valores NULL o tener valores

predeterminados asignados.

Para obtener más información acerca de las variables table, vea table (Transact-SQL).

output_table

Especifica una tabla en la que se insertan las filas devueltas en lugar de devolverse al autor de la llamada. output_table puede ser una tabla temporal.

Si no se especifica column_list, la tabla debe tener el mismo número de columnas que el conjunto de resultados OUTPUT. Las excepciones son las columnas de

identidad y calculadas. Éstas deben omitirse. Si se especifica column_list, las columnas omitidas deben aceptar valores NULL o tener valores predeterminados

asignados.

output_table no puede:

Tener definidos desencadenadores habilitados.

Participar en alguna de las partes de una restricción FOREIGN KEY.

Tener restricciones CHECK o reglas habilitadas.

column_list

Es una lista opcional de nombres de columna de la tabla de destino de la cláusula INTO. Es equivalente a la lista de columnas permitida en la instrucción INSERT.

scalar_expression

Es cualquier combinación de símbolos y operadores que se evalúa como un solo valor. No se permiten funciones de agregado en scalar_expression.

Cualquier referencia a las columnas de la tabla que se va a modificar debe calificarse con el prefijo INSERTED o DELETED.

SQL Server 2014

<OUTPUT_CLAUSE> ::=

{

[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]

[ OUTPUT <dml_select_list> ]

}

<dml_select_list> ::=

{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]

[ ,...n ]

<column_name> ::=

{ DELETED | INSERTED | from_table_name } . { * | column_name }

| $action






column_alias_identifier

Es un nombre alternativo que se utiliza para hacer referencia al nombre de columna.

DELETED

Es un prefijo de columna que especifica el valor eliminado en la operación de actualización o eliminación. Las columnas con prefijo DELETED reflejan el valor

antes de que se complete la instrucción UPDATE, DELETE o MERGE.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

INSERTED

Es un prefijo de columna que especifica el valor agregado en la operación de inserción o actualización. Las columnas con prefijo INSERTED reflejan el valor

después de que se complete la instrucción UPDATE, INSERT o MERGE, pero antes de que se ejecuten los desencadenadores.

INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.

from_table_name

Es un prefijo de columna que especifica una tabla incluida en la cláusula FROM de una instrucción DELETE, UPDATE o MERGE que se utiliza para especificar lasfilas que se van a actualizar o eliminar.

Si la tabla que se va a modificar se especifica también en la cláusula FROM, cualquier referencia a las columnas de esa tabla deben calificarse con el prefijoINSERTED o DELETED.

*

Especifica que todas las columnas afectadas por la acción de eliminación, inserción o actualización se devuelvan en el orden en que se encuentran en la tabla.

Por ejemplo, OUTPUT DELETED.* en la siguiente instrucción DELETE devuelve todas las columnas eliminadas de la tabla ShoppingCartItem:

column_name

Es una referencia explícita a una columna. Cualquier referencia a la tabla que se va a modificar debe certificarse correctamente mediante el prefijo INSERTED o

DELETED, según corresponda; por ejemplo: INSERTED.column_name.

$action

Solo está disponible para la instrucción MERGE. Especifica una columna de tipo nvarchar(10) en la cláusula OUTPUT de una instrucción MERGE que devuelve unode estos tres valores por cada fila: 'INSERT', 'UPDATE' o 'DELETE', según la acción realizada en dicha fila.

Comentarios

La cláusula OUTPUT <dml_select_list> y la cláusula OUTPUT <dml_select_list> INTO { @table_variable | output_table } pueden definirse en una sola instrucción INSERT,UPDATE, DELETE o MERGE.

Nota

A menos que se indique lo contrario, las referencias a la cláusula OUTPUT se refieren tanto a la cláusula OUTPUT como a la cláusula OUTPUT INTO.

La cláusula OUTPUT puede ser útil para recuperar el valor de las columnas de identidad o calculadas después de una operación con INSERT o UPDATE.

Cuando se incluye una columna calculada en <dml_select_list>, la columna correspondiente de la tabla de salida o variable de tabla no es una columna calculada. Los

valores de la nueva columna son los que se calcularon en el momento en que se ejecutó la instrucción.

No se garantiza que coincidan el orden en que se aplican los cambios en la tabla y el orden en que se insertan las filas en la tabla de salida o variable de tabla.

Si se modifican parámetros o variables como parte de una instrucción UPDATE, la cláusula OUTPUT siempre devuelve el valor del parámetro o la variable tal como seencontraba antes de ejecutar la instrucción, en lugar de devolver el valor modificado.

OUTPUT se puede utilizar con una instrucción UPDATE o DELETE en un cursor que utilice la sintaxis WHERE CURRENT OF.

La cláusula OUTPUT no se admite en las siguientes instrucciones:

Instrucciones DML que hacen referencia a vistas locales con particiones, vistas distribuidas con particiones o tablas remotas.

Instrucciones INSERT que contienen una instrucción EXECUTE.

Los predicados de texto completo no están permitidos en la cláusula OUTPUT cuando el nivel de compatibilidad de la base de datos está establecido en 100.

La cláusula OUTPUT INTO no se puede utilizar para realizar inserciones en vistas o en una función de conjunto de filas.

No se puede crear una función definida por el usuario si contiene una cláusula OUTPUT INTO que tiene una tabla como destino.

Para evitar el comportamiento no determinista, la cláusula OUTPUT no puede contener las referencias siguientes:

Subconsultas o funciones definidas por el usuario que obtienen acceso a datos de usuario o del sistema, o que se asume que obtienen dicho acceso. Se supone

que las funciones definidas por el usuario realizan el acceso a los datos si no están enlazadas a un esquema.

Una columna de una vista o función insertada con valores de tabla si la columna se define mediante uno de los métodos siguientes:

Una subconsulta.

DELETE Sales.ShoppingCartItem

OUTPUT DELETED.*;

Una función definida por el usuario que obtiene acceso a datos de usuario o del sistema, o que se asume que obtiene dicho acceso.

Una columna calculada que contiene una función definida por el usuario que obtiene acceso a datos de usuario o del sistema en su definición.

Cuando SQL Server detecta este tipo de columna en la cláusula OUTPUT, se produce el error 4186. Para obtener más información, vea MSSQLSERVER_4186.

Insertar datos devueltos de una cláusula OUTPUT en una tablaAl capturar los resultados de una cláusula OUTPUT en una instrucción INSERT, UPDATE, DELETE o MERGE anidada e insertarlos en una tabla de destino, tenga presentela información siguiente:

Toda la operación es atómica. Se ejecutarán la instrucción INSERT y la instrucción DML anidada que contiene la cláusula OUTPUT, o bien se producirá un error entoda la instrucción.

Las restricciones siguientes se aplican al destino de la instrucción INSERT externa:

El destino no puede ser una expresión de tabla común, vista o tabla remota.

El destino no puede tener una restricción FOREIGN KEY, ni ser objeto de referencia por una restricción FOREIGN KEY.

No se pueden definir desencadenadores en el destino.

El destino no puede participar en la replicación de mezcla ni en las suscripciones actualizables para la replicación transaccional.

Las restricciones siguientes se aplican a la instrucción DML anidada:

El destino no puede ser una tabla remota ni una vista con particiones.

El propio origen no puede contener una cláusula <dml_table_source>.

La cláusula OUTPUT INTO no se admite en instrucciones INSERT que contengan una cláusula <dml_table_source>.

@@ROWCOUNT devuelve las filas insertadas únicamente por la instrucción INSERT externa.

@@IDENTITY, SCOPE_IDENTITY e IDENT_CURRENT devuelven los valores de identidad generados solo por la instrucción DML anidada, y no los generados por lainstrucción INSERT externa.

Las notificaciones de consulta tratan la instrucción como una entidad única, y el tipo de cualquier mensaje creado es el del DML anidado, aunque el cambiosignificativo provenga de la propia instrucción INSERT externa.

En la cláusula <dml_table_source>, las cláusulas SELECT y WHERE no pueden incluir subconsultas, funciones de agregado, funciones de categoría, predicados detexto completo, funciones definidas por el usuario que realicen accesos a datos, ni la función TEXTPTR.

Desencadenadores

Las columnas devueltas de OUTPUT reflejan los datos tal como estaban después de completarse la instrucción INSERT, UPDATE o DELETE, pero antes de ejecutarse losdesencadenadores.

En el caso de los desencadenadores INSTEAD OF, los resultados devueltos se generan como si la operación de INSERT, UPDATE o DELETE se hubiese producidorealmente, aunque no se produzcan modificaciones como resultado de la operación del desencadenador. Si se utiliza una instrucción que incluye una cláusula OUTPUTen el cuerpo de un desencadenador, deben utilizarse alias de tabla para hacer referencia a las tablas inserted y deleted del desencadenador con el fin de evitar la

duplicación de las referencias a columnas con las tablas INSERTED y DELETED asociadas a OUTPUT.

Si la cláusula OUTPUT se especifica sin especificar también la palabra clave INTO, el destino de la operación DML no puede tener definido ningún desencadenadorhabilitado para la acción DML dada. Por ejemplo, si se define la cláusula OUTPUT en una instrucción UPDATE, la tabla de destino no puede tener desencadenadoresUPDATE habilitados.

Si se establece la opción sp_configure de disallow results from triggers, una cláusula OUTPUT sin una cláusula INTO hará que la instrucción genere un error cuando seinvoque desde un desencadenador.

Tipos de datos

La cláusula OUTPUT admite los tipos de datos de objetos grandes: nvarchar(max), varchar(max), varbinary(max), text, ntext, image y xml. Cuando se utiliza la

cláusula .WRITE en la instrucción UPDATE para modificar una columna de tipo nvarchar(max), varchar(max) o varbinary(max), se devuelven las imágenes anterior yposterior completas de los valores si se hace referencia a ellas. La función TEXTPTR﴾ ﴿ no puede aparecer como parte de una expresión en una columna de tipo text,

ntext o image en la cláusula OUTPUT.

Colas

OUTPUT se puede utilizar en aplicaciones que utilizan tablas como colas, o para contener conjuntos de resultados intermedios. Dicho de otro modo, la aplicaciónagrega o quita filas de la tabla constantemente. En el ejemplo siguiente se utiliza la cláusula OUTPUT en una instrucción DELETE para devolver la fila eliminada a laaplicación que realiza la llamada.

En este ejemplo, se quita una fila de una tabla utilizada como cola y se devuelven los valores eliminados a la aplicación de procesamiento en una única acción. Tambiénse puede implementar otro tipo de semántica, como utilizar una tabla para implementar una pila. No obstante, SQL Server no garantiza el orden en que las

instrucciones DML procesan y devuelven las filas por medio de la cláusula OUTPUT. Es la aplicación la que debe incluir una cláusula WHERE que garantice la semánticadeseada, o reconocer que, si hay varias filas aptas para la operación DML, no se garantiza el orden. En el ejemplo siguiente se utiliza una subconsulta y se supone que

USE AdventureWorks2012;

GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)

OUTPUT deleted.*

WHERE DatabaseLogID = 7;

GO

http://msdn.microsoft.com/es-es/library/cc645595.aspx

la unicidad es una característica de la columna DatabaseLogID para implementar la semántica de ordenación deseada.

Nota

Use la sugerencia de tabla READPAST en las instrucciones UPDATE y DELETE si el escenario permite que varias aplicaciones realicen una lectura destructiva de una

tabla. De esta forma se impide que surjan problemas de bloqueo si otra aplicación ya está leyendo el primer registro de la tabla que reúne los requisitos.

Permisos

Se requieren permisos SELECT en las columnas recuperadas a través de <dml_select_list> o utilizadas en <scalar_expression>.

Se requieren permisos INSERT en las tablas especificadas en <output_table>.

Ejemplos

A.Utilizar OUTPUT INTO con una instrucción INSERT simpleEn el siguiente ejemplo se inserta una fila en la tabla ScrapReason y se utiliza la cláusula OUTPUT para devolver los resultados de la instrucción a la variabletable@MyTableVar. Como la columna ScrapReasonID se ha definido con una propiedad IDENTITY, no se especifica ningún valor en la instrucción INSERT de esa

columna. No obstante, tenga en cuenta que el valor generado por el Motor de base de datos para esa columna se devuelve en la cláusula OUTPUT de la columna

inserted.ScrapReasonID.

USE tempdb;

GO

CREATE TABLE dbo.table1

(

id INT,

employee VARCHAR(32)

);

GO

INSERT INTO dbo.table1 VALUES

(1, 'Fred')

,(2, 'Tom')

,(3, 'Sally')

,(4, 'Alice');

GO

DECLARE @MyTableVar TABLE

(

id INT,

employee VARCHAR(32)

);

PRINT 'table1, before delete'

SELECT * FROM dbo.table1;

DELETE FROM dbo.table1

OUTPUT DELETED.* INTO @MyTableVar

WHERE id = 4 OR id = 2;

PRINT 'table1, after delete'

SELECT * FROM dbo.table1;

PRINT '@MyTableVar, after delete'

SELECT * FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results

--table1, before delete

--id employee

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

--1 Fred

--2 Tom

--3 Sally

--4 Alice

--

--table1, after delete

--id employee

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

--1 Fred

--3 Sally

--@MyTableVar, after delete

--id employee

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

--2 Tom

--4 Alice

B.Usar OUTPUT con una instrucción DELETEEn el ejemplo siguiente se eliminan todas las filas de la tabla ShoppingCartItem. La cláusula OUTPUT deleted.* especifica que se devuelvan a la aplicación que realizala llamada los resultados de la instrucción DELETE, es decir, todas las columnas de las filas eliminadas. La instrucción SELECT posterior comprueba los resultados de la

operación de eliminación en la tabla ShoppingCartItem.

C.Usar OUTPUT INTO con una instrucción UPDATEEn el ejemplo siguiente se actualiza un 25 por ciento la columna VacationHours de las 10 primeras filas de la tabla Employee. La cláusula OUTPUT devuelve el valor de

VacationHours antes de aplicar la instrucción UPDATE en la columna deleted.VacationHours, y el valor actualizado de la columna inserted.VacationHours en la

variable table@MyTableVar.

Las dos instrucciones SELECT que le siguen devuelven los valores en @MyTableVar y los resultados de la operación de actualización en la tabla Employee.

D.Usar OUTPUT INTO para devolver una expresiónEl ejemplo siguiente, que se basa en el ejemplo C, define una expresión en la cláusula OUTPUT como la diferencia entre el valor actualizado de VacationHours y el valor

de VacationHours antes de aplicar la actualización. El valor de esta expresión se devuelve a la variable table@MyTableVar en la columna VacationHoursDifference.


GO

DECLARE @MyTableVar table( NewScrapReasonID smallint,

Name varchar(50),

ModifiedDate datetime);

INSERT Production.ScrapReason

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

INTO @MyTableVar

VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.

SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

--Display the result set of the table.

SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason;

GO


GO

DELETE Sales.ShoppingCartItem

OUTPUT DELETED.*

WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.

SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;

GO


GO

DECLARE @MyTableVar table(

EmpID int NOT NULL,

OldVacationHours int,

NewVacationHours int,


UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25,

ModifiedDate = GETDATE()

OUTPUT inserted.BusinessEntityID,

deleted.VacationHours,

inserted.VacationHours,

inserted.ModifiedDate

INTO @MyTableVar;


SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate

FROM @MyTableVar;

GO

--Display the result set of the table.

SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate

FROM HumanResources.Employee;

GO


GO


EmpID int NOT NULL,

OldVacationHours int,

E.Usar OUTPUT INTO con from_table_name en una instrucción UPDATEEn el ejemplo siguiente se actualiza la columna ScrapReasonID de la tabla WorkOrder para todas las órdenes de trabajo que tengan especificados ProductID y

ScrapReasonID. La cláusula OUTPUT INTO devuelve los valores de la tabla que se actualiza (WorkOrder) y de la tabla Product. La tabla Product se utiliza en la cláusulaFROM para especificar las filas que se van a actualizar. Dado que la tabla WorkOrder tiene definido un desencadenador AFTER UPDATE, se requiere la palabra clave INTO.

F.Usar OUTPUT INTO con from_table_name en una instrucción DELETEEn el ejemplo siguiente se eliminan las filas de la tabla ProductProductPhoto según los criterios de búsqueda definidos en la cláusula FROM de la instrucción DELETE. La

cláusula OUTPUT devuelve columnas de la tabla que se elimina (deleted.ProductID, deleted.ProductPhotoID) y de la tabla Product. La tabla se utiliza en la cláusulaFROM para especificar las filas que se van a eliminar.

NewVacationHours int,

VacationHoursDifference int,


UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25,

ModifiedDate = GETDATE()

OUTPUT inserted.BusinessEntityID,

deleted.VacationHours,

inserted.VacationHours,

inserted.VacationHours - deleted.VacationHours,

inserted.ModifiedDate

INTO @MyTableVar;


SELECT EmpID, OldVacationHours, NewVacationHours,

VacationHoursDifference, ModifiedDate

FROM @MyTableVar;

GO

SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate


GO


GO

DECLARE @MyTestVar table (

OldScrapReasonID int NOT NULL,

NewScrapReasonID int NOT NULL,

WorkOrderID int NOT NULL,

ProductID int NOT NULL,

ProductName nvarchar(50)NOT NULL);

UPDATE Production.WorkOrder

SET ScrapReasonID = 4

OUTPUT deleted.ScrapReasonID,

inserted.ScrapReasonID,

inserted.WorkOrderID,

inserted.ProductID,

p.Name

INTO @MyTestVar

FROM Production.WorkOrder AS wo

INNER JOIN Production.Product AS p

ON wo.ProductID = p.ProductID

AND wo.ScrapReasonID= 16

AND p.ProductID = 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,

ProductID, ProductName

FROM @MyTestVar;

GO


GO

DECLARE @MyTableVar table (


ProductName nvarchar(50)NOT NULL,

ProductModelID int NOT NULL,

PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto

OUTPUT DELETED.ProductID,

p.Name,

p.ProductModelID,

DELETED.ProductPhotoID

INTO @MyTableVar

FROM Production.ProductProductPhoto AS ph

JOIN Production.Product as p

ON ph.ProductID = p.ProductID

WHERE p.ProductModelID BETWEEN 120 and 130;

G.Usar OUTPUT INTO con un tipo de datos de objetos grandes

En el ejemplo siguiente se actualiza un valor parcial de DocumentSummary, una columna de tipo nvarchar(max) de la tabla Production.Document, utilizando la cláusula.WRITE. La palabra components se sustituye por la palabra features al especificar la palabra sustituta, la ubicación inicial ﴾desplazamiento﴿ de la palabra que se va asustituir en los datos existentes y el número de caracteres que se va a sustituir ﴾longitud﴿. En el ejemplo se utiliza la cláusula OUTPUT para devolver las imágenes anteriory posterior de la columna DocumentSummary en la variable table@MyTableVar. Observe que se devuelven las imágenes anterior y posterior completas de la columnaDocumentSummary.

H.Usar OUTPUT en un desencadenador INSTEAD OF

En el ejemplo siguiente se utiliza la cláusula OUTPUT en un desencadenador para devolver los resultados de la operación del desencadenador. En primer lugar se crea

una vista en la tabla ScrapReason y, después, en la vista se define un desencadenador INSTEAD OF INSERT que permite al usuario modificar únicamente la columnaName de la tabla base. Puesto que la columna ScrapReasonID es una columna IDENTITY de la tabla base, el desencadenador omite el valor suministrado por el usuario.

Esto permite que el Motor de base de datos genere automáticamente el valor correcto. Asimismo, se omite el valor suministrado por el usuario para ModifiedDate,

que se establece en la fecha actual. La cláusula OUTPUT devuelve los valores reales insertados en la tabla ScrapReason.

Éste es el conjunto de resultados generado el 12 de abril de 2004 ﴾'2004-04-12'). Tenga en cuenta que las columnas ScrapReasonIDActual y ModifiedDate reflejan

los valores generados en la operación del desencadenador en lugar de los valores suministrados en la instrucción INSERT.

ScrapReasonID Name ModifiedDate

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

17 My scrap reason 2004-04-12 16:23:33.050

I.Usar OUTPUT INTO con columnas de identidad y calculadas

En el ejemplo siguiente se crea la tabla EmployeeSales y, después, se insertan en ella varias filas utilizando una instrucción INSERT con una instrucción SELECT para

recuperar los datos de las tablas de origen. La tabla EmployeeSales contiene una columna de identidad (EmployeeID) y una columna calculada (ProjectedSales).

--Display the results of the table variable.

SELECT ProductID, ProductName, ProductModelID, PhotoID

FROM @MyTableVar

ORDER BY ProductModelID;

GO


GO


SummaryBefore nvarchar(max),

SummaryAfter nvarchar(max));

UPDATE Production.Document

SET DocumentSummary .WRITE (N'features',28,10)

OUTPUT deleted.DocumentSummary,

inserted.DocumentSummary

INTO @MyTableVar

WHERE Title = N'Front Reflector Bracket Installation';

SELECT SummaryBefore, SummaryAfter

FROM @MyTableVar;

GO


GO

IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL

DROP VIEW dbo.vw_ScrapReason;

GO

CREATE VIEW dbo.vw_ScrapReason

AS (SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason);

GO

CREATE TRIGGER dbo.io_ScrapReason

ON dbo.vw_ScrapReason

INSTEAD OF INSERT

AS

BEGIN

--ScrapReasonID is not specified in the list of columns to be inserted

--because it is an IDENTITY column.

INSERT INTO Production.ScrapReason (Name, ModifiedDate)

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,

INSERTED.ModifiedDate

SELECT Name, getdate()

FROM inserted;

END

GO

INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)

VALUES (99, N'My scrap reason','20030404');

GO

J.Usar OUTPUT y OUTPUT INTO en una sola instrucciónEn el ejemplo siguiente se eliminan las filas de la tabla ProductProductPhoto según los criterios de búsqueda definidos en la cláusula FROM de la instrucción DELETE. La

cláusula OUTPUT INTO devuelve las columnas de la tabla que se elimina (deleted.ProductID, deleted.ProductPhotoID) y columnas de la tabla Product a la variable

table@MyTableVar. La tabla Product se utiliza en la cláusula FROM para especificar las filas que se van a eliminar. La cláusula OUTPUT devuelve las columnas

deleted.ProductID y deleted.ProductPhotoID, y la fecha y hora de eliminación de la fila de la tabla ProductProductPhoto a la aplicación que realiza la llamada.

K.Insertar los datos devueltos por una cláusula OUTPUTEl ejemplo siguiente captura datos devueltos por la cláusula OUTPUT de una instrucción MERGE y los inserta en otra tabla. La instrucción MERGE actualiza diariamente la

columna Quantity de la tabla ProductInventory en función de los pedidos procesados en la tabla SalesOrderDetail. También elimina las filas correspondientes alos productos cuyas existencias están en el valor 0 o por debajo de este valor. En el ejemplo, se capturan las filas que se eliminan y se insertan en otra tabla,

ZeroInventory, que realiza el seguimiento de los productos sin existencias.

USE AdventureWorks2012 ;

GO

IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeSales;

GO

CREATE TABLE dbo.EmployeeSales

( EmployeeID int IDENTITY (1,5)NOT NULL,

LastName nvarchar(20) NOT NULL,

FirstName nvarchar(20) NOT NULL,

CurrentSales money NOT NULL,

ProjectedSales AS CurrentSales * 1.10

);

GO


EmployeeID int NOT NULL,



CurrentSales money NOT NULL,

ProjectedSales money NOT NULL

);

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)

OUTPUT INSERTED.LastName,

INSERTED.FirstName,

INSERTED.CurrentSales

INTO @MyTableVar

SELECT c.LastName, c.FirstName, sp.SalesYTD

FROM Sales.SalesPerson AS sp

INNER JOIN Person.Person AS c

ON sp.BusinessEntityID = c.BusinessEntityID

WHERE sp.BusinessEntityID LIKE '2%'

ORDER BY c.LastName, c.FirstName;

SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales

FROM @MyTableVar;

GO

SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales

FROM dbo.EmployeeSales;

GO


GO



ProductName nvarchar(50)NOT NULL,

ProductModelID int NOT NULL,

PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto

OUTPUT DELETED.ProductID,

p.Name,

p.ProductModelID,

DELETED.ProductPhotoID

INTO @MyTableVar

OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate

FROM Production.ProductProductPhoto AS ph

JOIN Production.Product as p

ON ph.ProductID = p.ProductID

WHERE p.ProductID BETWEEN 800 and 810;

--Display the results of the table variable.

SELECT ProductID, ProductName, PhotoID, ProductModelID

FROM @MyTableVar;

GO


GO

Vea también

ReferenciaDELETE (Transact-SQL)



table (Transact-SQL)

CREATE TRIGGER (Transact-SQL)

sp_configure (Transact-SQL)

© 2014 Microsoft

IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL

DROP TABLE Production.ZeroInventory;

GO

--Create ZeroInventory table.

CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);

GO

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)

SELECT ProductID, GETDATE()

FROM

( MERGE Production.ProductInventory AS pi

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod



AND soh.OrderDate = '20070401'

GROUP BY ProductID) AS src (ProductID, OrderQty)

ON (pi.ProductID = src.ProductID)

WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0

THEN DELETE

WHEN MATCHED

THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty

OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)

WHERE Action = 'DELETE';

IF @@ROWCOUNT = 0

PRINT 'Warning: No rows were inserted';

GO

SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;







READTEXT (Transact-SQL)

Lee los valores text, ntext o image de una columna text, ntext o image; comienza a partir de un desplazamiento especificado y lee el número de bytes determinado.

Importante

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificarlas aplicaciones que actualmente la utilizan. Utilice la función SUBSTRING en su lugar.

Se aplica a: SQL Server ﴾SQL Server 2008 a través de la versión actual).


Sintaxis

Argumentos

table . column

Es el nombre de la tabla y de la columna donde se va a leer. Los nombres de tablas y columnas se deben ajustar a las reglas de los identificadores. Es necesario

especificar los nombres de la tabla y de la columna; sin embargo, es opcional especificar el nombre de la base de datos y del propietario.

text_ptr

Es un puntero de texto válido. text_ptr debe ser de tipo binary(16).

offset

Es el número de bytes ﴾cuando se utilizan los tipos de datos text o image) o de caracteres (cuando se utiliza el tipo de datos ntext) que se va a omitir antes de

comenzar a leer los datos text, image o ntext.

size

Es el número de bytes ﴾cuando se utilizan los tipos de datos text o image) o de caracteres (cuando se utiliza el tipo de datos ntext) de los datos que se van a

leer. Si size es 0, se leen 4 KB de datos.

HOLDLOCK

Hace que se bloquee el valor de texto para lectura hasta el final de la transacción. Otros usuarios pueden leer el valor, pero no pueden modificarlo.

Comentarios

Use la función TEXTPTR para obtener un valor de text_ptr válido. TEXTPTR devuelve un puntero para la columna text, ntext o image de la fila especificada o para la

columna text, ntext o image de la última fila devuelta por la consulta si devuelve más de una fila. Debido a que TEXTPTR devuelve una cadena binaria de 16 bytes, se

recomienda declarar una variable local para que contenga el puntero de texto y, a continuación, utilizar la variable con READTEXT. Para obtener más información acercade la declaración de una variable local, vea DECLARE @local_variable (Transact-SQL).

En SQL Server, pueden existir punteros de texto consecutivos, aunque quizás no sean válidos. Par obtener más información acerca de la opción text in row, vea

sp_tableoption (Transact-SQL). Para obtener más información acerca de la invalidación de punteros de texto, vea sp_invalidate_textptr (Transact-SQL).

El valor de la función @@TEXTSIZE reemplaza el tamaño especificado para READTEXT si es menor que éste. La función @@TEXTSIZE especifica el límite que establece lainstrucción SET TEXTSIZE sobre el número de bytes de datos que se va a devolver. Para obtener más información acerca de cómo establecer la configuración de sesiónpara TEXTSIZE, vea SET TEXTSIZE (Transact-SQL).

Permisos

Los permisos READTEXT se conceden de manera predeterminada a los usuarios con permisos SELECT para la tabla especificada. Los permisos se pueden transferir

cuando se transfieren los permisos SELECT.

Ejemplos

En el siguiente ejemplo se lee desde el carácter dos al veintiséis de la columna pr_info de la tabla pub_info.

Nota

Para ejecutar este ejemplo, es necesario instalar la base de datos de ejemplo pubs.

SQL Server 2014

READTEXT { table.column text_ptr offset size } [ HOLDLOCK ]










Vea también

Referencia@@TEXTSIZE (Transact-SQL)



© 2014 Microsoft

USE pubs;

GO

DECLARE @ptrval varbinary(16);

SELECT @ptrval = TEXTPTR(pr_info)

FROM pub_info pr INNER JOIN publishers p

ON pr.pub_id = p.pub_id

AND p.pub_name = 'New Moon Books'

READTEXT pub_info.pr_info @ptrval 1 25;

GO




Condiciones de búsqueda ﴾Transact‐SQL﴿

Es una combinación de uno o varios predicados que utilizan los operadores lógicos AND, OR y NOT.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).


Sintaxis

Argumentos

<search_condition>

Especifica las condiciones de las filas devueltas en el conjunto de resultados de una instrucción SELECT, una expresión de consulta o una subconsulta. En una

instrucción UPDATE, especifica las filas que se van a actualizar. En una instrucción DELETE, especifica las filas que se van a eliminar. No hay límite en el número depredicados que se pueden incluir en una condición de búsqueda de una instrucción Transact‐SQL.

NOT

Niega la expresión booleana que especifica el predicado. Para obtener más información, vea NOT (Transact-SQL).

AND

Combina dos condiciones y se evalúa como TRUE cuando ambas condiciones son TRUE. Para obtener más información, vea AND (Transact-SQL).

OR

Combina dos condiciones y se evalúa como TRUE cuando alguna de las condiciones es TRUE. Para obtener más información, vea OR (Transact-SQL).

< predicate >

Es una expresión que devuelve TRUE, FALSE o UNKNOWN.

expression

Es un nombre de columna, una constante, una función, una variable, una subconsulta escalar o cualquier combinación de nombres de columna, constantes yfunciones conectados mediante uno o varios operadores o una subconsulta. La expresión también puede contener la expresión CASE.

Nota

Cuando se hace referencia a los tipos de datos de caracteres Unicode nchar, nvarchar y ntext, debe agregarse como prefijo la letra mayúscula 'N' a'expression'. Si no se especifica 'N', SQL Server convierte la cadena a la página de códigos que se corresponde con la intercalación predeterminada de labase de datos o columna. Los caracteres que no se encuentren en esta página de códigos se perderán.

=

Es el operador que se utiliza para probar la igualdad entre dos expresiones.

<>

Es el operador que se utiliza para probar si dos expresiones no son iguales entre sí.

!=

Es el operador que se utiliza para probar si dos expresiones no son iguales entre sí.

>

Es el operador que se utiliza para probar si una expresión es mayor que la otra.

>=

Es el operador que se utiliza para probar si una expresión es mayor o igual que la otra expresión.

!>

Es el operador que se utiliza para probar si una expresión no es mayor que la otra expresión.

<

SQL Server 2014

<search_condition> ::=

{ [ NOT ] <predicate> | ( <search_condition> ) }

[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]

[ ,...n ]

<predicate> ::=

{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression

| string_expression [ NOT ] LIKE string_expression

[ ESCAPE 'escape_character' ]

| expression [ NOT ] BETWEEN expression AND expression

| expression IS [ NOT ] NULL

| CONTAINS

( { column | * } , '<contains_search_condition>' )

| FREETEXT ( { column | * } , 'freetext_string' )

| expression [ NOT ] IN ( subquery | expression [ ,...n ] )

| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }

{ ALL | SOME | ANY} ( subquery )

| EXISTS ( subquery ) }







Es el operador que se utiliza para probar si una expresión es menor que la otra.

<=

Es el operador que se utiliza para probar si una expresión es menor o igual que la otra expresión.

!<

Es el operador que se utiliza para probar si una expresión no es menor que la otra expresión.

string_expression

Es una cadena de caracteres y caracteres comodín.

[ NOT ] LIKE

Indica que la siguiente cadena de caracteres se utilizará con la coincidencia de patrón. Para obtener más información, vea LIKE (Transact-SQL).

ESCAPE 'escape_ character'

Permite buscar un carácter comodín en una cadena de caracteres sin que funcione como carácter comodín. escape_character es el carácter que se coloca delantedel carácter comodín para indicar este uso especial.

[ NOT ] BETWEEN

Especifica un intervalo inclusivo de valores. Utilice AND para separar los valores inicial y final. Para obtener más información, vea BETWEEN (Transact-SQL).

IS [ NOT ] NULL

Especifica una búsqueda de valores NULL o de valores que no son NULL, en función de las palabras clave utilizadas. Una expresión que contenga un operadorbit a bit o aritmético se evalúa como NULL si uno de los operandos es NULL.

CONTAINS

Busca en columnas que contengan datos basados en caracteres coincidencias precisas o menos precisas (parciales) con palabras o frases, a una cierta distancia

las unas de las otras y coincidencias ponderadas. Esta opción solo puede usarse con instrucciones SELECT. Para obtener mas información, vea CONTAINS

(Transact-SQL).

FREETEXT

Proporciona una forma sencilla de realizar consultas en lenguaje natural al buscar, en columnas con datos basados en caracteres, valores que coincidan con el

significado en lugar de con las palabras exactas del predicado. Esta opción solo puede usarse con instrucciones SELECT. Para obtener mas información, veaFREETEXT (Transact-SQL).

[ NOT ] IN

Especifica la búsqueda de una expresión, basada en si la expresión está incluida en una lista o excluida de ella. La expresión de búsqueda puede ser unaconstante o un nombre de columna, y la lista puede ser un conjunto de constantes o, más normalmente, una subconsulta. Encierre la lista de valores entre

paréntesis. Para obtener mas información, vea IN (Transact-SQL).

subquery

Se puede considerar como una instrucción SELECT restringida y es similar a <query_expresssion> en la instrucción SELECT. No se permiten la cláusula ORDER BYni la palabra clave INTO. Para obtener más información, vea SELECT (Transact-SQL).

ALL

Se utiliza con un operador de comparación y una subconsulta. Devuelve TRUE para <predicate> si todos los valores obtenidos de la subconsulta satisfacen la

operación de comparación, o FALSE si no todos los valores satisfacen la comparación o cuando la subconsulta no devuelve filas a la instrucción externa. Para

obtener más información, vea ALL (Transact-SQL).

{ SOME | ANY }

Se utiliza con un operador de comparación y una subconsulta. Devuelve TRUE para <predicate> si algún valor obtenido de la subconsulta satisface el operadorde comparación, o FALSE si ningún valor de la subconsulta satisface la comparación o cuando la subconsulta no devuelve filas a la instrucción externa. En caso

contrario, la expresión es UNKNOWN. Para obtener más información, vea SOME | ANY (Transact-SQL).

EXISTS

Se utiliza con una subconsulta para probar la existencia de filas devueltas por la subconsulta. Para obtener más información, vea EXISTS (Transact-SQL).

Comentarios

El orden de prioridad de los operadores lógicos es NOT ﴾el más alto﴿, seguido de AND y OR. Se pueden utilizar paréntesis para invalidar esta prioridad en unacondición de búsqueda. El orden de evaluación de los operadores lógicos puede variar dependiendo de las opciones elegidas por el optimizador de consultas. Para

obtener más información acerca del funcionamiento de los operadores lógicos con valores lógicos, vea AND (Transact-SQL), OR (Transact-SQL) y NOT (Transact-SQL).

Ejemplos

A.Usar WHERE con la sintaxis de LIKE y ESCAPE

En el siguiente ejemplo se buscan filas en las que la columna LargePhotoFileName tenga los caracteres green_ y se utiliza la opción ESCAPE porque _ es un caráctercomodín. Sin especificar la opción ESCAPE, la consulta buscaría los valores de descripción que contuvieran la palabra green seguida de cualquier carácter distinto delcarácter _.

B.Usar la sintaxis de WHERE y LIKE con datos Unicode


GO

SELECT *

FROM Production.ProductPhoto

WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;













En el siguiente ejemplo se utiliza la cláusula WHERE para recuperar la dirección de correo de una empresa que está fuera de los Estados Unidos ﴾US) y en una ciudad

cuyo nombre empieza con Pa.

Vea también

ReferenciaFunciones de agregado (Transact-SQL)

CASE (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

Cursores (Transact-SQL)


Expresiones (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

FROM (Transact-SQL)

Operadores (Transact-SQL)


© 2014 Microsoft


GO

SELECT AddressLine1, AddressLine2, City, PostalCode, CountryRegionCode

FROM Person.Address AS a

JOIN Person.StateProvince AS s ON a.StateProvinceID = s.StateProvinceID

WHERE CountryRegionCode NOT IN ('US')

AND City LIKE N'Pa%' ;











Constructor con valores de tabla (Transact-SQL)

Especifica un conjunto de expresiones de valores de fila que se va a construir en una tabla. El constructor de valor de tabla de Transact-SQL permite que se especifiquen

varias filas de datos en una sola instrucción DML. El constructor del valor de tabla se puede especificar en la cláusula VALUES de la instrucción INSERT, en la cláusulaUSING <tabla de origen> de la instrucción MERGE y en la definición de una tabla derivada en la cláusula FROM.



Sintaxis

Argumentos

VALUES

Introduce las listas de expresión de los valores de las filas. Cada lista debe aparecer entre paréntesis y separarse mediante una coma.

El número de valores especificados en cada lista debe ser el mismo y los valores deben estar en el mismo orden que las columnas de la tabla. Se debe

especificar un valor para cada columna de la tabla o la lista de columnas debe especificar explícitamente las columnas para cada valor entrante.

DEFAULT

Hace que Motor de base de datos inserte el valor predeterminado definido para una columna. Si no existe ningún valor predeterminado para la columna y estaadmite valores NULL, se inserta NULL. DEFAULT no es un valor válido para una columna de identidad. Cuando se especifica en un constructor con valores de

tabla, DEFAULT solo se permite en una instrucción INSERT.

expression

Es una constante, variable o expresión. La expresión no puede contener una instrucción EXECUTE.

Limitaciones y restricciones

Los constructores con valores de tabla pueden utilizarse de una de dos maneras: directamente en la lista VALUES de una instrucción INSERT … VALUES o como unatabla derivada donde las tablas derivadas están permitidas. El número máximo de filas que pueden construirse insertando filas directamente en la lista de valores es1000. Se devuelve el error 10738 si el número de filas es superior a 1000 en ese caso. Para insertar más de 1000 filas, use uno de los métodos siguientes:

Crear varias instrucciones INSERT

Usar una tabla derivada

Importar masivamente los datos mediante la utilidad de bcp o la instrucción BULK INSERT

Como expresión de valores de fila solo se permiten valores escalares. Como expresión de valores de fila no se permiten las subconsultas que impliquen a variascolumnas. Por ejemplo, el código siguiente produce un error de sintaxis porque la tercera lista de expresiones de valores de fila contiene una subconsulta con variascolumnas.

Sin embargo, la instrucción se puede reescribir especificando cada columna en la subconsulta independientemente. El ejemplo siguiente inserta correctamente tres filas

en la tabla MyProducts.

SQL Server 2014

VALUES ( <row value expression list> ) [ ,...n ]

<row value expression list> ::=

{<row value expression> } [ ,...n ]

<row value expression> ::=

{ DEFAULT | NULL | expression }


GO

CREATE TABLE dbo.MyProducts (Name varchar(50), ListPrice money);

GO

-- This statement fails because the third values list contains multiple columns in the subquery.

INSERT INTO dbo.MyProducts (Name, ListPrice)

VALUES ('Helmet', 25.50),

('Wheel', 30.00),

(SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);

GO




Tipos de datos

Los valores especificados en una instrucción INSERT de varias filas siguen las propiedades de conversión de tipos de datos de la sintaxis de UNION ALL. Esto produce

la conversión implícita de tipos no coincidentes al tipo de precedencia superior. Si la conversión no es una conversión implícita admitida, se devuelve un error. Por

ejemplo, la instrucción siguiente inserta un valor entero y un valor de carácter en una columna de tipo char.

Cuando se ejecuta la instrucción INSERT, SQL Server intenta convertir 'a' en un entero porque la precedencia del tipo de datos indica que un entero pertenece a un tipomás alto que un carácter. Se produce un error en la conversión y se devuelve un error. Para evitar este error, puede convertir explícitamente los valores segúncorresponda. Por ejemplo, la instrucción anterior puede escribirse del siguiente modo.

Ejemplos

A.Insertar varias filas de datos

En el siguiente ejemplo se crea la tabla dbo.Departments y, a continuación, se utiliza el constructor de valor de tabla para insertar cinco filas en la tabla. Dado que los

valores para todas las columnas se suministran e incluyen en el mismo orden que las columnas de la tabla, no es necesario especificar los nombres de columna en la

lista de columnas.

B.Insertar varias filas con los valores DEFAULT y NULL

En el siguiente ejemplo se demuestra cómo especificar DEFAULT y NULL cuando se utiliza el constructor de valor de tabla para insertar filas en una tabla.

C.Especificar varios valores como una tabla derivada en una cláusula FROMEn los siguientes ejemplos se utiliza el constructor con valores de tabla para especificar varios valores en la cláusula FROM de una instrucción SELECT.

D.Especificar varios valores como una tabla de origen derivada en una instrucción MERGE

INSERT INTO dbo.MyProducts (Name, ListPrice)

VALUES ('Helmet', 25.50),

('Wheel', 30.00),

((SELECT Name FROM Production.Product WHERE ProductID = 720),

(SELECT ListPrice FROM Production.Product WHERE ProductID = 720));

GO

CREATE TABLE dbo.t (a int, b char);

GO

INSERT INTO dbo.t VALUES (1,'a'), (2, 1);

GO

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));


GO

INSERT INTO Production.UnitMeasure

VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');

GO


GO

CREATE TABLE Sales.MySalesReason(

SalesReasonID int IDENTITY(1,1) NOT NULL,

Name dbo.Name NULL ,

ReasonType dbo.Name NOT NULL DEFAULT 'Not Applicable' );

GO

INSERT INTO Sales.MySalesReason

VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');

SELECT * FROM Sales.MySalesReason;

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

GO

-- Used in an inner join to specify values to return.

SELECT ProductID, a.Name, Color

FROM Production.Product AS a

INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)

ON a.Name = b.Name;


En el ejemplo siguiente se usa MERGE para modificar la tabla SalesReason, actualizando o insertando las filas. Cuando el valor de NewName de la tabla de origen

coincide con un valor de la columna Name de la tabla de destino, (SalesReason), la columna ReasonType se actualiza en la tabla de destino. Cuando el valor de NewName

no coincide, la fila del origen se inserta en la tabla de destino. La tabla de origen es una tabla derivada que usa la característica de constructor con valores de tabla deTransact-SQL para especificar varias filas en la tabla de origen.

Vea también

ReferenciaINSERT (Transact-SQL)


FROM (Transact-SQL)

© 2014 Microsoft


GO

-- Create a temporary table variable to hold the output actions.

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target

USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))

AS Source (NewName, NewReasonType)

ON Target.Name = Source.NewName

WHEN MATCHED THEN

UPDATE SET ReasonType = Source.NewReasonType

WHEN NOT MATCHED BY TARGET THEN

INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.

SELECT Change, COUNT(*) AS CountPerChange

FROM @SummaryOfChanges

GROUP BY Change;




TOP (Transact-SQL)

Limita las filas devueltas en un conjunto de resultados de la consulta a un número o porcentaje de filas especificado en SQL Server 2014. Cuando se usa TOP junto con la

cláusula ORDER BY, el conjunto de resultados se limita al primer número N de filas ordenadas; de lo contrario, devuelve el primer número N de filas en un orden sin

definir. Utilice esta cláusula para especificar el número de filas devueltas de una instrucción SELECT o afectadas por una instrucción INSERT, UPDATE, MERGE o DELETE.



Sintaxis

Argumentos

expression

Es la expresión numérica válida que especifica el número de filas que se van a devolver. Si se especifica PERCENT, expression se convierte implícitamente a unvalor float; en caso contrario, se convierte en bigint.

PERCENT

Indica que la consulta devuelve sólo el primer porcentaje de filas de expression del conjunto de resultados. Los valores fraccionarios se redondean al siguiente

valor entero.

WITH TIES

Se usa cuando se desean devolver dos o más filas que ocupan el último lugar en el conjunto de resultados limitado. Se debe usar con la cláusula ORDER BY.

WITH TIES puede hacer que se devuelvan más filas que el valor especificado en expression. Por ejemplo, si expression está establecido en 5, pero 2 filasadicionales coinciden con los valores de las columnas ORDER BY en la fila 5, el conjunto de resultados contendrá 7 filas.

TOP...WITH TIES solo se puede especificar en instrucciones SELECT y siempre que se haya especificado una cláusula ORDER BY. El orden devuelto de los registros

enlazados es arbitrario. ORDER BY no afecta a esta regla.

Prácticas recomendadas

En una instrucción SELECT, utilice siempre una cláusula ORDER BY con la cláusula TOP. Esta es la única manera de indicar previsiblemente a qué filas afecta TOP.

Utilice OFFSET y FETCH en la cláusula ORDER BY en lugar de la cláusula TOP para implementar una solución de paginación de consulta. Una solución de paginación ﴾esdecir, el envío de fragmentos o "páginas" de datos al cliente﴿ es más fácil de implementar mediante OFFSET y FETCH. Para obtener más información, consulte ORDER BY

﴾cláusula de Transact‐SQL﴿.

Utilice TOP ﴾o bien, OFFSET y FETCH﴿ en lugar de SET ROWCOUNT para limitar el número de filas devueltas. Estos métodos son preferibles a utilizar SET ROWCOUNTpor las siguientes razones:

Como parte de una instrucción SELECT, el optimizador de consultas puede considerar el valor de expression en las cláusulas TOP o FETCH durante la optimizaciónde la consulta. Dado que SET ROWCOUNT se usa fuera de una instrucción que ejecuta una consulta, su valor no se puede considerar en un plan de consulta.

Soporte de compatibilidad

Por compatibilidad con versiones anteriores, los paréntesis son opcionales en las instrucciones SELECT. Se recomienda utilizar siempre los paréntesis para TOP en lasinstrucciones SELECT por coherencia con su uso necesario en las instrucciones INSERT, UPDATE, MERGE y DELETE, en las que se requieren los paréntesis.

Interoperabilidad

La expresión TOP no afecta a las instrucciones que se pueden ejecutar debido a un desencadenador. Las tablas inserted y deleted en los desencadenadores solo

mostrarán las filas verdaderamente afectadas por las instrucciones INSERT, UPDATE, MERGE o DELETE. Por ejemplo, si INSERT TRIGGER se desencadena como

resultado de una instrucción INSERT que utilizó una cláusula TOP,

SQL Server permite actualizar las filas a través de las vistas. Dado que la cláusula TOP puede estar incluida en la definición de vista, es posible que algunas filasdesaparezcan de la vista a causa de una actualización si las filas ya no cumplen con los requisitos de la expresión TOP.

Cuando se especifica en la instrucción MERGE, la cláusula TOP se aplica después de que se combinen la tabla de origen completa y la tabla de destino completa y de

que se quiten las filas combinadas que no certifican las acciones de inserción, actualización o eliminación. La cláusula TOP reduce aún más el número de filas

SQL Server 2014

[

TOP (expression) [PERCENT]

[ WITH TIES ]

]





combinadas al valor especificado y se aplican las acciones de inserción, actualización o eliminación a las filas combinadas restantes de una manera desordenada. Es

decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN. Por ejemplo, si especificar TOP (10) afecta a 10 filas; de

estas filas, se pueden actualizar 7 e insertar 3, o se pueden eliminar 1, actualizar 5 e insertar 4, etc. Dado que la instrucción MERGE realiza exámenes de tablacompletos de ambas tablas, de destino y de origen, el rendimiento de E/S puede verse afectado al utilizar la cláusula TOP para modificar una tabla grande mediante lacreación de varios lotes. En este escenario, es importante asegurase de que todos los lotes sucesivos tengan como destino nuevas filas.

Tenga precaución al especificar la cláusula TOP en una consulta que contiene un operador UNION, UNION ALL, EXCEPT o INTERSECT. Es posible escribir una consulta

que devuelva resultados inesperados porque el orden en el que se procesan lógicamente las cláusulas TOP y ORDER BY no siempre es intuitivo cuando estosoperadores se utilizan en una operación Select. Por ejemplo, dados los siguientes datos y la siguiente tabla, suponga que desea devolver el coche rojo menos caro y el

coche azul menos caro. Es decir, el sedán rojo y la camioneta azul.

Para lograr estos resultados, podría escribir la siguiente consulta.

El conjunto de resultados es el siguiente.

Model Color Price

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

sedan red 10000.00

convertible blue 15000.00

Se devuelven resultados inesperados porque la cláusula TOP se ejecuta lógicamente antes que la cláusula ORDER BY, que ordena los resultados del operador ﴾UNIONALL en este caso). Así, la consulta anterior devuelve cualquier coche rojo y cualquier coche azul y, a continuación, ordena el resultado de esa unión por el precio. En el

siguiente ejemplo se muestra el método correcto de escribir esta consulta para lograr el resultado deseado.

Utilizando TOP y ORDER BY en una operación de subselección, se puede asegurar de que los resultados de la cláusula ORDER BY que se utilizan se apliquen a lacláusula TOP y no a ordenar el resultado de la operación UNION.

El conjunto de resultados es el siguiente.

Model Color Price

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

sedan red 10000.00

van blue 8000.00

Limitaciones y restricciones

Cuando se utiliza TOP con INSERT, UPDATE, MERGE o DELETE, las filas a las que hace referencia no están organizadas de ninguna manera y la cláusula ORDER BY no sepuede especificar directamente en estas instrucciones. Si necesita usar TOP para insertar, eliminar o modificar las filas en un orden cronológico significativo, debeutilizar TOP junto con una cláusula ORDER BY que se especifica en una instrucción de subselección. Consulte la sección Ejemplos siguiente en este tema.

TOP no se puede usar en las instrucciones UPDATE y DELETE en vistas con particiones.

TOP no se puede combinar con OFFSET y FETCH en la misma expresión de consulta ﴾en el mismo ámbito de la consulta﴿. Para obtener más información, vea ORDER BY

﴾cláusula de Transact‐SQL﴿.

CREATE TABLE dbo.Cars(Model varchar(15), Price money, Color varchar(10));

INSERT dbo.Cars VALUES

('sedan', 10000, 'red'), ('convertible', 15000, 'blue'),

('coupe', 20000, 'red'), ('van', 8000, 'blue');

SELECT TOP(1) Model, Color, Price

FROM dbo.Cars

WHERE Color = 'red'

UNION ALL

SELECT TOP(1) Model, Color, Price

FROM dbo.Cars

WHERE Color = 'blue'

ORDER BY Price ASC;

SELECT Model, Color, Price

FROM (SELECT TOP(1) Model, Color, Price

FROM dbo.Cars

WHERE Color = 'red'

ORDER BY Price ASC) AS a

UNION ALL

SELECT Model, Color, Price

FROM (SELECT TOP(1) Model, Color, Price

FROM dbo.Cars

WHERE Color = 'blue'

ORDER BY Price ASC) AS b;


Ejemplos

Categoría Elementos de sintaxis ofrecidos

Sintaxis básica TOP • PERCENT

Incluir valores equivalentes WITH TIES

Limitar las filas afectadas por DELETE, INSERT o UPDATE DELETE • INSERT • UPDATE

Sintaxis básicaEn los ejemplos de esta sección se muestra la funcionalidad básica de la cláusula ORDER BY con la sintaxis mínima requerida.

A.Utilizar TOP con un valor constante

En los siguientes ejemplos se utiliza un valor constante para especificar el número de empleados que se devuelven en el conjunto de resultados de la consulta. En el

primer ejemplo, se devuelven las 10 primeras filas sin definir porque no se usa una cláusula ORDER BY. En el segundo ejemplo, se utiliza una cláusula ORDER BY paradevolver los primeros 10 empleados contratados recientemente.

B.Usar TOP con una variable

En el siguiente ejemplo se utiliza una variable para especificar el número de empleados que se devuelven en el conjunto de resultados de la consulta.

C.Especificar un porcentaje

En el siguiente ejemplo se utiliza PERCENT para especificar el número de empleados que se devuelven en el conjunto de resultados de la consulta. Hay 290 empleados

en la tabla HumanResources.Employee. Dado que el 5 por ciento de 290 es un valor fraccionario, el valor se redondea al número entero siguiente.

Incluir valores equivalentes

A.Utilizar WITH TIES para incluir las filas que coinciden con los valores de la última filaEn el ejemplo siguiente se obtiene el primer 10 por ciento de los empleados que tienen los salarios más altos y se devuelven en orden descendente de acuerdo con susalario. La especificación de WITH TIES garantiza que también se incluyan en el conjunto de resultados los empleados con salarios iguales al salario más bajo devuelto﴾la última fila﴿, aun cuando esto exceda el 10 por ciento de los empleados.

Limitar las filas afectadas por DELETE, INSERT o UPDATE

A.Utilizar TOP para limitar el número de filas eliminadas


GO

-- Select the first 10 random employees.

SELECT TOP(10)JobTitle, HireDate


GO

-- Select the first 10 employees hired most recently.

SELECT TOP(10)JobTitle, HireDate

FROM HumanResources.Employee

ORDER BY HireDate DESC;


GO

DECLARE @p AS int = 10;

SELECT TOP(@p)JobTitle, HireDate, VacationHours


ORDER BY VacationHours DESC

GO


GO

SELECT TOP(5)PERCENT JobTitle, HireDate


ORDER BY HireDate DESC;


GO

SELECT TOP(10)WITH TIES

pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate

FROM Person.Person AS pp

INNER JOIN HumanResources.Employee AS e

ON pp.BusinessEntityID = e.BusinessEntityID

INNER JOIN HumanResources.EmployeePayHistory AS r

ON r.BusinessEntityID = e.BusinessEntityID

ORDER BY Rate DESC;

Cuando se usa una cláusula TOP ﴾n﴿ con DELETE, la operación de eliminación se realiza en una selección sin definir de n número de filas. Es decir, la instrucción DELETEelige cualquier número ﴾n﴿ de filas que cumplen los criterios definidos en la cláusula WHERE. En el ejemplo siguiente se eliminan 20 filas de la tabla

PurchaseOrderDetail cuyas fechas de vencimiento son anteriores al 1 de julio de 2002.

Si necesita utilizar TOP para eliminar filas por un orden cronológico significativo, debe utilizarla junto con ORDER BY en una instrucción de subselección. La siguiente

consulta elimina de la tabla PurchaseOrderDetail las 10 filas con las fechas de vencimiento más antiguas. Para garantizar que solo se eliminen 10 filas, la columna

especificada en la instrucción de subselección ﴾PurchaseOrderID) es la clave principal de la tabla. El uso de una columna sin clave en la instrucción de subselecciónpodría causar la eliminación de más de 10 filas si la columna especificada contiene valores duplicados.

B.Utilizar TOP para limitar el número de filas insertadasEn el ejemplo siguiente se crea la tabla EmployeeSales y se inserta el nombre y los datos de ventas del año hasta la fecha para los 5 primeros empleados de la tablaHumanResources.Employee. La instrucción INSERT elige 5 filas cualesquiera devuelvas por la instrucción SELECT que cumplen los criterios definidos en la cláusulaWHERE. La cláusula OUTPUT muestra las filas que se insertan en la tabla EmployeeSales. Observe que la cláusula ORDER BY de la instrucción SELECT no se utiliza paradeterminar los primeros 5 empleados.

Si necesita usar TOP para insertar las filas en un orden cronológico significativo, debe utilizar TOP junto con ORDER BY en una instrucción de subselección, tal y como semuestra en el siguiente ejemplo. La cláusula OUTPUT muestra las filas que se insertan en la tabla EmployeeSales. Observe que los 5 primeros empleados se insertan

ahora según los resultados de la cláusula ORDER BY en lugar de las filas sin definir.

C.Utilizar TOP para limitar el número de filas actualizadasEn el ejemplo siguiente se usa la cláusula TOP para actualizar filas de una tabla. Cuando se usa una cláusula TOP ﴾n﴿ con UPDATE, la operación de actualización serealiza sobre un número sin definir de filas. Es decir, la instrucción UPDATE elige cualquier número ﴾n﴿ de filas que cumplen los criterios definidos en la cláusula WHERE.En el ejemplo siguiente se asignan 10 clientes de un vendedor a otro.


GO

DELETE TOP (20)

FROM Purchasing.PurchaseOrderDetail

WHERE DueDate < '20020701';

GO


GO

DELETE FROM Purchasing.PurchaseOrderDetail

WHERE PurchaseOrderDetailID IN

(SELECT TOP 10 PurchaseOrderDetailID

FROM Purchasing.PurchaseOrderDetail

ORDER BY DueDate ASC);

GO


GO

IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeSales;

GO

CREATE TABLE dbo.EmployeeSales

( EmployeeID nvarchar(11) NOT NULL,



YearlySales money NOT NULL

);

GO

INSERT TOP(5)INTO dbo.EmployeeSales

OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales

SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD




WHERE sp.SalesYTD > 250000.00

ORDER BY sp.SalesYTD DESC;

INSERT INTO dbo.EmployeeSales

OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales

SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD




WHERE sp.SalesYTD > 250000.00

ORDER BY sp.SalesYTD DESC;


UPDATE TOP (10) Sales.Store

SET SalesPersonID = 276

Si necesita usar TOP para aplicar actualizaciones según un orden cronológico significativo, debe usar TOP junto con ORDER BY en una instrucción de subselección. En el

siguiente ejemplo se actualizan las horas de vacaciones de los 10 empleados cuyas fechas de alta son más antiguas.

Vea también

ReferenciaSELECT (Transact-SQL)




ORDER BY ﴾cláusula de Transact‐SQL﴿SET ROWCOUNT (Transact-SQL)


© 2014 Microsoft

WHERE SalesPersonID = 275;

GO

UPDATE HumanResources.Employee

SET VacationHours = VacationHours + 8

FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee

ORDER BY HireDate ASC) AS th

WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;

GO









Actualiza un campo text, ntext o image existente. Utilice UPDATETEXT solo para cambiar una parte de una columna text, ntext o image existente. Utilice WRITETEXT para

actualizar y reemplazar un campo text, ntext o image completo.

Importante

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificarlas aplicaciones que actualmente la utilizan. Utilice los tipos de datos de valores grandes y la cláusula .WRITE de la instrucción UPDATE, en su lugar.



Sintaxis

Argumentos

BULK

Hace posible que las herramientas de carga carguen flujos de datos binarios. La herramienta debe proporcionar flujos en el nivel de protocolo TDS. Cuando el

flujo de datos no esté presente el procesador de consultas omite la opción BULK.

Importante

Recomendamos que la opción BULK no se utilice en las aplicaciones basadas en SQL Server. Es posible que esta opción se modifique o quite en versionesfuturas de SQL Server.

table_name . dest_column_name

Es el nombre de la tabla y la columna text, ntext o image que se actualizarán. Los nombres de las tablas y de las columnas se deben ajustar a las reglas para

los identificadores. La especificación de los nombres de la base de datos y del propietario es opcional.

dest_text_ptr

Es el valor de un puntero de texto ﴾devuelto por la función TEXTPTR﴿ que señala a los datos text, ntext o image que se deben actualizar. dest_text_ptr debe ser

binary(16).

insert_offset

Es la posición de inicio de la actualización a partir de cero. En columnas text o image, insert_offset es el número de bytes que se debe omitir desde el principiode la columna existente antes de insertar nuevos datos. En columnas ntext, insert_offsetes el número de caracteres ﴾cada carácter ntext usa 2 bytes). Los datos

text, ntext o image existentes que empiecen en esta posición de inicio a partir de cero se desplazan a la derecha para dejar espacio a los nuevos datos. Un

valor 0 inserta los nuevos datos al principio de los datos existentes. Un valor NULL anexa los nuevos datos al valor de datos existente.

delete_length

Es la longitud de los datos que se van a eliminar de la columna text, ntext o image existente, a partir de la posición de insert_offset. El valor delete_lengthse

especifica en bytes para columnas text e image, y en caracteres para columnas ntext. Cada carácter ntext utiliza 2 bytes. Con un valor 0 no se eliminan datos.

Con un valor NULL, se eliminan todos los datos desde la posición de insert_offset hasta el final de la columna text o image existente.

WITH LOG

El registro está determinado por el modelo de recuperación vigente para la base de datos.

inserted_data

Son los datos que se van a insertar en la columna text, ntext o image existente en la ubicación de insert_offset. Es un único valor de tipo char, nchar, varchar,

nvarchar, binary, varbinary, text, ntext o image. inserted_data puede ser un literal o una variable.

table_name.src_column_name

Es el nombre de la tabla y de la columna text, ntext o image que se utiliza como origen de los datos insertados. Los nombres de las tablas y de las columnas

se deben ajustar a las reglas para los identificadores.

src_text_ptr

Es un valor de puntero de texto ﴾devuelto por la función TEXTPTR﴿ que señala a una columna text, ntext o image utilizada como origen de los datos insertados.

Nota

El valor de scr_text_ptrno debe ser igual que el valor de dest_text_ptr.

Comentarios

SQL Server 2014

UPDATETEXT [BULK] { table_name.dest_column_name dest_text_ptr }

{ NULL | insert_offset }

{ NULL | delete_length }

[ WITH LOG ]

[ inserted_data

| { table_name.src_column_name src_text_ptr } ]





Los datos recién insertados pueden ser una única constante inserted_data, un nombre de tabla, un nombre de columna o un puntero de texto.

Acción de actualización Parámetros de UPDATETEXT

Para sustituir los datos

existentes

Especifique un valor de insert_offset que no sea NULL, un valor de delete_length que no sea cero y los nuevos datos que se deban

insertar.

Para eliminar datos existentes Especifique un valor insert_offset que no sea NULL y un valor de delete_length distinto de cero. No especifique nuevos datos para la

inserción.

Para insertar nuevos datos Especifique el valor de insert_offset, un valor 0 en delete_length y los nuevos datos que se deban insertar.

Para aumentar el rendimiento, se recomienda insertar o actualizar datos de tipo text, ntext e image en tamaños de fragmentos que sean múltiplos de 8.040 bytes.

En SQL Server, es posible que existan punteros de texto consecutivos para datos de tipo text, ntext o image pero que no sean válidos. Para obtener informaciónacerca de la opción text in row, vea sp_tableoption (Transact-SQL). Para obtener más información sobre cómo invalidar punteros de texto, vea sp_invalidate_textptr

(Transact-SQL).

Para inicializar columnas text en NULL, use WRITETEXT; UPDATETEXT inicializa columnas text en una cadena vacía.

Permisos

Requiere el permiso UPDATE en la base de datos especificada.

Ejemplos

En el ejemplo siguiente se coloca el puntero de texto en la variable local @ptrval; a continuación, se utiliza UPDATETEXT para actualizar un error de ortografía.

Nota

Para ejecutar este ejemplo, debe instalar la base de datos pubs.

Vea también

ReferenciaREADTEXT (Transact-SQL)

TEXTPTR (Transact-SQL)


© 2014 Microsoft

USE pubs;

GO

ALTER DATABASE pubs SET RECOVERY SIMPLE;

GO

DECLARE @ptrval binary(16);


FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id


UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';

GO

ALTER DATABASE pubs SET RECOVERY FULL;

GO






WITH common_table_expression (Transact-SQL)

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común ﴾CTE﴿. Se deriva de una consulta simple y se define en el ámbito deejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucciónSELECT que la define. Una expresión de tabla común puede incluir referencias a ella misma. Esto se conoce como expresión de tabla común recursiva.



Sintaxis

Argumentos

expression_name

Es un identificador válido de la expresión de tabla común. expression_name debe ser diferente del nombre de cualquier otra expresión de tabla común definidaen la misma cláusula WITH <common_table_expression>, pero expression_name puede coincidir con el nombre de una vista o tabla base. Cualquier referencia a

expression_name en la consulta utiliza la expresión de tabla común y no el objeto base.

column_name

Especifica un nombre de columna en la expresión de tabla común. No se permiten nombres duplicados en una misma definición de CTE. El número de nombresde columna especificado debe coincidir con el número de columnas del conjunto de resultados de CTE_query_definition. La lista de nombres de columna es

opcional solamente si en la definición de la consulta se suministran nombres diferentes para todas las columnas resultantes.

CTE_query_definition

Especifica una instrucción SELECT cuyo conjunto de resultados llena la expresión de tabla común. La instrucción SELECT de CTE_query_definition debe cumplir los

mismos requisitos que en la creación de una vista, excepto que una expresión CTE no puede definir otra expresión CTE. Para obtener más información, vea lasección Comentarios y el tema CREATE VIEW (Transact-SQL).

Si se definen varios parámetros CTE_query_definition, las definiciones de consulta deben combinarse mediante uno de estos operadores de conjunto: UNION ALL,

UNION, EXCEPT o INTERSECT.

Comentarios

Instrucciones para crear y utilizar expresiones de tabla comunes

Las instrucciones siguientes se aplican a expresiones de tabla comunes no recursivas. Para obtener instrucciones que se aplican a expresiones de tabla comunes

recursivas, vea "Instrucciones para definir y usar expresiones de tabla comunes recursivas" más adelante.

Una expresión CTE debe ir seguida de una única instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a una parte o a la totalidad de suscolumnas. Una expresión CTE también se puede especificar en una instrucción CREATE VIEW como parte de la instrucción SELECT de definición de la vista.

Se pueden especificar varias definiciones de consulta de CTE en una CTE no recursiva. Las definiciones deben combinarse mediante uno de estos operadores de

conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.

Una expresión CTE puede hacer referencia a ella misma y a otras expresiones CTE previamente definidas en la misma cláusula WITH. No se permite la referencia

adelantada.

No se permite especificar más de una cláusula WITH en una expresión CTE. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, esta no

puede contener ninguna cláusula WITH anidada que defina otra expresión CTE.

No se pueden utilizar las cláusulas siguientes en la definición de CTE_query_definition:

ORDER BY ﴾excepto cuando se especifica una cláusula TOP﴿

INTO

Cláusula OPTION con sugerencias de consulta

FOR BROWSE

Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.

Una consulta que haga referencia a una CTE se puede utilizar para definir un cursor.

SQL Server 2014

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=

expression_name [ ( column_name [ ,...n ] ) ]

AS

( CTE_query_definition )





En la expresión CTE se puede hacer referencia a tablas de servidores remotos.

Cuando se ejecuta una CTE, todas las sugerencias que hagan referencia a ella pueden entrar en conflicto con otras sugerencias detectadas cuando la CTE tiene

acceso a sus tablas subyacentes, de la misma manera que las sugerencias que hacen referencia a vistas en las consultas. En ese caso, la consulta devuelve un

error.

Instrucciones para definir y usar expresiones de tabla comunes recursivas

Las instrucciones siguientes se aplican a la definición de una expresión de tabla común recursiva:

La definición de la CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro no recursivo y un miembro recursivo. Se pueden

definir varios miembros no recursivos y recursivos, aunque todas las definiciones de consultas de miembros no recursivos deben colocarse delante de la

primera definición de miembro recursivo. Todas las definiciones de consulta de CTE son miembros no recursivos a menos que hagan referencia a la propia CTE.

Los miembros no recursivos deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL es el

único operador de conjuntos permitido entre el último miembro no recursivo y el primer miembro recursivo, y si se combinan varios miembros recursivos.

El número de columnas de los miembros no recursivo y recursivo debe coincidir.

El tipo de datos de una columna del miembro recursivo debe ser igual al tipo de datos de la columna correspondiente en el miembro no recursivo.

La cláusula FROM de un miembro recursivo solo debe hacer referencia una vez a expression_name de CTE.

No se permiten los siguientes elementos en el parámetro CTE_query_definition de un miembro recursivo:

SELECT DISTINCT

GROUP BY

PIVOT (cuando el nivel de compatibilidad de la base de datos sea 110 o superior. Vea Cambios recientes en las características del Motor de base dedatos de SQL Server 2014).

HAVING

Agregación escalar

TOP

LEFT, RIGHT, OUTER JOIN (se permite INNER JOIN)

Subconsultas

Una sugerencia aplicada a una referencia recursiva a una CTE dentro de CTE_query_definition.

Las instrucciones siguientes se aplican al uso de una expresión de tabla común recursiva:

Todas las columnas devueltas por la expresión CTE recursiva aceptan valores NULL independientemente de la nulabilidad de las columnas devueltas por lasinstrucciones SELECT participantes.

Una expresión CTE formada incorrectamente puede generar un bucle infinito. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve losmismos valores para las columnas primarias y secundarias, se crea un bucle infinito. Para evitar que se genere un bucle infinito, se puede limitar el número deniveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusulaOPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva elproblema de código que genera el bucle. El valor predeterminado de todo el servidor es 100. Cuando se especifica 0, no se aplica ningún límite. Solo se puede

especificar un valor de MAXRECURSION por instrucción. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

No se puede utilizar una vista que contenga una expresión de tabla común recursiva para actualizar datos.

Se pueden definir cursores en las consultas que utilicen expresiones CTE. La expresión CTE es el argumento de select_statement que define el conjunto de

resultados del cursor. En el caso de las CTE recursivas únicamente se permiten los cursores de solo avance rápido y estáticos ﴾de instantánea﴿. Si se especifica

otro tipo de cursor en una CTE recursiva, el tipo de cursor se convierte a estático.

En la expresión CTE se puede hacer referencia a tablas de servidores remotos. Si se hace referencia al servidor remoto en el miembro recursivo de la CTE, se

crea una cola para cada tabla remota de manera que se pueda tener acceso local a las tablas repetidas veces. Si es una consulta de CTE, aparecerá IndexSpool/Lazy Spools en el plan de consulta y tendrá el predicado adicional WITH STACK. Esta es una forma de confirmar la recursividad apropiada.

Las funciones analíticas y de agregado de la parte recursiva del CTE se aplican al conjunto para el nivel de recursividad actual y no al conjunto para el CTE. Las

funciones como ROW_NUMBER solo funcionan sobre el subconjunto de datos que les pasa el nivel de recursividad actual y no sobre todo el conjunto de datos

pasados a la parte recursiva de la CTE. Para obtener más información, vea el ejemplo K. Usar funciones analíticas en una CTE recursiva que sigue.

Ejemplos

A.Crear una expresión de tabla común simpleEn el siguiente ejemplo se muestra el número total de pedidos de venta por año para cada representante de ventas en Adventure Works Cycles.

-- Define the CTE expression name and column list.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)

AS

-- Define the CTE query.



B.Usar una expresión de tabla común para limitar recuentos y promedios de informesEn el siguiente ejemplo se muestra el número medio de pedidos de venta correspondiente a todos los años para los representantes de ventas.

C.Usar varias definiciones de CTE en una sola consulta

En el ejemplo siguiente se muestra cómo definir más de una CTE en una sola consulta. Observe que se usa una coma para separar las definiciones de consulta CTE. La

función FORMAT, utilizada para mostrar las cantidades de moneda en un formato de moneda, está disponible en SQL Server 2012 y versiones posteriores.

A continuación se muestra un conjunto de resultados parcial.

D.Usar una expresión de tabla común recursiva para mostrar varios niveles de recursividad

(

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

)

-- Define the outer query referencing the CTE name.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders)

AS

(

SELECT SalesPersonID, COUNT(*)



GROUP BY SalesPersonID

)

SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"

FROM Sales_CTE;

GO

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)

AS

-- Define the first CTE query.

(

SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear



GROUP BY SalesPersonID, YEAR(OrderDate)

)

, -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.

Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)

AS

(

SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear

FROM Sales.SalesPersonQuotaHistory

GROUP BY BusinessEntityID, YEAR(QuotaDate)

)

-- Define the outer query by referencing columns from both CTEs.

SELECT SalesPersonID

, SalesYear

, FORMAT(TotalSales,'C','en-us') AS TotalSales

, SalesQuotaYear

, FORMAT (SalesQuota,'C','en-us') AS SalesQuota

, FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota

FROM Sales_CTE

JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID

AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear

ORDER BY SalesPersonID, SalesYear;

GO

SalesPersonID SalesYear TotalSales SalesQuotaYear SalesQuota Amt_Above_or_Below_Quota

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

274 2005 $32,567.92 2005 $35,000.00 ($2,432.08)

274 2006 $406,620.07 2006 $455,000.00 ($48,379.93)

274 2007 $515,622.91 2007 $544,000.00 ($28,377.09)

274 2008 $281,123.55 2008 $271,000.00 $10,123.55

En el ejemplo siguiente se muestra la lista jerárquica de los directivos y de los empleados que tienen a su cargo. En el ejemplo se empieza creando y rellenando la

tabla dbo.MyEmployees.

E.Usar una expresión de tabla común recursiva para mostrar dos niveles de recursividadEn el ejemplo siguiente se muestran los directivos y los empleados que tienen a su cargo. El número de niveles devueltos está limitado a dos.

F.Usar una expresión de tabla común recursiva para mostrar una lista jerárquicaEl ejemplo siguiente, que está basado en el ejemplo D, agrega los nombres del directivo y de los empleados, y sus cargos respectivos. La jerarquía de directivos yempleados se resalta más mediante la aplicación de sangrías a cada nivel.

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees

(

EmployeeID smallint NOT NULL,



Title nvarchar(50) NOT NULL,

DeptID smallint NOT NULL,

ManagerID int NULL,

CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES

(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL),(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel

FROM dbo.MyEmployees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

ORDER BY ManagerID;

GO


GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel


WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1


INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

WHERE EmployeeLevel <= 2 ;

GO


GO

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)

AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),

e.Title,

e.EmployeeID,

G.Usar MAXRECURSION para cancelar una instrucciónMAXRECURSION se puede utilizar para impedir que una CTE recursiva con formato incorrecto entre en un bucle infinito. En el ejemplo siguiente se crea un bucle infinito

intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.

Después de corregir el error de código, ya no se requiere MAXRECURSION. En el siguiente ejemplo se muestra el código corregido.

H.Usar una expresión de tabla común para recorrer selectivamente y paso a paso una relación recursiva en una instrucción SELECTEn el ejemplo siguiente se muestra la jerarquía de ensamblados y componentes de producto necesarios para fabricar la bicicleta correspondiente aProductAssemblyID = 800.

1,

CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)


WHERE e.ManagerID IS NULL

UNION ALL

SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +

e.FirstName + ' ' + e.LastName),

e.Title,

e.EmployeeID,

EmployeeLevel + 1,

CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

LastName)


JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM DirectReports

ORDER BY Sort;

GO


GO

--Creates an infinite loop

WITH cte (EmployeeID, ManagerID, Title) as

(

SELECT EmployeeID, ManagerID, Title


WHERE ManagerID IS NOT NULL

UNION ALL

SELECT cte.EmployeeID, cte.ManagerID, cte.Title

FROM cte

JOIN dbo.MyEmployees AS e

ON cte.ManagerID = e.EmployeeID

)

--Uses MAXRECURSION to limit the recursive levels to 2


FROM cte

OPTION (MAXRECURSION 2);

GO


GO

WITH cte (EmployeeID, ManagerID, Title)

AS

(



WHERE ManagerID IS NOT NULL

UNION ALL

SELECT e.EmployeeID, e.ManagerID, e.Title


JOIN cte ON e.ManagerID = cte.EmployeeID

)


FROM cte;

GO


GO

WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS

(

SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,

b.EndDate, 0 AS ComponentLevel

FROM Production.BillOfMaterials AS b

WHERE b.ProductAssemblyID = 800

AND b.EndDate IS NULL

UNION ALL

SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,

I.Usar una CTE recursiva en una instrucción UPDATEEn el siguiente ejemplo se actualiza el valor de PerAssemblyQty para todos los componentes que se utilizan para fabricar el producto 'Road-550-W Yellow, 44'

(ProductAssemblyID800). La expresión de tabla común devuelve una lista jerárquica de los elementos que se utilizan para fabricar ProductAssemblyID 800 y los

componentes que se utilizan para crear esos elementos, etc. Solo se modifican las filas devueltas por la expresión de tabla común.

J.Usar varios miembros no recursivos y recursivos

En el ejemplo siguiente se utilizan varios miembros no recursivos y recursivos para devolver todos los antecesores de una persona especificada. Se crea una tabla y se

insertan valores en ella para establecer la genealogía familiar devuelta por la CTE recursiva.

bom.EndDate, ComponentLevel + 1

FROM Production.BillOfMaterials AS bom

INNER JOIN Parts AS p

ON bom.ProductAssemblyID = p.ComponentID

AND bom.EndDate IS NULL

)

SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,

ComponentLevel

FROM Parts AS p

INNER JOIN Production.Product AS pr

ON p.ComponentID = pr.ProductID

ORDER BY ComponentLevel, AssemblyID, ComponentID;

GO


GO

WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS

(

SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,

b.EndDate, 0 AS ComponentLevel

FROM Production.BillOfMaterials AS b

WHERE b.ProductAssemblyID = 800

AND b.EndDate IS NULL

UNION ALL

SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,

bom.EndDate, ComponentLevel + 1

FROM Production.BillOfMaterials AS bom

INNER JOIN Parts AS p

ON bom.ProductAssemblyID = p.ComponentID

AND bom.EndDate IS NULL

)

UPDATE Production.BillOfMaterials

SET PerAssemblyQty = c.PerAssemblyQty * 2

FROM Production.BillOfMaterials AS c

JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID

WHERE d.ComponentLevel = 0;

-- Genealogy table

IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;

GO

CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);

GO

INSERT dbo.Person

VALUES(1, 'Sue', NULL, NULL)

,(2, 'Ed', NULL, NULL)

,(3, 'Emma', 1, 2)

,(4, 'Jack', 1, 2)

,(5, 'Jane', NULL, NULL)

,(6, 'Bonnie', 5, 4)

,(7, 'Bill', 5, 4);

GO

-- Create the recursive CTE to find all of Bonnie's ancestors.

WITH Generation (ID) AS

(

-- First anchor member returns Bonnie's mother.

SELECT Mother

FROM dbo.Person

WHERE Name = 'Bonnie'

UNION

-- Second anchor member returns Bonnie's father.

SELECT Father

FROM dbo.Person

WHERE Name = 'Bonnie'

UNION ALL

-- First recursive member returns male ancestors of the previous generation.

SELECT Person.Father

FROM Generation, Person

WHERE Generation.ID=Person.ID

UNION ALL

-- Second recursive member returns female ancestors of the previous generation.

SELECT Person.Mother

K.Utilizar funciones analíticas en una CTE recursivaEn el siguiente ejemplo se muestra un error que puede producirse al utilizar una función analítica o de agregado en la parte recursiva de una CTE.

Los siguientes resultados son los esperados para la consulta.

Los siguientes resultados son los resultados reales de la consulta.

N devuelve 1 para cada paso de la parte recursiva del CTE, porque solo el subconjunto de datos para ese nivel de recursividad se pasa a ROWNUMBER. Por cada iteraciónde la parte recursiva de la consulta solo se pasa una fila a ROWNUMBER.

Vea también

FROM Generation, dbo.Person

WHERE Generation.ID=Person.ID

)

SELECT Person.ID, Person.Name, Person.Mother, Person.Father

FROM Generation, dbo.Person

WHERE Generation.ID = Person.ID;

GO

DECLARE @t1 TABLE (itmID int, itmIDComp int);

INSERT @t1 VALUES (1,10), (2,10);

DECLARE @t2 TABLE (itmID int, itmIDComp int);

INSERT @t2 VALUES (3,10), (4,10);

WITH vw AS

(

SELECT itmIDComp, itmID

FROM @t1

UNION ALL

SELECT itmIDComp, itmID

FROM @t2

)

,r AS

(

SELECT t.itmID AS itmIDComp

, NULL AS itmID

,CAST(0 AS bigint) AS N

,1 AS Lvl

FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)

UNION ALL

SELECT t.itmIDComp

, t.itmID

, ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N

, Lvl + 1

FROM r

JOIN vw AS t ON t.itmID = r.itmIDComp

)

SELECT Lvl, N FROM r;

Lvl N

1 0

1 0

1 0

1 0

2 4

2 3

2 2

2 1

Lvl N

1 0

1 0

1 0

1 0

2 1

2 1

2 1

2 1

ReferenciaCREATE VIEW (Transact-SQL)


EXCEPT e INTERSECT (Transact-SQL)


SELECT (Transact-SQL)


© 2014 Microsoft








Permite la actualización interactiva de registro mínimo de una columna text, ntext o image existente. WRITETEXT sobrescribe completamente los datos existentes en la

columna afectada. No se puede utilizar WRITETEXT en las columnas text, ntext, e image de vistas.

Importante

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificarlas aplicaciones que actualmente la utilizan. Utilice los tipos de datos de valores grandes y la cláusula .WRITE de la instrucción UPDATE, en su lugar.



Sintaxis

Argumentos

BULK

Hace posible que las herramientas de carga carguen flujos de datos binarios. La herramienta debe proporcionar flujos en el nivel de protocolo TDS. Cuando el

flujo de datos no esté presente el procesador de consultas omite la opción BULK.

Importante

Recomendamos que la opción BULK no se utilice en las aplicaciones basadas en SQL Server. Es posible que esta opción se modifique o quite en versionesfuturas de SQL Server.

table . column

Es el nombre de la tabla y de la columna text, ntext o image que se van a actualizar. Los nombres de tablas y columnas se deben ajustar a las reglas de los

identificadores. La especificación de los nombres de la base de datos y del propietario es opcional.

text_ptr

Es un valor que almacena el puntero a los datos text, ntext o image. text_ptr debe ser de tipo binary(16). Para crear un puntero de texto, ejecute una

instrucción INSERT o UPDATE con datos que no sean NULL para la columna text, ntext o image.

WITH LOG

Omitido por SQL Server. El registro está determinado por el modelo de recuperación vigente para la base de datos.

data

Son los datos de tipo text, ntext o image que se van a almacenar. data puede ser un literal o un parámetro. La longitud máxima de texto que se puede insertarinteractivamente con WRITETEXT es de 120 KB aproximadamente para datos de tipo text, ntext e image.

Comentarios

Utilice WRITETEXT para reemplazar datos de tipo text, ntext e image y UPDATETEXT para modificar datos de tipo text, ntext e image. UPDATETEXT es más flexibledebido a que cambia solo una parte de una columna text, ntext o image en lugar de la columna completa.

Para obtener un rendimiento óptimo, se recomienda insertar o actualizar los datos de tipo text, ntext e image por partes que sean múltiplos de 8040 bytes.

Si el modelo de recuperación de la base de datos es simple u optimizado para cargas masivas de registros, las operaciones text, ntext e image que usen WRITETEXT

se registrarán mínimamente cuando se inserten o se anexen datos nuevos.

Nota

El registro mínimo no se utiliza cuando se actualizan los datos existentes.

Para que WRITETEXT funcione correctamente, la columna ya debe contener un puntero de texto válido.

Si en la tabla no existe texto de fila, SQL Server ahorra espacio al no inicializar las columnas text cuando se colocan valores NULL explícitos o implícitos en las columnastext con INSERT, y no es posible obtener ningún puntero de texto para estos valores NULL. Para inicializar columnas text a NULL, utilice la instrucción UPDATE. Si en la

tabla existe texto de fila, no es necesario inicializar a valores NULL la columna de texto y siempre es posible obtener un puntero de texto.

La función SQLPutData de ODBC es más rápida y utiliza menos memoria dinámica que WRITETEXT. Esta función puede insertar hasta 2 gigabytes de datos de tipo text,

ntext o image.

En SQL Server, pueden existir punteros de texto de fila para datos de tipo text, ntext o image, pero es posible que no sean válidos. Para obtener información acercade la opción text in row, vea sp_tableoption (Transact-SQL). Para obtener información sobre cómo invalidar punteros de texto, vea sp_invalidate_textptr (Transact-SQL).

SQL Server 2014

WRITETEXT [BULK]

{ table.column text_ptr }

[ WITH LOG ] { data }









Permisos

Se requieren permisos UPDATE para la tabla especificada. El permiso se puede transferir cuando se transfiere el permiso UPDATE.

Ejemplos

En el ejemplo siguiente el puntero de texto se sitúa en la variable local @ptrval y, a continuación, WRITETEXT sitúa la nueva cadena de texto en la fila a la que señala@ptrval.

Nota

Para ejecutar este ejemplo, debe instalar la base de datos de ejemplo pubs.

Vea también

ReferenciaTipos de datos (Transact-SQL)

DECLARE @local_variable (Transact-SQL)


SELECT (Transact-SQL)

Instrucciones SET (Transact-SQL)


© 2014 Microsoft

USE pubs;

GO


GO

DECLARE @ptrval binary(16);


FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id


WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!';

GO


GO







Documents

Otros Comandos Transact SQL 2014.pdf