Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: insert SQL Server 2000
I am indebted to Stan Littlefield for the examples he posts on the WinBatch support/BBS site - Stan, you rock!
This example:
This demonstration app illustrates inserting into SQL Server 2000 using two methods:
Insert statement
Stored procedure execution
To run this example:
* Log in as a user that can add logins, create tables in the pubs database, and manage db users and roles. This would either be the 'sa' account or some other account set up with the proper system and db roles.
* Select the pubs database that comes with SQL Server 2000.
* Execute the Create_Employee_APP_table.sql file provided.
* This sql script will set up everything needed in SQL Server to support this demonstration.
src = "m-beadles" ;This is the name of the SQL Server we point to - can also use IP address.
Notes:
The demonstration assumes that you have installed SQL Server 2000 and the pubs sample database that comes with SQL Server.
As a dba, I should mention that the stored procedure method is the preferred approach for SQL Server. When executing stored procedures, there is less network traffic and SQL Server already has most of the database <---> client overhead taken care of.
The Winbatch code:
; Notes on SQL Accounts: ; If you have created a SQL login and have granted access to the db, ensure that permissions have been set up for the user. ; If you are using Windows authentication into SQL Server, ensure that the account has been given proper access to the db. BoxOpen("Test Script","Inserting into SQL Server") BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%1) Populate some variables to make a SQL DB Connection" ) TimeDelay(4) BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%2) Set Up the OLEDB Connection to SQL Server%@crlf%%@TAB% using the variables") TimeDelay(4) BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%3) Perform an INSERT statement to a pre-existing table%@crlf%%@TAB% in the database") TimeDelay(4) BoxText("This sample program will do the following:%@crlf%%@crlf%%@TAB%4) Execute a pre-existing stored procedure%@crlf%%@TAB% (the procedure also does an insert)") TimeDelay(4) BoxTitle("Starting ...") BoxText("") TimeDelay(1) src = "m-beadles" ;This is the name of the SQL Server we point to - can also use IP address. uid = "pubsuser" ;If using NT authentication, you do not need uid - can delete this line pwd = "pubsuser" ;If using NT authentication, you do not need pwd - can delete this line catalog = "pubs" ;This sets the database we need to point to. table = "employee_app" ;Could be hard coded in the cSQL, below, rather than using a variable. BoxTitle("Using these connection variables:") BoxText("Server Name = %src% %crlf% User = %uid% %crlf% Password = %pwd% %crlf% Database = %catalog%") TimeDelay(3) cConn = 'DRIVER={SQL Server};SERVER=%src%;UID=%uid%;PWD=%pwd%;Catalog=%catalog%' DB = ObjectOpen("ADODB.Connection") DB.Open(cConn) BoxTitle("Using this OLE connection string:") BoxText("%cConn%") TimeDelay(3) cSQL = "INSERT [%catalog%].[dbo].[%table%] VALUES('mbeadles', 'Insert Method', getdate());" BoxTitle("SQL Insert Statment:") BoxText("%cSQL%") TimeDelay(3) DB.Execute("%cSQL%") ;Insert cSQL = "usp_ins_employee_app @emp_nt_id = 'mbeadles', @app_name = 'Stored Procedure Method', @app_launchtime = ''" BoxTitle("SQL Stored Procedure (with parameters):") BoxText("%cSQL%") TimeDelay(3) DB.Execute(cSQL) ;SP DB.Close() ObjectClose(DB) BoxShut() tget = strcat( Dirget(),"db.Txt") DB = ObjectOpen("ADODB.Connection") ;We are openning an ADO db connection Object RS = ObjectOpen("ADODB.Recordset") ;We are opening a record set object to hold the results of a select query. cSQL = "select * from employee_app" message(cSQL,"") DB.Open(cConn) RS = DB.Execute(cSQL) ;create ASCII text file fle = FileOpen(tget,"WRITE") ;open the text file for writing cRc = "" sort = "" f = "" ;make Column Headings First line in text file FLDS = RS.Fields ;object recordset fields nCount = FLDS.Count() ;the count method - how many columns For i=0 to ( nCount-1 ) nField = RS.Fields(i) cName= nField.Name cRc = strcat(cRc,',"',cName,'"') sort = strcat(sort,'Other pieces:',cName," ",@CRLF) f = strcat(f,'',@CRLF) Next cRc = StrSub(cRc,2,-1) FileWrite(fle,cRc) Boxtext("Creating ASCII Input File") ; /////////////////////////////////////////////////////// ; I originally used the getstring() method here as it is ; 10-20 times faster than looping through the records, however ; the HTML template wants all data to be delimited with "" which ; getstring() does not do by default, so the resulting HTML file ; screws up when sorting by Dialcode ; /////////////////////////////////////////////////////// RS.MoveFirst() While ! RS.eof() cRc="" For i=0 to ( nCount-1 ) nField = RS.Fields(i) If i == ( nCount-1 ) cRc = strcat(cRc,'"',nField.Value,'"') Else cRc = strcat(cRc,'"',nField.Value,'",') Endif Next FileWrite(fle,cRc) RS.MoveNext() EndWhile FileClose(fle) Drop(cRest) RS.Close() ObjectClose(RS) DB.Close() ObjectClose(DB) Boxtext("Formatting HTML Template File") fs = FileSize( "htm.tem" ) binbuf = binaryalloc( fs+1000 ) ;file size of htm.tem + 1000 bytes ret = BinaryRead( binbuf, "htm.tem" ) ;read the htm.tem file into the binbuf buffer and then act on the buffer as follows: num = BinaryReplace( binbuf, "//file//", tget ,0) ;replace this placeholder with the contents of tget (the text file path) num = BinaryReplace( binbuf, "//sort//", sort ,0) ;replace this placeholder with javascript function call - see above in the sort loop. num = BinaryReplace( binbuf, "//f//", f ,0) ;replace this placeholder BinaryWrite( binbuf, "db.htm" ) ;write the completed template into the html file BinaryFree(binbuf) ;free up the buffer BoxShut() ;shut down the message box ShellExecute("db.htm","","",@NORMAL,"") Exit
sqltest02.zip
Article ID: W15228
File Created: 2017:08:29:11:58:40
Last Updated: 2017:08:29:11:58:40