[sql-server] 저장된 모든 행을 포함하는 기존 SQL Server 테이블에 대해 INSERT 스크립트를 생성하려면 어떻게해야합니까?

Program / 글쓴이

SQL Management Studio 2008 R2를 사용하여 “모든 행 만들기 및 삽입”스크립트를 생성하는 방법을 찾고 있습니다.

“테이블 만들기”스크립트를 만들 수 있다는 것을 알고 있습니다.

“삽입”스크립트를 만들 수도 있지만 자리 표시자가있는 단일 행만 생성합니다.

현재 저장된 모든 행을 포함하는 삽입 스크립트를 생성하는 방법이 있습니까?

답변

예, 그러나 데이터베이스 수준에서 실행해야합니다.

SSMS에서 데이터베이스를 마우스 오른쪽 단추로 클릭하고 “작업”, “스크립트 생성 …”을 선택하십시오. 작업하면서 “스크립팅 옵션”섹션으로 이동합니다. “고급”을 클릭하고 팝업되는 목록에서 “스크립트 유형의 데이터”라고 표시되는 데이터 및 / 또는 스키마를 선택할 수있는 옵션이 있습니다.

고급 스크립팅 옵션의 스크린 샷

답변

이 스크립트는 기존 데이터의 삽입 문을 생성합니다. 이 절차는 한 번 실행해야하는 맞춤형 저장 프로 시저입니다.

나는 이런 종류의 물건을 잠시 찾으려고 노력했지만 결과에 만족하지 않았기 때문에이 저장 프로 시저를 작성했습니다.

예:

Exec [dbo].[INS]  'Dbo.test where 1=1'

(1) dbo스키마와 테스트는 tablename이며 1=1조건입니다.

Exec [dbo].[INS]  'Dbo.test where name =''neeraj''' * for string

(2) dbo스키마와 테스트는 테이블 이름이며 name='neeraj'조건입니다.

저장 프로시 저는 다음과 같습니다.

/*
Authore : neeraj prasad sharma (please dont remove this :))
Example (1) Exec [dbo].[INS]  'Dbo.test where 1=1'
        (2) Exec [dbo].[INS]  'Dbo.test where name =''neeraj''' * for string

here Dbo is schema and test is tablename and 1=1 is condition

*/


CREATE procedure  [dbo].[INS]
(
   @Query  Varchar(MAX)
)

AS

SET nocount ON

DECLARE @WithStrINdex as INT
DECLARE @WhereStrINdex as INT
DECLARE @INDExtouse as INT

DECLARE @SchemaAndTAble VArchar(270)
DECLARE @Schema_name  varchar(30)
DECLARE @Table_name  varchar(240)
DECLARE @Condition  Varchar(MAX)

SET @WithStrINdex=0

SELECT @WithStrINdex=CHARINDEX('With',@Query )
, @WhereStrINdex=CHARINDEX('WHERE', @Query)

IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WhereStrINdex

SELECT @SchemaAndTAble=Left (@Query,@INDExtouse-1)
SELECT @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))

SELECT @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)
,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )

,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                            


DECLARE @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS as varchar(MAX)
DECLARE @Total_Rows as SmallINT
DECLARE @Counter as SmallINT

DECLARE @ComaCol as varchar(max)
SELECT @ComaCol=''

SET @Counter=1
SET @CONDITIONS=''

INSERT INTO @COLUMNS
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count], Column_Name
FROM INformation_schema.columns
WHERE Table_schema=@Schema_name AND table_name=@Table_name


SELECT @Total_Rows= Count(1)
FROM @COLUMNS

SELECT @Table_name= '['+@Table_name+']'

SELECT @Schema_name='['+@Schema_name+']'

While (@Counter<=@Total_Rows )
begin
--PRINT @Counter                              

SELECT @ComaCol= @ComaCol+'['+Column_Name+'],'
FROM @COLUMNS
WHERE [Row_number]=@Counter

SELECT @CONDITIONS=@CONDITIONS+ ' + Case When ['+Column_Name+'] is null then ''Null'' Else '''''''' + Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  ) +'''''''' end+'+''','''
FROM @COLUMNS
WHERE [Row_number]=@Counter

SET @Counter=@Counter+1

End

SELECT @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )

SELECT @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS

SELECT @CONDITIONS=@CONDITIONS+'+'+ ''')'''

SELECT @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition
print(@CONDITIONS)
Exec(@CONDITIONS)

답변

예. “결과 집합, 테이블 또는 데이터베이스에서 Insert 문 생성” 기능 이 있는 상용이지만 저렴한 SSMS 도구 팩 추가 기능 을 사용하십시오.

답변

공유하기 위해 스크립트를 직접 개발했습니다. 자유롭게 사용하십시오. “SELECT”문을 생성 한 다음 테이블에서 실행하여 “INSERT”문을 생성 할 수 있습니다.

select distinct 'SELECT ''INSERT INTO ' + schema_name(ta.schema_id) + '.' + so.name + ' (' + substring(o.list, 1, len(o.list)-1) + ') VALUES ('
+ substring(val.list, 1, len(val.list)-1) + ');''  FROM ' + schema_name(ta.schema_id) + '.' + so.name + ';'
from    sys.objects so
join sys.tables ta on ta.object_id=so.object_id
cross apply
(SELECT '  ' +column_name + ', '
 from information_schema.columns c
 join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
 where table_name = so.name
 order by ordinal_position
FOR XML PATH('')) o (list)
cross apply
(SELECT '''+' +case
         when data_type = 'uniqueidentifier' THEN 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''' END '
         WHEN data_type = 'timestamp' then '''''''''+CONVERT(NVARCHAR(MAX),CONVERT(BINARY(8),[' + COLUMN_NAME + ']),1)+'''''''''
         WHEN data_type = 'nvarchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
         WHEN data_type = 'varchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
         WHEN data_type = 'char' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
         WHEN data_type = 'nchar' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE([' + COLUMN_NAME + '],'''''''','''''''''''')+'''''''' END'
         when DATA_TYPE='datetime' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
         when DATA_TYPE='datetime2' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],121)+'''''''' END '
         when DATA_TYPE='geography' and column_name<>'Shape' then 'ST_GeomFromText(''POINT('+column_name+'.Lat '+column_name+'.Long)'') '
         when DATA_TYPE='geography' and column_name='Shape' then '''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''''
         when DATA_TYPE='bit' then '''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''''
         when DATA_TYPE='xml' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+REPLACE(CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + ']),'''''''','''''''''''')+'''''''' END '
         WHEN DATA_TYPE='image' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY(MAX),[' + COLUMN_NAME + ']),1)+'''''''' END '
         WHEN DATA_TYPE='varbinary' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],1)+'''''''' END '
         WHEN DATA_TYPE='binary' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '],1)+'''''''' END '
         when DATA_TYPE='time' then 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE ''''''''+CONVERT(NVARCHAR(MAX),[' + COLUMN_NAME + '])+'''''''' END '
         ELSE 'CASE WHEN [' + column_name+'] IS NULL THEN ''NULL'' ELSE CONVERT(NVARCHAR(MAX),['+column_name+']) END' end
   + '+'', '
 from information_schema.columns c
 join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
 where table_name = so.name
 order by ordinal_position
FOR XML PATH('')) val (list)
where   so.type = 'U'

답변