Insert script for particular records only

Transact-SQL
1

Hi,
Is it possible to generate the insert into... value scripts with
values after a specific date or based on condition like last
10 records. Tried with generate scripts option by right clicking
the db and selecting the data option,but it gives all records.

thanks.

2

sure it's possible, it's just not point and click. You'll need to write a query. It will look something like this:

declare @insert nvarchar(4000) = 
    'insert into yourtable (col1, col2, ...) '
  +   ' values ('

select @insert + '''' + cast(col1 as varchar(8000)) + ''',' +  '''' + cast(col2 as varchar(8000)) + ''','  ... + ')'
from yourtable
where (put your conditions here)
for xml path('')
3

This should work

DECLARE @Test TABLE
(
	A INT,
	B CHAR(1),
	DT DATE
)

INSERT INTO @Test
VALUES (1,'A','20150101'),
(2,'B','20150102'),
(3,'C','20150103'),
(4,'D','20150104'),
(5,'E','20150105');

SELECT	'INSERT INTO @Test (A,B,DT)' + CHAR(10) + 'VALUES ' + CHAR(10)

UNION ALL

SELECT	DISTINCT STUFF((SELECT ',('
		+ CAST(T.A AS VARCHAR) + ','
		+ QUOTENAME(T.B,'''') + ','
		+ QUOTENAME(CONVERT(VARCHAR(10),T.DT, 112),'''')
		+ ')'
		+ CHAR(10)
		FROM	@Test AS T
		WHERE	T.DT <= '20150104'
		FOR	XML path(''))
		,1,1,'')
4

Need to watch out for the size limit on that (particularly in this example which I assume might have long rows ...). Also it will mangle XML characters in text ("<", ">", "&")

I think?? the answer to both those is

			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')	-- NOTE: "'value" is case sensitive!!
5

Actually even with that code change I cannot get past 8000 chars length. Is it just me?

6

Yeah, that was a coding error in my test. I cannot get FOR XML PATH to fail in terms of length of the concatenated string returned. The issue with escaping XML characters does not consideration though.