Mirroring the Schema of a Single SQL Server Table in Quickbase

Application Note for QuNect ODBC for 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.

@sqlServerTableName: The name of the SQL Server table.
@appDBID: The appid of the application where you want the Quickbase table to be created.
@newTableName: The name you want to give to the new table that will be created in the Quickbase application.
@createDeleteMeField: Set this to 1 if you want records deleted in SQL Server to get deleted in Quickbase. Otherwise leave it set to zero.
@compoundKeyFields: Leave this blank if your SQL Server table has an integer identity field. If it doesn't then list the field or fields that make up the key or compound key. If more than one field is used to uniquely identify each record then separate them with commas.
@newDBID: Leave this blank. The stored procedure that this stored procedure creates will use this parameter to refresh the data in an existing table.

	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