Robert L. Stewart
rl_stewart at highstream.net
Mon May 14 08:37:32 CDT 2007
John,
As an education for you and probably some other here...
The following creates a system based default so that constraints at
the column level do not have to be created.
CREATE DEFAULT dbo.Default_0 as 0
GO
The following binds a default to a column in a specific table.
EXEC sys.sp_bindefault @defname=N'[dbo].[Default_0]',
@objname=N'[dbo].[tsysImportDefinition].[ArchivedFlag]'
GO
The code for vwImportDefColumns was just the SQL statement to create
the view used in the stored procedure.
The following is the stored procedure for creating the table from the
definitions you stored in the 2 tables that would have been created
in the code that ran before it. I have added additional comments,
areas with -- in front of them to try and make thing clearer.
CREATE PROCEDURE [dbo].[pImportCountryFile]
AS
BEGIN
-- sets the record count off so records affected is not returned
SET NOCOUNT ON;
-- declare the variables you will need for the country columns
DECLARE @CountryCode VARCHAR(3),
@CountryName VARCHAR(100),
@ColumnName VARCHAR(64),
@ColumnStart INT,
@ColumnEnd INT,
@ColumnDataType VARCHAR(15),
@ColumnLength VARCHAR(10),
-- column length defined as varchar to take MAX as a parameter
@ColumnPrecision TINYINT,
@ColumnScale TINYINT,
@DBName VARCHAR(64),
@DBTableName VARCHAR(64)
-- Declare the variable you will need for your dynamic SQL statements
DECLARE @CreateTable NVARCHAR(4000)
-- the following code is generic for creating the table
-- the only thing that would be changed is being able to
-- pass into the proc the name of the table you want to
-- do the build of. for your system, it would probably be
-- the client job information
-- create a cursor. this works like a read-only forward
scrolling recordset
-- does in Access
DECLARE cColumns CURSOR
FOR
SELECT DBName,
DBTableName,
ColumnName,
ColumnStart,
ColumnEnd,
ColumnDataType,
ColumnLength,
ColumnPrecision,
ColumnScale
FROM dbo.vwImportDefColumns
WHERE DBTableName = 'tlkpCountry'
-- opens the cursor for use
OPEN cColumns
-- reads the first record of the select statement into the
-- variables listed
FETCH NEXT FROM cColumns
INTO @DBName,
@DBTableName,
@ColumnName,
@ColumnStart,
@ColumnEnd,
@ColumnDataType,
@ColumnLength,
@ColumnPrecision,
@ColumnScale
-- build the create table sql
SET @CreateTable = 'CREATE TABLE dbo.' + @DBTableName + ' ('
+ 'PK_ID int identity(1,1), '
-- the system variable @@FETCH_STATUS will return 0 as long as
there are records
WHILE (@@FETCH_STATUS = 0) -- There are records
-- you must enclose things and group them inside of BEGIN...END
-- when there are more than 1 statement that you want to execute
BEGIN
-- build the create table sql
IF (CHARINDEX('varchar', at ColumnDataType,1) > 0)
-- CHARINDEX checks for the existance of one string
inside of another
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + '(' +
+ @ColumnLength + '), '
END
ELSE IF (CHARINDEX('int', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE IF (CHARINDEX('date', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE IF (CHARINDEX('text', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE -- money, numeric or decimal
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + '(' +
+ CAST(@ColumnPrecision AS varchar(10))
+ CAST(@ColumnScale AS varchar(10)) + '), '
-- CAST converts between data types
-- you can also use CONVERT
END
-- read the next record into the variables
FETCH NEXT FROM cColumns
INTO @DBName,
@DBTableName,
@ColumnName,
@ColumnStart,
@ColumnEnd,
@ColumnDataType,
@ColumnLength,
@ColumnPrecision,
@ColumnScale
END
-- trim the final , from the end of the string
SET @CreateTable = substring(@CreateTable,1,len(@CreateTable) - 1) + ')'
-- substring is the same as the MID function in VBA
-- Prints out the SQL statement so we can see it
PRINT @CreateTable
-- Execute the create table statement
-- uncomment out the following line to actually execute the SQL
-- statement that was built
-- EXEC sp_ExecuteSql @CreateTable
-- clean up the CURSOR by closing and deallocating it
CLOSE cColumns
DEALLOCATE cColumns
END
I hope that helps some with what it does.
If any one has questions about it, let me know.
By the way John, with the CLR integration, SQL Server can run the VB.net code
you can up with for the initial processing also.
Robert
At 06:25 PM 5/11/2007, you wrote:
>Date: Fri, 11 May 2007 14:34:31 -0400
>From: "jwcolby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Reciprocity
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <20070511183431.06052BCF5 at smtp-auth.no-ip.com>
>Content-Type: text/plain; charset="us-ascii"
>
>Robert,
>
>I do thank you for your assistance. The scripts are way cool from what I can
>understand of them. Please understand that I kind of work from what I need
>now to what I need eventually and this appears to be DEFINITELY what I need
>eventually. However I am, as we speak, migrating my VBA code to VB.NET.
>That has pretty much captured my entire attention.
>
>Perhaps you do not realize that I am VERY inexperienced in SQL Server, in
>fact have only ever written and am using exactly one Sproc. Thus I am
>completely unable to grasp the entirety of what you are trying to
>accomplish. I have not tested this stuff because I don't even know how. I
>understand everything down through the tsysImportDefinition (at least on a
>conceptual level), though I do not understand the EXEC sys.SP_BindDefault...
>No clue at all there. vwImportDefColumns not a clue. pImportCountryFile
>not a clue, though I assume that perhaps this is an "example" of using it?
>This appears to be the actual meat of the matter but it is so far over my
>head that "I'll have to get back to you" on that one.
>
>I have no doubt that whatever it does, it does well, and hopefully I will be
>able to use it soon. As for today.... I just don't have the SQL Server
>skills to apply it.
>
>As for "Should I bother with proceeding with the text file read?", I don't
>know. How can I answer that? You are apparently providing an all SQL
>solution to the problem which is really cool, and provides me with example
>code of how to do things, which is also really cool, but until I can absorb
>it I am not sure I can use it.
>
>If you have the time to explain this stuff, line by line or block by block,
>then perhaps yes, let's continue. I am not stupid, just ignorant. This
>looks like an education in progress. Of course I am still in first grade.
>;-)
>
>I do think this would make a fascinating thread though if you do get down
>and explain it.
>
>John W. Colby