How to Insert(Append/Write) data to SQL Server database in table from JMP dataTable through script

RamManickam

Hello,

I scripted data preparation / data cleaning with various formula / formats and output data is ready for Insert(Write) to SQL server from JMP table. I connected to SQL, but not able to insert(append) data to SQL server database on target table. Can any one help on this situation to overcome on SQL statement to append. Here is my end script which is ready (JMP Table = "Upload") to write. 

 

Data Table( "ServerData" ) << Join(
	With( Data Table( "DataProcessed" ) ),
	Merge Same Name Columns,
	Copy second table formula( 0 ),
	Suppress main table formula evaluation( 0 ),
	By Matching Columns( :Individual_Claim_Number_ID_Full = :Individual_Claim_Number_ID_Full ),
	Drop multiples( 0, 0 ),
	Name( "Include non-matches" )(0, 1),
	Preserve main table order( 1 ),
	Output Table( "Upload" )
);

dbc = Create Database Connection(
	"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101"
);

sqlreturn = Execute SQL(
	dbc,
	"INSERT Upload INTO [dbo].ALL_CUSTOMERS"
);
Close Database Connection( dbc );

vince_faller

SQL has no idea what upload is, 

 

You might be able to do 

 

dt = Data Table( "ServerData" ) << Join(
	With( Data Table( "DataProcessed" ) ),
	Merge Same Name Columns,
	Copy second table formula( 0 ),
	Suppress main table formula evaluation( 0 ),
	By Matching Columns( :Individual_Claim_Number_ID_Full = :Individual_Claim_Number_ID_Full ),
	Drop multiples( 0, 0 ),
	Name( "Include non-matches" )(0, 1),
	Preserve main table order( 1 ),
	Output Table( "Upload" )
);

dt << Save DataBase("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MAWA_REP;Data Source=USFLI-WAS101", "dbo.all_customers");

 

 

But I'm pretty sure that would actually overwrite your sql table.  

 

You could write the sql dymanically.  Just for through your table to put in all the values into a SQL string that you can then run. 

 

INSERT INTO [dbo].[ALL_CUSTOMERS]
           (col1, col2)
     VALUES
           (col1_value1, col2_value1), 
           (col1_value2, col2_value2),