The following stored procedure can help you add a table directly from the post-sample data to the sample data, and save a large number of manual input data.
/ * Create Table from your Web Page Data * 2004-JAN-1, OpenVMS, V0.1 * 2004-JAN-2, V0.5, Add Tab & Blank Values Logical * 2004-JAN-3, V1.0, Add SQL Statement Generation * 2004-JAN-4, V1.1, FIX DATATYPE LIKE DECIMAL (4, 2) BUG * 2004-JAN-4, V1.2, FIX Field Name bug ** Sample Call: in SQL Query Analyzexec dbo. Create_Table '## T2', 'VARCHAR (20), DateTime K', 'Id Andate 99101 2002-11-24 00: 00: 00: 00: 00: 00:00: 2003-11-29 00:00: 00.00099101 2003-12-20 00: 00:00.000 '
Note: 1 If you use a temporary table name, you can only use the global temporary table ##, otherwise you can access 2 If you don't column name, you need to manually add the name 3 field name that is not allowed to include the air 4 at least one line of data in the first line of data. Otherwise, there is no meaning 5 field value to write NULL, and any symbol in the field value is a part of the value of the value. 6 does not have the type matching check 7 of the defined type and value, and the space is in the specified value, the method is in this type definition The tail adds alphabet K, such as DataTime K, 8, with single quotes, need to reply '- "' * /
IF exists (select name from sysoads where name = n'create_table 'and type =' p ') Drop Procedure Create_tableGo
Create proc dbo.create_table@table_name varchar (60), --- table name @ DataType varchar (1000), --- Separated by comma ',' @ Str Nvarchar (3000) --- Input string pasted from web pageasbegindeclare @dt Table (ID Int Id Id Id Id ", FLD_NAME VARCHAR (30), FLD_TYPE VARCHAR (20), Blank Int Declare @Sqlt Table (SQL_Statement Varchar (8000)) DECLARE @tmp varchar (1000), @ Num1 Int, @ Num2 int, @ SQL NVARCHAR (4000) Declare @a nvarchar (3000), @ i int, @ J Int, @ K Int, @ m int, @ x nvarchar (1000)
SET NOCOUNT ONIF Object_ID (@Table_name) Is Not Null Begin Set @ a = 'Table' @ Table_name 'Exists, Choose A New One!' Raiserror (@ a, 16, 1) Return End - Extract Type Set @DataType = Lower (@DataType, '', ')) SET @ TMP = @ DataTypeset @ i = 1set @ Num1 = 0
While @i> 0begin select @ i = charindex (',', @ Datatype) --check DataType Like Decimal (10, 4) IF @i> Charindex ('(', @ DataType) and @i
- Check Type IF EXISTS (SELECT FLD_TYPE from @dt where (', fld_type)> 0 Then Left (FLD_TYPE, CHARINDEX (' (', fld_type) - Else FLD_TYPE END NOT IN (SELECT Name from systypes) or Charindex ('(', fld_type) * Charindex (')', FLD_TYPE) = 0 And Charindex ('(', FLD_TYPE) CHARINDEX (')', FLD_TYPE)> 0) Begin Raiserror ('Error) Data type. ', 16, 1) Return End - extract fields and data set @ a = replace (@ Str, char (9),' ') --- tab charset @ a = r = = = r (LTRIM (@a)) IF Charindex (25), Right (@ a, len (@A) -1))) = 0 or LEN (@a) = 0 Begin Raiserror ('Input Data Error, Check Your Data) , 16, 1) Return End
IF Object_ID ('Tempdb.dbo. # xx') IS Not Null Drop Table #XXSelect Identity (Int, 1, 1) ID, Space (50) Val Into #xx where 1 = 2set @ K = 0Set @ Num2 = 0set @ M = 0WHILE LEN (@A)> 0 begin set @ i = 1 set @ x = left (@ a, 1)
IF @ x = char (10) Begin if @m> @n @ Num2 and @ Num2> 0 and Charindex ('k', @ DataType) = 0 Begin Raiserror ('Number of Data Is Greater Than THE Columns, you Should Add K in Data Type Difinition. ', 16, 1) Return End Set @ m = 0 End
IF @x not in ('', char (13), char (10)) begin set @ i = charIndex ('', @ a) set @ J = charIndex (char (13) char (10), @ a ) Set @ m = @ m 1 if @K <> - 1 set @ k = @ K 1 if @J> 0 and (@J <@i @J> @i and substring (@ a, @ i , @ J- @ i) = Space (@ J- @ i)) Begin set @ i = @ j iF @K> @n @k@> - 1 set @n = @ K set @ k = -1 end IF @ i = 0 set @ i = (case when @J> 0 Then @J else LEN (@a) 1 End) SELECT @ j = max (id) from #xx if @ m = 1 or @J <= @ Num1 or (SELECT Blank from @dt where id = @ m-1) <> 1 begin if @J <@ Num1 set @x = '[' replace (RTRIM (l @ a, @ i-1)) , ']', ']]) '] 'Else set @ x = rtrim (left (@a,' '', '' '), @ i-1)) Insert Into # XX (VAL) VALUES (@X) Else Begin Update #xx set val = val ' rtRim (left (@ a, @ i-1)) where id = @ J set @ m = @ m-1 End end IF @i
IF @ Num1 <> @ Num2beginraiserror ('Datatype Dismatch The Column ", 16, 1) Returnend
- if use the exists template table, drop itif object_id ('tempdb.dbo.' @ Table_name) IS not null exec ('Drop Table' @ Table_Name) - Buy table update a.fld_name = B.VALFROM @ DT A, # xx bwhere a.id = B.ID and A.ID <= @ Num1
Set @a = '' SELECT @ A = @ A FLD_NAME '' FLD_TYPE ',' from @dt where id <= @ Num1set @ a = left (@ a, len (@a) -1) set @ SQL = 'Create Table' @ Table_name '(' @ A ')' Exec (@SQL) Insert Into @Sqlt Select @SQL
- Insert data set @ i = @ Num1 1WHILE @i <= (select max (id) from #xx) beginset @a = 'set @ SQL =' SELECT @ S @ S VAL '', '' ' ' from (Select Top ' Convert (VARCHAR (10), @ Num1) ' Val from #xx where id> = ' convert (varchar (10), (@ i)) ') a'exec sp_executesql @ SQL, N '@ s nvarchar (3000) Output', @ a Output
Set @ a = left (@ a, len (@A) -1)
Set @ SQL = 'INSERT INTO' @ Table_name 'SELECT' @ Aif Len (@A)> 0 Exec (@SQL) INSERT INTO @Sqlt Select @SQL set @ i = @ i @ Num Lee
Select * from @ Sqlt - select * from @dtexec ('select * from' @ Table_Name) Set NoCount Offend
test
Exec dbo.create_table '## t2', 'Varchar (20), DateTime K', '
ID andATE
99101 2002-11-24 00: 00: 00.000
99101 2003-11-15 00: 00: 00.000
99101 2003-11-29 00: 00: 00.000
99101 2003-12-20 00: 00: 00.000 '
Results SQL_STATEMENT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- Create Table ## t2 (id varchar (20), andate datetime) Insert Into ## T2 SELECT '99101', '2002-11-24 00: 02-11-24 00:' 200.000'Insert INTO ## T2 SELECT '99101', '2003-11-15 00: 00.000'Nsert Into ## T2 SELECT' 99101 ',' 2003-11-29 00: 00.000'NSERT INTO ## T2 SELECT '99101', ' 2003-12-20 00: 00: 00.000'ID andate ----------------------------------- ---------- 99101 2002-11-24 00: 00: 00.00099101 2003-11-15 00: 00: 00:0099101 2003-11-29 00: 00: 00.00099101 2003-12-20 00:00: 00.000
Oracle's writing is in the test.
