Application Note for QuNect ODBC for QuickBase
Mirroring the Schema of a Single SQL Server Table in Quickbase
The following stored procedure will create a matching table in Quickbase with all of the fields from a SQL Server table. It will also populate the table with all the records from SQL Server, and it will create a stored procedure that you can run periodically to keep the Quickbase table in synchronization with your SQL Server data.
It takes five parameters.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Claude von Roesgen -- Create date: 5/13/2010 -- Last Modified; 8/12/2022 -- Description: Mirror table from SQL Server to Quickbase -- ============================================= -- This stored procedure depends on the existence of a linked server called -- Quickbase. To create this linked server run the following SQL: -- -- EXEC sp_addlinkedserver -- @server = 'QuickBase', -- @provider = 'MSDASQL', -- @srvproduct = 'QuNect', -- @provstr = 'DRIVER={QuNect ODBC for QuickBase};UID=YourQuickBaseEmailOrScreenName;PWD=YourPassword' -- GO -- After you have your linked server in place you're ready to run the stored -- procedure below. IF OBJECT_ID('mirrorSQLServerTableInQuickBase') IS NULL EXEC('CREATE PROCEDURE mirrorSQLServerTableInQuickBase AS SET NOCOUNT ON;') GO /****** Object: StoredProcedure [dbo].[mirrorSQLServerTableInQuickBase] Script Date: 8/12/2022 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[mirrorSQLServerTableInQuickBase] @sqlServerTableName Varchar(255), @appDBID Varchar(255), @newTableName Varchar(255), @createDeleteMeField bit = 0, @compoundKeyFields VarChar(1000) = '', @newDBID Varchar(18) = '' AS BEGIN DECLARE @tableExists BIT = 0 IF (@newDBID <> '') BEGIN SET @tableExists = 1 END 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 @constraint nvarchar(128) DECLARE @data_type int DECLARE @typename nvarchar(128) DECLARE @sqltypename nvarchar(128) DECLARE @length int DECLARE @lengthtext nvarchar(12) DECLARE @precision nvarchar(12) DECLARE @scale nvarchar(12) DECLARE @columnlist varchar(8000) = '' DECLARE @insertlist varchar(8000) = '' DECLARE @SQLinsertlist varchar(8000) = '' DECLARE @QDBinsertlist varchar(8000) = '' DECLARE @comma nvarchar(1) = ',' DECLARE @commaInsert nvarchar(1) = '' DECLARE @fieldCounter int = 0 DECLARE @qSQL varchar(8000) DECLARE column_cursor CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE, TYPE_NAME, LENGTH, PRECISION, SCALE FROM [qunect_columns] OPEN column_cursor FETCH NEXT FROM column_cursor INTO @columnname, @data_type, @sqltypename, @length, @precision, @scale WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SET @typename = @sqltypename SET @constraint = '' SET @lengthtext = @precision IF @precision > 1000000 BEGIN SET @lengthtext = '1000000' END IF @sqltypename = 'datetime2' or @sqltypename = 'smalldatetime' or @sqltypename = 'datetimeoffset' BEGIN SET @typename = 'datetime' END IF @sqltypename = 'text' or @sqltypename LIKE '%chr%' or @sqltypename LIKE '%char%' or @sqltypename = 'ntext' or @sqltypename = 'hierarchyid' or @sqltypename = 'uniqueidentifier' or @sqltypename = 'xml' BEGIN SET @typename = 'varchar' END IF @sqltypename = 'int' or @sqltypename = 'decimal' or @sqltypename = 'smallint' or @sqltypename = 'float' or @sqltypename = 'real' or @sqltypename LIKE '%money%' or @sqltypename = 'bigint' or @sqltypename = 'tinyint' or @sqltypename LIKE '%quan%' BEGIN SET @typename = 'DOUBLE' END IF @sqltypename = 'binary' or @sqltypename = 'image' or @sqltypename = 'varbinary' BEGIN SET @typename = 'LONGVARBINARY' END IF @sqltypename = 'int identity' BEGIN SET @typename = 'DOUBLE' SET @constraint = ', CONSTRAINT constraintname PRIMARY KEY ("' + @columnname + '") ' END SET @columnlist = '"' + @columnname + '" ' + @typename IF @typename = 'varchar' BEGIN SET @columnlist = @columnlist + '(' + @lengthtext + ')' END IF @typename = 'numeric' BEGIN SET @columnlist = @columnlist + '(' + @precision + ',' + @scale + ')' END SET @columnlist = @columnlist + @constraint SET @insertlist = @insertlist + @commaInsert + '"' + @columnname + '"' SET @commaInsert = ',' IF @tableExists = 0 BEGIN IF @fieldCounter = 0 BEGIN SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''CREATEOQ TABLE "' + @newTableName + ' ' + @appDBID + '"('+ REPLACE(@columnlist, '''', '''''') + ')'')' PRINT @qSQL EXEC(@qSQL) SELECT @newDBID = dbid from OpenQuery(QUICKBASE, 'SELECT @@newdbid') SET @fieldCounter = 1 END ELSE BEGIN SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''ALTEROQ TABLE "' + ' ' + @newDBID + '" ADD '+ REPLACE(@columnlist, '''', '''''') + ''')' PRINT @qSQL EXEC(@qSQL) END END END FETCH NEXT FROM column_cursor INTO @columnname, @data_type, @sqltypename, @length, @precision, @scale END DEALLOCATE column_cursor DECLARE @deletemefield nvarchar(40) = '' IF @createDeleteMeField = 1 BEGIN SET @deletemefield = ', delete_me' END IF @createDeleteMeField = 1 AND @tableExists = 0 BEGIN BEGIN SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''ALTEROQ TABLE "' + @newDBID + '" ADD delete_me varchar (40)'')' PRINT @qSQL EXEC(@qSQL) END END SET @SQLinsertlist = @insertlist SET @QDBinsertlist = @insertlist SET @compoundKeyFields = REPLACE(@compoundKeyFields, ', ', ',') SET @compoundKeyFields = REPLACE(@compoundKeyFields, ' ,', ',') DECLARE @compoundKeyFieldName nvarchar(4000) = REPLACE(@compoundKeyFields, ',', '_') IF @compoundKeyFields <> '' AND @constraint = '' AND CHARINDEX(',', @compoundKeyFields) > 0 AND @tableExists = 0 BEGIN SET @compoundKeyFieldName = REPLACE(@compoundKeyFieldName, ' ', '') SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''ALTEROQ TABLE ' + @newDBID + ' ADD "' + @compoundKeyFieldName + '" varchar (1000)'')' PRINT @qSQL EXEC(@qSQL) SET @SQLinsertlist = @SQLinsertlist + @comma + 'CONCAT("' + REPLACE(@compoundKeyFields, ',', '",''-'',"') + '")' SET @QDBinsertlist = @QDBinsertlist + @comma + '"' + @compoundKeyFieldName + '"' END IF @compoundKeyFields <> '' AND @constraint = '' AND @tableExists = 0 BEGIN TRY SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''ALTEROQ TABLE ' + @newDBID + ' ADD CONSTRAINT DoesNotMatter PRIMARY KEY (' + @compoundKeyFieldName + ')'')' PRINT @qSQL EXEC(@qSQL) END TRY BEGIN CATCH PRINT 'Could not set the primary key to ' + @compoundKeyFields + ' because ' + ERROR_MESSAGE() PRINT 'You must have QuNect ODBC for QuickBase 6.21.9.76 or greater' END CATCH DECLARE @insertSQLprefix varchar(8000) SET @insertSQLprefix = 'INSERT INTO [QUICKBASE].[APPNAME]..[' + @newDBID + '] (' + REPLACE(@QDBinsertlist, '''', '''''') + @deletemefield + ') SELECT ' + @SQLinsertlist DECLARE @deleteSQL varchar(8000) = '' IF @createDeleteMeField = 1 BEGIN DECLARE @deleteGUID uniqueidentifier SET @deleteGUID = NEWID() SET @deleteSQL = ',''' + CONVERT(nvarchar(36), @deleteGUID) + '''' END DECLARE @deleteSQLsuffix varchar(300) = ' FROM [' + @sqlServerTableName + ']' SET @qSQL = @insertSQLprefix + @deleteSQL + @deleteSQLsuffix PRINT @qSQL EXEC(@qSQL) IF @createDeleteMeField = 1 BEGIN SET @qSQL = 'SELECT * FROM OPENQUERY(QUICKBASE, ''DELETEOQ FROM ' + @newDBID + ' WHERE delete_me <> ''''' + CONVERT(nvarchar(36), @deleteGUID) + ''''''')' PRINT @qSQL EXEC(@qSQL) END DECLARE @spSQL varchar(8000) = ' CREATE PROCEDURE [Sync ' + @sqlServerTableName + ' in QuickBase] AS BEGIN DECLARE @return_value int EXEC @return_value = [dbo].[mirrorSQLServerTableInQuickBase] @sqlServerTableName = N''' + @sqlServerTableName + ''', @appDBID = N''' + @appDBID + ''', @newTableName = N''' + @newTableName + ''', @createDeleteMeField = ' + CONVERT(nvarchar(1), @createDeleteMeField) + ', @compoundKeyFields = N''' + @compoundKeyFields + ''', @newDBID = ''' + @newDBID + ''' SELECT ''Return Value'' = @return_value END ' BEGIN TRY EXEC('DROP PROCEDURE [Sync ' + @sqlServerTableName + ' in QuickBase]') END TRY BEGIN CATCH PRINT 'CREATING PROCEDURE [Sync ' + @sqlServerTableName + ' in QuickBase]' END CATCH PRINT @spSQL EXEC(@spSQL) END