locklooki.blogg.se

1. #Generate insert statements from table sql server how to#
2. #Generate insert statements from table sql server code#

We assume you have a table Country as below in your database and you want get insert SQL statements from some records in this table.

#Generate insert statements from table sql server how to#

Next, I do an example to help you understand how to use it. IF PRINT ' 1.columns string: '+ ' 2.values string: '+ PRINT ' EXEC( NOCOUNT OFF END GO Example SELECT ' AS sql FROM + ' WHERE 1=1 AND ' + isnull( ' 1=1') WHERE sc.id = object_id( sc.xtype 189 - xtype=189 'timestamp' AND sc.xtype 34 - xtype=34 'image' AND sc.xtype 35 - xtype= 35 'text' AND (columnproperty(sc.id, sc.name, ' IsIdentity') = 0 OR AS t Xtype=62 'float'*/ WHEN sc.xtype in ( 59, 62) THEN ' CASE WHEN is null THEN ''NULL'' ELSE '+ ' CONVERT(NVARCHAR(23),'+sc.name + ' ,2)'+ ' END' /* xtype=48 'tinyint'

Xtype=108 'numeric'*/ WHEN sc.xtype in( 106, 108) THEN ' CASE WHEN is null THEN ''NULL'' ELSE '+ ' CONVERT(NVARCHAR('+CONVERT( NVARCHAR( 4),sc.xprec+2)+ ' ),)'+ ' END' /* xtype=59 'real' Xtype=165 'varbinary'*/ WHEN sc.xtype in ( 173, 165) THEN ' CASE WHEN is null THEN ''NULL'' ELSE '+ ' CONVERT(NVARCHAR('+CONVERT( NVARCHAR( 4),sc.*2+2)+ ' ),)'+ ' END' /* xtype=104 'bit'*/ WHEN sc.xtype =104 THEN ' CASE WHEN is null THEN ''NULL'' ELSE '+ ' CONVERT(NVARCHAR(1),)'+ ' END' /* xtype=61 'datetime' when 1,output the internal sql stringĪS BEGIN SET NOCOUNT ON DECLARE NVARCHAR(MAX) , bit=0 - indicate if this is used to debug. , bit=0 - indicate if ignore columne with identity , NVARCHAR( 400) - condition to filter data

#Generate insert statements from table sql server code#

1)Add code lines to avoid error when is empty string - 2)Based on Lukas Macedo's suggetstions, add surrounding brackets for column name - 3)Simplify WHEN.CASE - = CREATE PROC. Based on Mike's suggestions, I optimized the codes - 3.

Demo : exec sp_CreateInsertScript '.','=''North America''' - Change History: - 1. It does not support the columns with timestamp,text,image. = - Author: Mark Kang - Company: - Create date: - Description: Generat the insert sql script according to the data in the specified table.