CODEWITHSUNDEEP
sqlt-sql
TheBoubou
TheBoubou

Reputation: 19903

Generate insert script for some data

I have this : 

SELECT * FROM Person WHERE Age > 5

Is there a way to have as result the insert queries ?

The result should like this :

INSERT INTO MyTable VALUES (.......)

Thanks,

Upvotes: 0

Views: 126

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

Following is a script which iterate throw the current database and create a SELECT INTO query for each table inside it. you can filter the query to iterate throw the desired tables or columns, or also you can replace the EXEC(@selectQuery) with Print @selectQuery to only get the query without executing it.

DECLARE @selectQuery NVARCHAR(MAX);
DECLARE @tableSchemaName NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(MAX);

DECLARE @schemaId INT;

DECLARE C CURSOR FAST_FORWARD FOR(
SELECT schemas.schema_id,
       sys.schemas.name,
       sys.tables.name
FROM sys.tables
    INNER JOIN sys.schemas
        ON schemas.schema_id = TABLES.schema_id);
OPEN C;
FETCH NEXT FROM C
INTO @schemaId,
     @tableSchemaName,
     @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @columns NVARCHAR(MAX);

    SELECT @columns = STRING_AGG(name, ',')
    FROM sys.columns
    WHERE object_id =
    (
        SELECT object_id
        FROM sys.tables
        WHERE name = @tableName
              AND schema_id = @schemaId
    );

    SET @selectQuery
        = N'SELECT ' + @columns + N' INTO ' + @tableSchemaName + N'.' + @tableName + N'2' + N' FROM '
          + @tableSchemaName + N'.' + @tableName;

    EXEC (@selectQuery);
    FETCH NEXT FROM C
    INTO @schemaId,
         @tableSchemaName,
         @tableName;
END;
CLOSE C;
DEALLOCATE C;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Are you looking for INSERT . . . SELECT:

INSERT INTO mytable ( . . . )  -- list the columns here
    SELECT *   -- you should list the columns here too
    FROM Person
    WHERE Age > 5;

Or just create the table, use INTO:

SELECT p.*
INTO mytable
FROM Person p
WHERE Age > 5;

Upvotes: 2

Related Questions