CODEWITHSUNDEEP
sql-serverinsert
karthik k
karthik k

Reputation: 3981

Insert script for a particular set of rows in SQL

I am using SQL Server 2008. I use to take the script of my data from SQL table using Tasks --> Generate Scripts option.

Here is my problem:

Let's say I have 21,000 records in Employee table. When I take the script of this table, it takes the insert script for all 21000 records. What is the solution if I want to take only the script of 18000 records from the table?

Is there any solution using SQL query or from the tasks wizard?

Thanks in advance...

Upvotes: 1

Views: 513

Answers (2)

RobRolls
RobRolls

Reputation: 508

In case the views are not an option for you I wrote the following code based on the Aaron Bertrand's answer here that will give the insert statement for a single record in the db.

CREATE PROCEDURE dbo.GenerateSingleInsert
@table     NVARCHAR(511), -- expects schema.table notation
@pk_column SYSNAME,       -- column that is primary key
@pk_value  NVARCHAR(10)   -- change data type accordingly
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cols   NVARCHAR(MAX), @vals   NVARCHAR(MAX),
        @valOut NVARCHAR(MAX), @valSQL NVARCHAR(MAX);

    SELECT @cols = N'', @vals = N'';

    SELECT @cols = @cols + ',' + QUOTENAME(name),
       @vals = @vals + ' + '','' + ' + 'ISNULL('+REPLICATE(CHAR(39),4)+'+RTRIM(' + 
                                            CASE WHEN system_type_id IN (40,41,42,43,58,61) -- dateteime and time stamp type
                                        THEN
                                            'CONVERT(CHAR(8), '  + QUOTENAME(name) + ', 112) + '' ''+ CONVERT(CHAR(14), ' + QUOTENAME(name) + ', 14)'
                                        WHEN system_type_id IN (35) -- text type
                                        THEN
                                            'REPLACE(CAST(' + QUOTENAME(name) + 'as nvarchar(MAX)),'+REPLICATE(CHAR(39),4)+','+REPLICATE(CHAR(39),6)+')' 
                                        ELSE 
                                            'REPLACE(' + QUOTENAME(name) + ','+REPLICATE(CHAR(39),4)+','+REPLICATE(CHAR(39),6)+')' 
                                        END 
                                + ')+' + REPLICATE(CHAR(39),4) + ',''null'') + '

      FROM sys.columns WHERE [object_id] = OBJECT_ID(@table)
      AND system_type_id <> 189 -- can't insert rowversion
      AND is_computed = 0;      -- can't insert computed columns

    SELECT @cols = STUFF(@cols, 1, 1, ''),
       @vals = REPLICATE(CHAR(39),2) + STUFF(@vals, 1, 6, '') + REPLICATE(CHAR(39),2) ;

    SELECT @valSQL = N'SELECT @valOut = ' + @vals + ' FROM ' + @table + ' WHERE '
    + QUOTENAME(@pk_column) + ' = ''' + RTRIM(@pk_value) + ''';';

    EXEC sp_executesql @valSQL, N'@valOut NVARCHAR(MAX) OUTPUT', @valOut OUTPUT;

    SELECT SQL = 'INSERT ' + @table + '(' + @cols + ') SELECT ' + @valOut;
END

I took the above code and wrapped it the following proc that will use the where clause you give it to select which insert statements to create

CREATE PROCEDURE dbo.GenerateInserts
    @table     NVARCHAR(511), -- expects schema.table notation
    @pk_column SYSNAME,       -- column that is primary key
@whereClause NVARCHAR(500) -- the where clause used to parse down the data
AS
BEGIN

declare @temp TABLE ( keyValue nvarchar(10),    Pos int );
declare @result TABLE ( insertString nvarchar(MAX) );
declare @query NVARCHAR(MAX)

set @query = 
'with qry as 
(
    SELECT ' + @pk_column + ' as KeyValue, ROW_NUMBER() over(ORDER BY ' + @pk_column + ') Pos
    from ' + @table + '
    ' + @whereClause + '
)
select * from qry'

insert into @temp
exec sp_sqlexec @query

Declare @i int, @key nvarchar(10) 

select @i = count(*) from @temp
WHILE @i > 0 BEGIN

    select @key = KeyValue from @temp where Pos = @i

    insert into @result
    exec [dbo].[GenerateSingleInsert] @table, @pk_column, @key

    set @i = @i - 1 

END

select insertString from @result

END

Calling it could look like the following. You pass in the table name, the table primary key and the where clause and you should end up with your insert statements.

set @whereClause = 'where PrettyColorsId > 1000 and PrettyColorsID < 5000'
exec [dbo].GenerateInserts 'dbo.PrettyColors', 'PrettyColorsID', @whereClause

set @whereClause = 'where Color in (' + @SomeValues + ')'
exec [dbo].GenerateInserts 'dbo.PrettyColors', 'PrettyColorsID', @whereClause

Upvotes: 0

Peter Kelly
Peter Kelly

Reputation: 14391

Create a new View where you select your desired rows from your Employee table e.g. SELECT TOP 21000...

Then simply script that View instead of the Table.

Upvotes: 3

Related Questions