-
GenerateTableDefinition.sql
-
/************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.0.70 * Time: 2014/5/4 17:25:34 ************************************************************/ IF OBJECT_ID('GenerateTableDefinition') IS NOT NULL BEGIN PRINT 'Dropping procedure GenerateTableDefinition' DROP PROCEDURE [GenerateTableDefinition] IF @@ERROR = 0 PRINT 'Procedure GenerateTableDefinition dropped' END GO CREATE PROCEDURE [dbo].[GenerateTableDefinition] (@TableName VARCHAR(100)) AS BEGIN IF EXISTS( SELECT [Table_Name] FROM Information_Schema.COLUMNS WHERE [TABLE_NAME] = @tableName ) BEGIN DECLARE @Sql VARCHAR(8000) DECLARE @Table VARCHAR(100) SET @Sql = '' SELECT @Sql = @Sql + CASE WHEN CHARINDEX('(', @Sql, 1) <= 0 THEN '(' ELSE '' END + [COLUMN_NAME] + ' ' + [DATA_TYPE] + CASE WHEN [DATA_TYPE] IN ('real', 'money', 'decimal', 'numeric') THEN + '(' + CAST(ISNULL([NUMERIC_PRECISION], '') AS VARCHAR) + ',' + CASE WHEN [DATA_TYPE] IN ('real', 'money', 'decimal', 'numeric') THEN CAST(ISNULL([NUMERIC_SCALE], '') AS VARCHAR) + ')' END WHEN [DATA_TYPE] IN ('char', 'nvarchar', 'varchar', 'nchar') THEN + '(' + CAST(ISNULL([CHARACTER_MAXIMUM_LENGTH], '') AS VARCHAR) + ' )' ELSE '' END + CASE WHEN [IS_NULLABLE] = 'No' THEN ' Not Null,' ELSE ' Null, ' END FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME] = @TableName SELECT @Table = 'Create table ' + [TABLE_NAME] FROM [Information_Schema].[COLUMNS] WHERE [TABLE_NAME] = @TableName SELECT @Sql = @Table + SUBSTRING(@Sql, 1, LEN(@sql) -1) + ' )' SELECT @Sql AS DDL END ELSE SELECT 'The table ' + @TableName + ' does not exist' END GO IF @@ERROR = 0 PRINT 'Procedure GenerateTableDefinition created' GO
sql2005.sql2008批量merge - ALL.sql -
/************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.0.70 * Time: 2014/5/4 14:38:33 ************************************************************/ DECLARE @temp TABLE (id INT,tblcod NVARCHAR(1000),colcod NVARCHAR(1000),odr int) DECLARE @tbls TABLE (tblname NVARCHAR(1000),odr int) DELETE FROM @tbls DELETE FROM @temp INSERT INTO @tbls VALUES /*('Stat_CollectPhotoCheck',11), ('Stat_CollectTemplateData',12), ('Stat_CollectTemplateDefine',13), ('Stat_CollectTPContentDefine',14), ('Stat_CollectTPData',15), ('Stat_RptAreaDfn',16), ('Stat_RptAreaDfnEdit',17), ('Stat_RptCatalog',18), ('Stat_RptCatalog_Files',19), ('Stat_RptCatalogCfg',20), ('Stat_RptCatalogType',21), ('Stat_RptCellPostil',22), ('Stat_RptCellSQL',23), ('Stat_RptCellState',24), ('Stat_RptCheckCdt',25), ('Stat_RptCheckPoint',26), ('Stat_RptCheckPointNodeState',27), ('Stat_RptCheckScope',28), ('Stat_RptCheckStateDesc',29), ('Stat_RptCheckType',30), ('Stat_RptCiteGid',31), ('Stat_RptCitePostilFile',32), ('Stat_RptCiteTid',33), ('Stat_RptCompareTempCell',34), ('Stat_RptCompareTemplates',35), ('Stat_RptCompareUnit',36), ('Stat_RptCompareValue',37), ('Stat_RptCoverDfn',38), ('Stat_RptCoverPos ',39), ('Stat_RptDataDefine',40), ('Stat_RptDataDfn',41), ('Stat_RptDataTemp',42), ('Stat_RptFeedbackCmdInfo',43), ('Stat_RptFeedbackType',44), ('Stat_RptGroups',45), ('Stat_RptGroups_ConvertTable',46), ('Stat_RptGroups_ConvertTable_party',47), ('Stat_RptGroupsCatalog_R',48), ('Stat_RptGrpType',49), ('Stat_RptGrpUnitDfn',50), ('Stat_RptGrpUnits_Hiber',51), ('Stat_RptJoinTbl',52), ('Stat_RptNodePeriods',53), ('Stat_RptNodeState',54), ('Stat_RptPeriodsCatalog_R',55), ('Stat_RptPostil_Files',56), ('Stat_RptQryTemplates',57), ('Stat_RptQryTemplatesDfn',58), ('Stat_RptRCDfn',59), ('Stat_RptRcFunction',60), ('Stat_RptRcSelectRange',61), ('Stat_RptReSqlSelect',62), ('Stat_RptSpecialAreaType',63), ('Stat_RptTemplate_Files',64), ('Stat_RptTemplateDsc_Files',65), ('Stat_RptTemplates',66), ('Stat_RptTemplates_ConvertTable',67), ('Stat_RptTemplates_ConvertTable_party',68), ('Stat_RptUnitDMDfn',69), ('Stat_RptUnitRel',70), ('Stat_RptUnitRptRelate',71), ('Stat_RptUnits',72), ('Stat_RptUnits_Hiber',73), ('Stat_RptUnits_Periods_R',74), ('Stat_RptUnitSubmit',75), ('Stat_RptUnitSubmitState',76), ('Stat_RptYears_ConvertTable',77), ('Stat_RptYears_ConvertTable_party',78), ('stat_stat',79), ('StatValue',80), ('stat_input_query_template_r',81), ('stat_rptSQLview',82), ('Stat_CheckCalogInit',83), ('Stat_RptCellCmpSQL',84), ('Stat_CellMerge',85), ('Stat_RptComputeRCDfn',86), ('Stat_RptAreaDfnDisplay',87), ('Stat_RptTemplatesExp_Files',88), ('Stat_RptCiteTemplatesExpFile',89), ('Stat_RptCatalogTid',91), ('Stat_RptTemplatesExpDfn',102) */ --新表 /* ('stat_input_query_template_r',11), ('stat_rptSQLview',12), ('Stat_CheckCalogInit',13), ('Stat_RptCellCmpSQL',14), ('Stat_CellMerge',15), ('Stat_RptComputeRCDfn',16), ('Stat_RptAreaDfnDisplay',17), ('Stat_RptTemplatesExp_Files',18), ('Stat_RptCiteTemplatesExpFile',19), ('Stat_RptTemplatesExpData36FB5559-04ED-4801-BAF7-4BEE927C8E69',20), ('Stat_RptCatalogTid',21), ('Stat_RptTemplatesExpData508E29B3-256B-4775-A26B-090A9DF241F7',22), ('Stat_RptCheckInfofe1d51f5-3709-4115-b693-4cbd6d125360',23), ('Stat_RptDatafe1d51f5-3709-4115-b693-4cbd6d125360',24), ('Stat_RptPostilfe1d51f5-3709-4115-b693-4cbd6d125360',25), ('Stat_RptTemplatesExpDatafe1d51f5-3709-4115-b693-4cbd6d125360',26), ('Stat_RptDataFilefe1d51f5-3709-4115-b693-4cbd6d125360',27), ('Stat_RptDataLogfe1d51f5-3709-4115-b693-4cbd6d125360',28), ('Stat_RptCommentfe1d51f5-3709-4115-b693-4cbd6d125360',29), ('Stat_RptCheckStatefe1d51f5-3709-4115-b693-4cbd6d125360',30), ('Stat_RptFeedbackInfofe1d51f5-3709-4115-b693-4cbd6d125360',31) */ ('Stat_RptCatalog',111), ('Stat_RptCatalog_Files',112), ('Stat_RptCatalogCfg',113), ('Stat_RptCatalogType',114), ('Stat_RptGroups',115), ('Stat_RptGroups_ConvertTable',116), ('Stat_RptGroups_ConvertTable_party',117), ('Stat_RptGroupsCatalog_R',118), ('Stat_RptNodePeriods',119), ('Stat_RptNodeState',120), ('Stat_RptUnits',121), ('Stat_RptTemplates',122), ('Stat_RptPeriodsCatalog_R',123), ('Stat_CollectPhotoCheck',124), ('Stat_CollectTemplateData',125), ('Stat_CollectTemplateDefine',126), ('Stat_CollectTPContentDefine',127), ('Stat_CollectTPData',128), ('Stat_RptAreaDfn',129), ('Stat_RptAreaDfnEdit',130), ('Stat_RptCellPostil',131), ('Stat_RptCellSQL',132), ('Stat_RptCellState',133), ('Stat_RptCheckCdt',134), ('Stat_RptCheckPoint',135), ('Stat_RptCheckPointNodeState',136), ('Stat_RptCheckScope',137), ('Stat_RptCheckStateDesc',138), ('Stat_RptCheckType',139), ('Stat_RptCiteGid',140), ('Stat_RptCitePostilFile',141), ('Stat_RptCiteTid',142), ('Stat_RptCompareTempCell',143), ('Stat_RptCompareTemplates',144), ('Stat_RptCompareUnit',145), ('Stat_RptCompareValue',146), ('Stat_RptCoverDfn',147), ('Stat_RptCoverPos',148), ('Stat_RptData018c339f-50a7-4a49-b5fc-a926f0cd3105',149), ('Stat_RptData06CCF612-192B-4A9D-B2B1-479F6CB5A419',150), ('Stat_RptData0c40958a-0589-4cf0-9233-d08c681c6ee9',151), ('Stat_RptData0C6BAC9A-1AE8-4A06-85C3-B4550723CBAA',152), ('Stat_RptData1044A0C0-1E38-43FD-A9B5-9EF0200F0B81',153), ('Stat_RptData16D1E3FE-8EB7-4FCB-B8C5-7B853299C68B',154), ('Stat_RptData1A8025CE-F244-4878-9558-7D74797CB4BE',155), ('Stat_RptData1dc23984-8b68-4247-b949-ae11ecd29506',156), ('Stat_RptData24c2b4a1-8ad5-4aef-8c29-8c65a8060640',157), ('Stat_RptData2C0A2872-1A48-4319-988A-7D3A456CCD77',158), ('Stat_RptData2F33B2F0-E518-439A-8A62-5F0749CFB6F0',159), ('Stat_RptData36fb5559-04ed-4801-baf7-4bee927c8e69',160), ('Stat_RptData385870E1-B37C-4DBB-ABF2-D5FD530A8182',161), ('Stat_RptData508e29b3-256b-4775-a26b-090a9df241f7',162), ('Stat_RptData5109af75-914a-43b4-97a1-e75dace26d8c',163), ('Stat_RptData634A7797-E3DE-41D1-88CA-DEF576AA78FD',164), ('Stat_RptData7184DA0B-69E7-4D1A-9523-E33CA6F67447',165), ('Stat_RptData739FF3B0-C46A-4FBA-84CB-D611BDCA6947',166), ('Stat_RptData7D7D518B-6CE7-4E29-B908-5D0C08C43FB3',167), ('Stat_RptData8733B39E-EF0F-44F5-93DB-D0071F2F2B96',168), ('Stat_RptData92B34403-94CA-4558-9729-4A36821AB8CA',169), ('Stat_RptData975A9AE5-1501-4B42-A965-8A5DEDD5ED29',170), ('Stat_RptDataAE19144E-4CFD-4B94-A4B3-32754CF5203E',171), ('Stat_RptDatab8474faf-0eb7-47ac-8ade-2560b91c9e31',172), ('Stat_RptDatac7edbdcb-6a70-4669-8873-e62e0ecc8738',173), ('Stat_RptDataDD5BC9C0-DFC6-4F3F-AD1B-0E9C0D1363B9',174), ('Stat_RptDataDefine',175), ('Stat_RptDataDfn',176), ('Stat_RptDatae11d96a1-22a3-400b-b151-3fcc02e34e04',177), ('Stat_RptDataE9434F8F-3422-4544-93C7-5CDE36DEBE57',178), ('Stat_RptDataefc514b3-eefa-4c3c-ae25-25e50ab27553',179), ('Stat_RptDataTemp',180), ('Stat_RptFeedbackCmdInfo',181), ('Stat_RptFeedbackType',182), ('Stat_RptGrpType',183), ('Stat_RptGrpUnitDfn',184), ('Stat_RptGrpUnits_Hiber',185), ('Stat_RptJoinTbl',186), ('Stat_RptPostil_Files',187), ('Stat_RptQryTemplates',188), ('Stat_RptQryTemplatesDfn',189), ('Stat_RptRCDfn',190), ('Stat_RptRcFunction',191), ('Stat_RptRcSelectRange',192), ('Stat_RptReSqlSelect',193), ('Stat_RptSpecialAreaType',194), ('Stat_RptTemplate_Files',195), ('Stat_RptTemplateDsc_Files',196), ('Stat_RptTemplates_ConvertTable',197), ('Stat_RptTemplates_ConvertTable_party',198), ('Stat_RptUnitDMDfn',199), ('Stat_RptUnitRel',200), ('Stat_RptUnitRptRelate',201), ('Stat_RptUnits_Hiber',202), ('Stat_RptUnits_Periods_R',203), ('Stat_RptUnitSubmit',204), ('Stat_RptUnitSubmitState',205), ('Stat_RptYears_ConvertTable',206), ('Stat_RptYears_ConvertTable_party',207), ('stat_stat',208), ('stat_input_query_template_r',211), ('stat_rptSQLview',212), ('Stat_CheckCalogInit',213), ('Stat_RptCellCmpSQL',214), ('Stat_CellMerge',215), ('Stat_RptComputeRCDfn',216), ('Stat_RptAreaDfnDisplay',217), ('Stat_RptTemplatesExp_Files',218), ('Stat_RptCiteTemplatesExpFile',219), ('Stat_RptTemplatesExpData36FB5559-04ED-4801-BAF7-4BEE927C8E69',220), ('Stat_RptCatalogTid',221), ('Stat_RptTemplatesExpData508E29B3-256B-4775-A26B-090A9DF241F7',222), ('Stat_RptCheckInfofe1d51f5-3709-4115-b693-4cbd6d125360',223), ('Stat_RptDatafe1d51f5-3709-4115-b693-4cbd6d125360',224), ('Stat_RptPostilfe1d51f5-3709-4115-b693-4cbd6d125360',225), ('Stat_RptTemplatesExpDatafe1d51f5-3709-4115-b693-4cbd6d125360',226), ('Stat_RptDataFilefe1d51f5-3709-4115-b693-4cbd6d125360',227), ('Stat_RptDataLogfe1d51f5-3709-4115-b693-4cbd6d125360',228), ('Stat_RptCommentfe1d51f5-3709-4115-b693-4cbd6d125360',229), ('Stat_RptCheckStatefe1d51f5-3709-4115-b693-4cbd6d125360',230), ('Stat_RptFeedbackInfofe1d51f5-3709-4115-b693-4cbd6d125360',231), ('Stat_RptTemplatesExpDfn',232) INSERT INTO @temp SELECT ROW_NUMBER() OVER(partition by d.name ORDER BY d.name) AS id, d.name tblcod, c.name AS colcod,tbls.odr FROM sys.key_constraints a LEFT JOIN sys.index_columns b ON a.parent_object_id = b.object_id LEFT JOIN sys.columns c ON b.object_id = c.object_id AND b.column_id = c.column_id LEFT JOIN sysobjects d ON a.parent_object_id = d.id INNER JOIN @tbls tbls ON d.name=tbls.tblname WHERE b.index_id = 1 --SELECT DISTINCT tmp.*,t.mid FROM @temp tmp LEFT OUTER JOIN ( -- SELECT MAX(ID)AS mid,tblcod FROM @temp t GROUP BY tblcod --)AS t ON t.mid=tmp.id AND t.tblcod=tmp.tblcod ORDER BY tmp.tblcod,tmp.id --SELECT DISTINCT @sql=@sql+'T1.' + tmp.colcod + '=T2.' + tmp.colcod+CASE ISNULL( t.mid,'') WHEN ''THEN '' ELSE' and 'END ,@sql1=@sql1+'T1.' + tmp.colcod+' is null and ' SELECT --tmp.*,t.mid --, CASE id WHEN 1 THEN ' if object_id(''['+tmp.tblcod+']'') is null select top 0 * into NEI.DBO.['+tmp.TBLCOD+'] from [127.0.0.1\ABC].wai.dbo.['+tmp.TBLCOD+'] where 0=1 go' ELSE ''END AS cdt0 ,CASE id WHEN 1 THEN 'insert into nei.dbo.['+tmp.tblcod+'] select T1.* from [127.0.0.1\ABC].wai.dbo.['+tmp.TBLCOD+'] T1 left outer join NEI.DBO.['+tmp.TBLCOD+'] T2 ON ' ELSE ''END AS cdt1 ,'T1.'+tmp.colcod+'=T2.'+tmp.colcod +CASE ISNULL(mid,'')WHEN '' THEN ' and 'ELSE ' where T2.'+tmp.colcod +' is null /*'+tmp.tblcod+'*/GO'END AS cdt --,'/*'+tmp.tblcod+'*/' AS [dis] FROM @temp tmp LEFT OUTER JOIN ( SELECT MAX(ID) AS mid, tblcod FROM @temp t GROUP BY tblcod ) AS t ON t.mid = tmp.id AND t.tblcod = tmp.tblcod --GROUP BY t ORDER BY tmp.odr, tmp.id --PRINT @sql+@sql1
sql2005.sql2008批量merge.sql -
/************************************************************ * Code formatted by SoftTree SQL Assistant ?v6.0.70 * Time: 2014/5/4 14:38:33 ************************************************************/ DECLARE @temp TABLE (id INT,tblcod NVARCHAR(1000),colcod NVARCHAR(1000),odr int) DECLARE @tbls TABLE (tblname NVARCHAR(1000),odr int) DELETE FROM @tbls DELETE FROM @temp INSERT INTO @tbls VALUES /*('Stat_CollectPhotoCheck',11), ('Stat_CollectTemplateData',12), ('Stat_CollectTemplateDefine',13), ('Stat_CollectTPContentDefine',14), ('Stat_CollectTPData',15), ('Stat_RptAreaDfn',16), ('Stat_RptAreaDfnEdit',17), ('Stat_RptCatalog',18), ('Stat_RptCatalog_Files',19), ('Stat_RptCatalogCfg',20), ('Stat_RptCatalogType',21), ('Stat_RptCellPostil',22), ('Stat_RptCellSQL',23), ('Stat_RptCellState',24), ('Stat_RptCheckCdt',25), ('Stat_RptCheckPoint',26), ('Stat_RptCheckPointNodeState',27), ('Stat_RptCheckScope',28), ('Stat_RptCheckStateDesc',29), ('Stat_RptCheckType',30), ('Stat_RptCiteGid',31), ('Stat_RptCitePostilFile',32), ('Stat_RptCiteTid',33), ('Stat_RptCompareTempCell',34), ('Stat_RptCompareTemplates',35), ('Stat_RptCompareUnit',36), ('Stat_RptCompareValue',37), ('Stat_RptCoverDfn',38), ('Stat_RptCoverPos ',39), ('Stat_RptDataDefine',40), ('Stat_RptDataDfn',41), ('Stat_RptDataTemp',42), ('Stat_RptFeedbackCmdInfo',43), ('Stat_RptFeedbackType',44), ('Stat_RptGroups',45), ('Stat_RptGroups_ConvertTable',46), ('Stat_RptGroups_ConvertTable_party',47), ('Stat_RptGroupsCatalog_R',48), ('Stat_RptGrpType',49), ('Stat_RptGrpUnitDfn',50), ('Stat_RptGrpUnits_Hiber',51), ('Stat_RptJoinTbl',52), ('Stat_RptNodePeriods',53), ('Stat_RptNodeState',54), ('Stat_RptPeriodsCatalog_R',55), ('Stat_RptPostil_Files',56), ('Stat_RptQryTemplates',57), ('Stat_RptQryTemplatesDfn',58), ('Stat_RptRCDfn',59), ('Stat_RptRcFunction',60), ('Stat_RptRcSelectRange',61), ('Stat_RptReSqlSelect',62), ('Stat_RptSpecialAreaType',63), ('Stat_RptTemplate_Files',64), ('Stat_RptTemplateDsc_Files',65), ('Stat_RptTemplates',66), ('Stat_RptTemplates_ConvertTable',67), ('Stat_RptTemplates_ConvertTable_party',68), ('Stat_RptUnitDMDfn',69), ('Stat_RptUnitRel',70), ('Stat_RptUnitRptRelate',71), ('Stat_RptUnits',72), ('Stat_RptUnits_Hiber',73), ('Stat_RptUnits_Periods_R',74), ('Stat_RptUnitSubmit',75), ('Stat_RptUnitSubmitState',76), ('Stat_RptYears_ConvertTable',77), ('Stat_RptYears_ConvertTable_party',78), ('stat_stat',79), ('StatValue',80), ('stat_input_query_template_r',81), ('stat_rptSQLview',82), ('Stat_CheckCalogInit',83), ('Stat_RptCellCmpSQL',84), ('Stat_CellMerge',85), ('Stat_RptComputeRCDfn',86), ('Stat_RptAreaDfnDisplay',87), ('Stat_RptTemplatesExp_Files',88), ('Stat_RptCiteTemplatesExpFile',89), ('Stat_RptCatalogTid',91), ('Stat_RptTemplatesExpDfn',102) */ --新表 /* ('stat_input_query_template_r',11), ('stat_rptSQLview',12), ('Stat_CheckCalogInit',13), ('Stat_RptCellCmpSQL',14), ('Stat_CellMerge',15), ('Stat_RptComputeRCDfn',16), ('Stat_RptAreaDfnDisplay',17), ('Stat_RptTemplatesExp_Files',18), ('Stat_RptCiteTemplatesExpFile',19), ('Stat_RptTemplatesExpData36FB5559-04ED-4801-BAF7-4BEE927C8E69',20), ('Stat_RptCatalogTid',21), ('Stat_RptTemplatesExpData508E29B3-256B-4775-A26B-090A9DF241F7',22), ('Stat_RptCheckInfofe1d51f5-3709-4115-b693-4cbd6d125360',23), ('Stat_RptDatafe1d51f5-3709-4115-b693-4cbd6d125360',24), ('Stat_RptPostilfe1d51f5-3709-4115-b693-4cbd6d125360',25), ('Stat_RptTemplatesExpDatafe1d51f5-3709-4115-b693-4cbd6d125360',26), ('Stat_RptDataFilefe1d51f5-3709-4115-b693-4cbd6d125360',27), ('Stat_RptDataLogfe1d51f5-3709-4115-b693-4cbd6d125360',28), ('Stat_RptCommentfe1d51f5-3709-4115-b693-4cbd6d125360',29), ('Stat_RptCheckStatefe1d51f5-3709-4115-b693-4cbd6d125360',30), ('Stat_RptFeedbackInfofe1d51f5-3709-4115-b693-4cbd6d125360',31) */ ('Stat_RptCatalog',111), ('Stat_RptCatalog_Files',112), ('Stat_RptCatalogCfg',113), ('Stat_RptCatalogType',114), ('Stat_RptGroups',115), ('Stat_RptGroups_ConvertTable',116), ('Stat_RptGroups_ConvertTable_party',117), ('Stat_RptGroupsCatalog_R',118), ('Stat_RptNodePeriods',119), ('Stat_RptNodeState',120), ('Stat_RptUnits',121), ('Stat_RptTemplates',122), ('Stat_RptPeriodsCatalog_R',123), ('Stat_CollectPhotoCheck',124), ('Stat_CollectTemplateData',125), ('Stat_CollectTemplateDefine',126), ('Stat_CollectTPContentDefine',127), ('Stat_CollectTPData',128), ('Stat_RptAreaDfn',129), ('Stat_RptAreaDfnEdit',130), ('Stat_RptCellPostil',131), ('Stat_RptCellSQL',132), ('Stat_RptCellState',133), ('Stat_RptCheckCdt',134), ('Stat_RptCheckPoint',135), ('Stat_RptCheckPointNodeState',136), ('Stat_RptCheckScope',137), ('Stat_RptCheckStateDesc',138), ('Stat_RptCheckType',139), ('Stat_RptCiteGid',140), ('Stat_RptCitePostilFile',141), ('Stat_RptCiteTid',142), ('Stat_RptCompareTempCell',143), ('Stat_RptCompareTemplates',144), ('Stat_RptCompareUnit',145), ('Stat_RptCompareValue',146), ('Stat_RptCoverDfn',147), ('Stat_RptCoverPos',148), ('Stat_RptData018c339f-50a7-4a49-b5fc-a926f0cd3105',149), ('Stat_RptData06CCF612-192B-4A9D-B2B1-479F6CB5A419',150), ('Stat_RptData0c40958a-0589-4cf0-9233-d08c681c6ee9',151), ('Stat_RptData0C6BAC9A-1AE8-4A06-85C3-B4550723CBAA',152), ('Stat_RptData1044A0C0-1E38-43FD-A9B5-9EF0200F0B81',153), ('Stat_RptData16D1E3FE-8EB7-4FCB-B8C5-7B853299C68B',154), ('Stat_RptData1A8025CE-F244-4878-9558-7D74797CB4BE',155), ('Stat_RptData1dc23984-8b68-4247-b949-ae11ecd29506',156), ('Stat_RptData24c2b4a1-8ad5-4aef-8c29-8c65a8060640',157), ('Stat_RptData2C0A2872-1A48-4319-988A-7D3A456CCD77',158), ('Stat_RptData2F33B2F0-E518-439A-8A62-5F0749CFB6F0',159), ('Stat_RptData36fb5559-04ed-4801-baf7-4bee927c8e69',160), ('Stat_RptData385870E1-B37C-4DBB-ABF2-D5FD530A8182',161), ('Stat_RptData508e29b3-256b-4775-a26b-090a9df241f7',162), ('Stat_RptData5109af75-914a-43b4-97a1-e75dace26d8c',163), ('Stat_RptData634A7797-E3DE-41D1-88CA-DEF576AA78FD',164), ('Stat_RptData7184DA0B-69E7-4D1A-9523-E33CA6F67447',165), ('Stat_RptData739FF3B0-C46A-4FBA-84CB-D611BDCA6947',166), ('Stat_RptData7D7D518B-6CE7-4E29-B908-5D0C08C43FB3',167), ('Stat_RptData8733B39E-EF0F-44F5-93DB-D0071F2F2B96',168), ('Stat_RptData92B34403-94CA-4558-9729-4A36821AB8CA',169), ('Stat_RptData975A9AE5-1501-4B42-A965-8A5DEDD5ED29',170), ('Stat_RptDataAE19144E-4CFD-4B94-A4B3-32754CF5203E',171), ('Stat_RptDatab8474faf-0eb7-47ac-8ade-2560b91c9e31',172), ('Stat_RptDatac7edbdcb-6a70-4669-8873-e62e0ecc8738',173), ('Stat_RptDataDD5BC9C0-DFC6-4F3F-AD1B-0E9C0D1363B9',174), ('Stat_RptDataDefine',175), ('Stat_RptDataDfn',176), ('Stat_RptDatae11d96a1-22a3-400b-b151-3fcc02e34e04',177), ('Stat_RptDataE9434F8F-3422-4544-93C7-5CDE36DEBE57',178), ('Stat_RptDataefc514b3-eefa-4c3c-ae25-25e50ab27553',179), ('Stat_RptDataTemp',180), ('Stat_RptFeedbackCmdInfo',181), ('Stat_RptFeedbackType',182), ('Stat_RptGrpType',183), ('Stat_RptGrpUnitDfn',184), ('Stat_RptGrpUnits_Hiber',185), ('Stat_RptJoinTbl',186), ('Stat_RptPostil_Files',187), ('Stat_RptQryTemplates',188), ('Stat_RptQryTemplatesDfn',189), ('Stat_RptRCDfn',190), ('Stat_RptRcFunction',191), ('Stat_RptRcSelectRange',192), ('Stat_RptReSqlSelect',193), ('Stat_RptSpecialAreaType',194), ('Stat_RptTemplate_Files',195), ('Stat_RptTemplateDsc_Files',196), ('Stat_RptTemplates_ConvertTable',197), ('Stat_RptTemplates_ConvertTable_party',198), ('Stat_RptUnitDMDfn',199), ('Stat_RptUnitRel',200), ('Stat_RptUnitRptRelate',201), ('Stat_RptUnits_Hiber',202), ('Stat_RptUnits_Periods_R',203), ('Stat_RptUnitSubmit',204), ('Stat_RptUnitSubmitState',205), ('Stat_RptYears_ConvertTable',206), ('Stat_RptYears_ConvertTable_party',207), ('stat_stat',208), ('stat_input_query_template_r',211), ('stat_rptSQLview',212), ('Stat_CheckCalogInit',213), ('Stat_RptCellCmpSQL',214), ('Stat_CellMerge',215), ('Stat_RptComputeRCDfn',216), ('Stat_RptAreaDfnDisplay',217), ('Stat_RptTemplatesExp_Files',218), ('Stat_RptCiteTemplatesExpFile',219), ('Stat_RptTemplatesExpData36FB5559-04ED-4801-BAF7-4BEE927C8E69',220), ('Stat_RptCatalogTid',221), ('Stat_RptTemplatesExpData508E29B3-256B-4775-A26B-090A9DF241F7',222), ('Stat_RptCheckInfofe1d51f5-3709-4115-b693-4cbd6d125360',223), ('Stat_RptDatafe1d51f5-3709-4115-b693-4cbd6d125360',224), ('Stat_RptPostilfe1d51f5-3709-4115-b693-4cbd6d125360',225), ('Stat_RptTemplatesExpDatafe1d51f5-3709-4115-b693-4cbd6d125360',226), ('Stat_RptDataFilefe1d51f5-3709-4115-b693-4cbd6d125360',227), ('Stat_RptDataLogfe1d51f5-3709-4115-b693-4cbd6d125360',228), ('Stat_RptCommentfe1d51f5-3709-4115-b693-4cbd6d125360',229), ('Stat_RptCheckStatefe1d51f5-3709-4115-b693-4cbd6d125360',230), ('Stat_RptFeedbackInfofe1d51f5-3709-4115-b693-4cbd6d125360',231), ('Stat_RptTemplatesExpDfn',232) INSERT INTO @temp SELECT ROW_NUMBER() OVER(partition by d.name ORDER BY d.name) AS id, d.name tblcod, c.name AS colcod,tbls.odr FROM sys.key_constraints a LEFT JOIN sys.index_columns b ON a.parent_object_id = b.object_id LEFT JOIN sys.columns c ON b.object_id = c.object_id AND b.column_id = c.column_id LEFT JOIN sysobjects d ON a.parent_object_id = d.id INNER JOIN @tbls tbls ON d.name=tbls.tblname WHERE b.index_id = 1 --SELECT DISTINCT tmp.*,t.mid FROM @temp tmp LEFT OUTER JOIN ( -- SELECT MAX(ID)AS mid,tblcod FROM @temp t GROUP BY tblcod --)AS t ON t.mid=tmp.id AND t.tblcod=tmp.tblcod ORDER BY tmp.tblcod,tmp.id --SELECT DISTINCT @sql=@sql+'T1.' + tmp.colcod + '=T2.' + tmp.colcod+CASE ISNULL( t.mid,'') WHEN ''THEN '' ELSE' and 'END ,@sql1=@sql1+'T1.' + tmp.colcod+' is null and ' SELECT --tmp.*,t.mid --, CASE id WHEN 1 THEN ' if object_id(''['+tmp.tblcod+']'') is null select top 0 * into NEI.DBO.['+tmp.TBLCOD+'] from [127.0.0.1\ABC].wai.dbo.['+tmp.TBLCOD+'] where 0=1 go' ELSE ''END AS cdt0 ,CASE id WHEN 1 THEN 'insert into nei.dbo.['+tmp.tblcod+'] select T1.* from [127.0.0.1\ABC].wai.dbo.['+tmp.TBLCOD+'] T1 left outer join NEI.DBO.['+tmp.TBLCOD+'] T2 ON ' ELSE ''END AS cdt1 ,'T1.'+tmp.colcod+'=T2.'+tmp.colcod +CASE ISNULL(mid,'')WHEN '' THEN ' and 'ELSE ' where T2.'+tmp.colcod +' is null /*'+tmp.tblcod+'*/GO'END AS cdt --,'/*'+tmp.tblcod+'*/' AS [dis] FROM @temp tmp LEFT OUTER JOIN ( SELECT MAX(ID) AS mid, tblcod FROM @temp t GROUP BY tblcod ) AS t ON t.mid = tmp.id AND t.tblcod = tmp.tblcod --GROUP BY t ORDER BY tmp.odr, tmp.id --PRINT @sql+@sql1
将Excel中的数据转换成sql Insert语句.sql -
excel表格中有A、B、C三列数据,希望导入到数据库users表中,对应的字段分别是name,sex,age ,在你的excel表格中增加一列,利用excel的公式自动生成sql语句,方法如下: 1、在你的excel表格中增加一列 2、在第一行的D列,就是D1中输入公式: =CONCATENATE("INSERT INTO PERSONS_SERIAL_NUMBER(PERSONS_NO, PERSONS_NAME,ID_NUMBER) VALUES('" & B1 & "', '" & C1 & "', '" & E1 & "');") 3、此时D1已经生成了如下的sql语句: INSERT INTO PERSONS_SERIAL_NUMBER(PERSONS_NO, PERSONS_NAME,ID_NUMBER) VALUES('xxxxx', 'xxxx, 'xxxxxxxxxx'); 4、将D1的公式复制到所有行的D列 5、此时D列已经生成了所有的sql语句 6、把D列复制到一个纯文本文件中。 注意:生成的insert语句中有一个特殊字符"?",需要转换成空格。推荐使用UltraEdit进行处理。
查看表中的主键 和主键列.sql -
SELECT d.name 表名, a.name 主键名, c.name 列名 FROM sys.key_constraints a LEFT JOIN sys.index_columns b ON a.parent_object_id = b.object_id LEFT JOIN sys.columns c ON b.object_id = c.object_id AND b.column_id = c.column_id LEFT JOIN sysobjects d ON a.parent_object_id = d.id WHERE b.index_id = 1