{"id":5675,"date":"2018-05-04T11:18:33","date_gmt":"2018-05-04T16:18:33","guid":{"rendered":"https:\/\/www.bhinfo.com.mx\/cursos\/?p=5675"},"modified":"2018-05-04T18:20:44","modified_gmt":"2018-05-04T23:20:44","slug":"sql-insertar-datos-con-identity-y-constraint","status":"publish","type":"post","link":"https:\/\/www.bhinfo.com.mx\/cursos\/2018\/05\/04\/sql-insertar-datos-con-identity-y-constraint\/","title":{"rendered":"SQL, Insertar datos con identity y constraint"},"content":{"rendered":"<p style=\"text-align: center;\"><strong>Se ejecuta el siguiente query para generar el query de inserci\u00f3n de datos<\/strong><\/p>\n<pre class=\"lang:tsql decode:true  \">\t--\r\n\t--********************************************  CAMPOS  ***************************************\r\n\tdeclare @ServerS as varchar(50) = 'srvbattanet'\r\n\tdeclare @BDataS as varchar(50) = 'battanet'\r\n\tdeclare @BData as varchar(100) = 'battanet' ----baasedatos destino\r\n\tdeclare @Tabla as varchar(200) = 'tipos'\r\n\tdeclare @Regs as varchar(600) = '('\r\n\t\r\n\tDECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR \r\n\t\t\tSELECT  c.name \r\n\t\t\tFROM sys.columns c JOIN sys.tables t \r\n\t\t\tON c.object_id = t.object_id\r\n\t\t\tWHERE t.name = @Tabla\r\n\tDECLARE @object_name AS varchar(100) \r\n\tOPEN objects_cursor \r\n\tFETCH NEXT FROM objects_cursor into @object_name\r\n\tWHILE @@FETCH_STATUS = 0 \r\n\t\tBEGIN\r\n\t\t\tset @Regs = @Regs + @object_name + ','\r\n\t\t\tFETCH NEXT FROM objects_cursor into @object_name\r\n\tend;\t \t\t\r\n\tCLOSE objects_cursor; \r\n    DEALLOCATE objects_cursor;\r\n\t\r\n\tset @Regs = substring(@Regs,1,LEN(@Regs)-1) + ')'\r\n\t\r\n\tprint\t'--ALTER TABLE '+ @BData + '.dbo.[' + @Tabla + '] NOCHECK CONSTRAINT ALL'\r\n\tprint   'set identity_insert '+ @BData + '.dbo.[' + @Tabla + '] on'\r\n\tprint   'insert into '+ @BData + '.dbo.[' + @Tabla + ']' + @Regs \r\n\tprint   'SELECT T.* FROM ' + @ServerS + '.' + @BDataS + '.dbo.' + @Tabla + ' T  WITH (NOLOCK)'\r\n\tprint   'set identity_insert '+ @BData + '.dbo.[' + @Tabla + '] oFF'\r\n\tprint   '--ALTER TABLE '+ @BData + '.dbo.[' + @Tabla + '] CHECK CONSTRAINT ALL'\r\n\t--**********************************************************************************************\r\n\t--*******************************************  EJEMPLO  ****************************************\r\n\t\tALTER TABLE battanet.dbo.[tipos] NOCHECK CONSTRAINT ALL\r\n\t\tset identity_insert battanet.dbo.[tipos] on\r\n\t\tinsert into battanet.dbo.[tipos](id_tipo,tipo_grupo,tipo_subgrupo,nombre,descripcion,id_estatus)\r\n\t\tSELECT T.* FROM srvbattanet.battanet.dbo.tipos T  WITH (NOLOCK)\r\n\t\tset identity_insert battanet.dbo.[tipos] oFF\r\n\t\tALTER TABLE battanet.dbo.[tipos] CHECK CONSTRAINT ALL\r\n\t--Caracteristica:  conectado a la base de datos donde se insertaran los datos y tener un linked server de donde se extraera la informacion}}\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Se ejecuta el siguiente query para generar el query de inserci\u00f3n de datos &#8212; &#8211;******************************************** CAMPOS *************************************** declare @ServerS as varchar(50) = &#8216;srvbattanet&#8217; declare @BDataS as varchar(50) = &#8216;battanet&#8217; declare @BData as varchar(100) = &#8216;battanet&#8217; &#8212;-baasedatos destino declare @Tabla as varchar(200) = &#8216;tipos&#8217; declare @Regs as varchar(600) = &#8216;(&#8216; DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[414,404],"tags":[427,424,426,425,53],"class_list":["post-5675","post","type-post","status-publish","format-standard","hentry","category-querys","category-sql-dessarrollo","tag-constraint","tag-identity","tag-key","tag-llaves","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/5675","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/comments?post=5675"}],"version-history":[{"count":3,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/5675\/revisions"}],"predecessor-version":[{"id":5678,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/posts\/5675\/revisions\/5678"}],"wp:attachment":[{"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/media?parent=5675"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/categories?post=5675"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bhinfo.com.mx\/cursos\/wp-json\/wp\/v2\/tags?post=5675"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}