Application Note for QuNect ODBC for Quick Base
Mirroring the Schema of a Single SQL Server Table in Quick Base
The following stored procedure will create and empty table in Quick Base with all of the fields from a SQL Server table. It takes only three parameters. This first parameter is the name of the SQL Server table. The second parameter is the dbid of the parent application where you want the Quick Base table to be created. You can find the parent DBID of your Quick Base application going to the home of the Quick Base application in question and looking at the characters in the URL after the last forward slash. The third and last parameter is the name you want to give to the new table that will be created in the Quick Base application.
After running this stored procedure you will probably want to move data from your SQL Server table to your new Quick Base table using Synching a SQL Server table in Quick Base or Copying Records from SQL Server to Quick Base
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Claude von Roesgen
-- Create date: 5/13/2010
-- Description: Mirror table from SQL Server to Quick Base
-- =============================================
-- This stored procedure depends on the existence of a linked server called
-- Quick Base. To create this linked server run the following SQL:
--
-- EXEC sp_addlinkedserver
-- @server = 'Quick Base',
-- @provider = 'MSDASQL',
-- @srvproduct = 'QuNect',
-- @provstr = 'DRIVER={QuNect ODBC for Quick Base};UID=YourQuickBaseEmailOrScreenName;PWD=YourPassword'
-- GO
-- After you have your linked server in place you're ready to run the stored
-- procedure below.
CREATE PROCEDURE [dbo].[mirrorSQLServerTableInQuickBase]
@sqlServerTableName Varchar(255),
@parentDBID Varchar(255),
@newTableName Varchar(255)
AS
BEGIN
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[qunect_columns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qunect_columns]
create table [qunect_columns]
(
TABLE_QUALIFIER sysname collate database_default NULL,
TABLE_OWNER sysname collate database_default NULL,
TABLE_NAME sysname collate database_default NOT NULL,
COLUMN_NAME sysname collate database_default NULL,
DATA_TYPE smallint NOT NULL,
TYPE_NAME sysname collate database_default NULL,
PRECISION int NULL,
LENGTH int NULL,
SCALE smallint NULL,
RADIX smallint NULL,
NULLABLE smallint NOT NULL,
REMARKS nvarchar(254) collate database_default NULL,
COLUMN_DEF nvarchar(254) collate database_default NULL,
SQL_DATA_TYPE smallint null,
SQL_DATETIME_SUB smallint NULL,
CHAR_OCTET_LENGTH int NULL,
ORDINAL_POSITION smallint,
IS_NULLABLE varchar(254) collate database_default NOT NULL,
SS_DATA_TYPE tinyint null
)
INSERT [qunect_columns] EXEC sp_columns @sqlServerTableName
DECLARE @columnname nvarchar(128)
DECLARE @typename nvarchar(128)
DECLARE @length int
DECLARE @lengthtext nvarchar(12)
DECLARE @precision nvarchar(12)
DECLARE @scale nvarchar(12)
DECLARE @columnlist nvarchar(4000)
DECLARE @comma nvarchar(1)
SET @comma = ''
SET @columnlist = ''
DECLARE column_cursor CURSOR FOR SELECT COLUMN_NAME, TYPE_NAME, LENGTH, PRECISION, SCALE FROM [qunect_columns]
OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @columnname, @typename, @length, @precision, @scale
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
set @lengthtext = @length
IF @typename = 'char' or @typename = 'nchar' or @typename = 'ntext' or @typename = 'text' or @typename = 'nvarchar' or @typename = 'sql_variant' or @typename = 'timestamp' or @typename = 'uniqueidentifier' or @typename = 'xml'
BEGIN
SET @typename = 'varchar'
END
IF @typename = 'smalldatetime'
BEGIN
SET @typename = 'datetime'
END
IF @typename = 'decimal' or @typename = 'money' or @typename = 'smallmoney'
BEGIN
SET @typename = 'numeric'
END
IF @typename = 'varchar' AND @length > 255
BEGIN
SET @typename = 'LONGVARCHAR'
END
IF @typename = 'image' or @typename = 'binary' or @typename = 'varbinary'
BEGIN
SET @typename = 'LONGVARBINARY'
END
IF @typename = 'float' or @typename = 'smallint' or @typename = 'int' or @typename = 'bigint' or @typename = 'tinyint' or @typename = 'real'
BEGIN
SET @typename = 'DOUBLE'
END
SET @columnlist = @columnlist + @comma + '"' + @columnname + '" ' + @typename
IF @typename = 'varchar' AND @length < 256
BEGIN
SET @columnlist = @columnlist + '(' + @lengthtext + ')'
END
IF @typename = 'numeric'
BEGIN
SET @columnlist = @columnlist + '(' + @precision + ',' + @scale + ')'
END
SET @comma = ','
END
FETCH NEXT FROM column_cursor INTO @columnname, @typename, @length, @precision, @scale
END
DEALLOCATE column_cursor
DECLARE @qSQL nvarchar(4000)
SET @qSQL = 'select * from openquery(quickbase, ''createoq table "' + @newTableName + ' ' + @parentDBID + '"('+@columnlist+')'')'
PRINT @qSQL
EXEC(@qSQL)
END