Tag: sql-server

Sumproduct in Excel VBA using function arguments as source

I’m trying to write a VBA function that takes the function arguments as the source and then runs SQL commands on it. The SQL command I’m currently trying to implement as a first step is the equivalent of Excel’s sumproduct() function, which in SQL would be something along the lines of “SELECT SUM(A * B)”. […]

Update SQL Server table from Excel data

Currently I am trying to import data from an Excel 2007 workbook into a SQL Server 2008 R2 table ‘test’. The import works perfectly, however, the data on the workbook is changed constantly and needs to be imported every ten mins or so. When I re-run the import, SQL Server just copies all the data […]

Pulling Data From .MDF or .LDF into Excel without a server

I’ve been at this for over a week straight now. I am trying to set up an excel file to pull data from a live .MDF and/or .LDF file on a server. Because of the sensitivity of the files, and updating live, I want to work with just dummy back ups of the files. But […]

Incompatible data types when importing Excel spreadsheet into SQL Server table

I want to import a spreadsheet from Excel into an existing SQL Server 2012 table. I use SQL Server Import and Export Wizard. Here is the create table statement: CREATE TABLE transactions ( payer_id int, order_number int, sum_units int, sum_value float(8), id_3rd_level int, order_date date, isCorporate int, payment char(20), tk int PRIMARY KEY(id_3rd_level) ); After […]

VB.NET load Excel document into SQL Server database table via code

I want to load an Excel document into my VB.NET project and then load all the data from the Excel document into my SQL Server database. The table name in SQL Server TCustomers and it has the following columns: CustomerID, FirstName, LastName, OrderID This is how my data looks in the datagridview: CustomerID FirstName LastName […]

VB2010 export SQL dataset to Excel

As a very newbie to any form of coding following a long break, I have started a little project at work for which i’d appreciate a bit of help. I bit of background – When items are submitted to our office they’re input into a bespoke software which stores them in a MS SQL 2012 […]

Query database with dot in its name

We have a database that has a “.” in it name. We need to be able to query this database from excel and excel is not having it. I’ve tried to put [] around the name, but excel just ignores the brackets. Attached is a picture of the query I’m trying to run. I’ve tried […]

MAJ a sql server DB from excel

I have a Sql Server database and I need to update it every day by adding temporary tables (from excel) and compare them with existing tables, then add the lines that do not exist. Can you help me ?

PivotTable: Dealing with large CommandText due to temporary table

I have the following CommandText for a pivot table’s connection: SET NOCOUNT ON CREATE TABLE #temptable (KonPos integer, KonStk integer); INSERT INTO #temptable (KonPos, KonStk) Values (1,1),(2,1),(3,1); SELECT a.*, b.* FROM #temptable a LEFT JOIN dbTable b ON a.KonPos = b.Position DROP TABLE #temptable; I am using VBA to create the inserted rows and the […]

How to deal with null cell during a SqlBulkCopy from Excel

I have a method that loads data from Excel and uses a SqlBulkCopy to insert it into a SQL Server table. The problem is that one of the column in Excel can be empty string, and that column is mapped to a MONEY column in SQL Server. I would like to default empty string to […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.