Lory Lory Lory Lory - 13 days ago 9
SQL Question

SQL Server: is it possible to script table as UPDATE?

I need to export/script two tables to my local database to a remote anyway.
Anyway I cannot export them as INSERT INTO scripts because I cannot drop them in the remote database and populate them again (because of FK and integrity constraints). So, is it possibile to script tables as UPDATE statements for each row, instead of INSERT INTO? I'm using SQL Server 2008/2012

Answer
CREATE TABLE mytable(
   ExtractTypeNum   INTEGER  NOT NULL --PRIMARY KEY 
  ,FileOrderNum     VARCHAR(11) 
  ,PrevFileOrderNum VARCHAR(11) 
  ,NextFileOrderNum VARCHAR(11)
  ,rownum1          INTEGER 
  ,Statusflag1      VARCHAR(9) 
);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1) 
VALUES (1,'2016-09-191',NULL,'2016-09-192',1,'IsInitial');
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1) 
VALUES (2,'2016-09-192','2016-09-191','2016-09-201',2,NULL);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1) 
VALUES (3,'2016-09-201','2016-09-192','2016-09-211',3,NULL);


select 'Update Table Xyz Set Abc='+Convert(varchar(25),rownum1)+' ' as X,*
from myTable