SQL Server rapid generation of multi-field statements

You might encounter a problem like this:

There is a table with dozens of or hundreds of fields, after the backup deleted a portion of the data, and later found that the problem needs to recover some of the data, but there are several fields do not need to restore (such as 100 fields, just restore 97)

Beginner Stage:

For a beginner may be a nightmare: "Oh, my god! Do you have to hit 97 fields??? ", while looking for a case from the Internet, while pondering, tossing for more than 10 minutes, and eventually hand-hit ...

Introductory phase:

For technicians who know the database but are not very good at writing SQL statements (involving system tables): "Well ... This can be from the table statement or with SP_HELP table_name to get all the fields, just want to copy the field to tidy up ... ", so copy and paste, change the field to sign, processing for a few minutes

Advanced Stage:

For people who are familiar with the data and write SQL statements: "The system table contains the field information of the table, in the query field can be used to eliminate the unwanted fields, stitching out the statements that can be executed directly ...", after a few minutes, knock out the code:

DECLARE @into_table_name varchar (50)

DECLARE @select_table_name varchar (50)

DECLARE @except_columns varchar (100)

DECLARE @where_condition varchar (500)

--Original table

Set @into_table_name = ' '

--Backup table

Set @select_table_name = ' '

--Excluded fields

Set @except_columns = ' '

--where conditions

Set @where_condition = ' Where 1=2 '

DECLARE @insert_str varchar (max)

DECLARE @column_str varchar (max)

--Stitching fields

Set @column_str = (

Select B.name+ ', '

From sysobjects A,syscolumns b

where a.id=b.id

and [Email protected]_name

and B.name not in (@except_columns)

FOR XML Path (")")

--Delete trailing comma

Select @column_str =substring (@column_str, 1,len (@column_str)-1)

--Stitching SQL statements

Set @insert_str = ' INSERT INTO ' [email protected]_table_name+ ' (' [email protected]_str+ ') SELECT ' [Email protected]_str+ ' From ' [Email protected][email protected]_condition

Select @insert_str

After check: exec (@insert_str)

A similar problem arises again

Beginner: "##$#@[email protected]#[email protected]#%$#% ..."

Beginner: "Alas ..."

The advanced person smiled, leisurely replacement parameters, more than 10 seconds after everything is done ...

So began to play strange upgrade Road: Beginners--------the advanced person->

This article is from the "Aimax" blog, make sure to keep this source http://aimax.blog.51cto.com/11610508/1976760

SQL Server rapid generation of multi-field statements