March 15, 2012 at 11:17 pm
Good day SSC,
I have this table and I would like to ask a proper way of importing some data from an Excel file to one of my SQL table. Attached is the Excel file I want to import and I need to indicate a WHERE statement to import only data available both on the SQL table and Excel file.
SQL TABLE:
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[INVENTORY_PART](
[PART_ID] [varchar](8) NOT NULL,
[PRODUCT_CODE] [varchar](10) NULL,
[DESCRIPTIONS] [varchar](50) NULL,
[TYPE] [varchar](20) NULL,
[STANDARD] [varchar](20) NULL,
[MASTER] [varchar](20) NULL,
[DROPTESTED] [varchar](20) NULL,
CONSTRAINT [PK_INVENTORY_PART] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
VALUES OF SQL Table:
INSERT INTO INVENTORY_PART
VALUES ('100155', 'VD-466', 'G-1200 ORANGE', NULL, NULL, NULL, NULL)
INSERT INTO INVENTORY_PART
VALUES ('100178', 'VD-466', 'G-1504 BROWN/GREEN', NULL,NULL, NULL,NULL)
INSERT INTO INVENTORY_PART
VALUES ('100641', 'VD-438','G-1127 MUSTARD', NULL,NULL, NULL,NULL)
INSERT INTO INVENTORY_PART
VALUES ('102029', 'VD-681','G-1509 WHITE',NULL, NULL,NULL, NULL)
INSERT INTO INVENTORY_PART
VALUES ('102080', 'VD-540', 'G-1504 BROWN/GREEN', NULL,NULL, NULL,NULL)
INSERT INTO INVENTORY_PART
VALUES ('102172','VD-565','G-1196 ANTQ WHITE', NULL, NULL, NULL, NULL)
INSERT INTO INVENTORY_PART
VALUES('102173', 'VD-500', 'G-1517 LIGHT GRAY',NULL, NULL,NULL, NULL)
INSERT INTO INVENTORY_PART
VALUES ('110045', 'VD-815','G-1620 ANTQ WHITE', NULL,NULL, NULL,NULL)
INSERT INTO INVENTORY_PART
VALUES ('110833', 'VD-815','G-1620 ANTQ WHITE', NULL,NULL, NULL,NULL)
INSERT INTO INVENTORY_PART
VALUES ('118015', 'PL-675','G-1504 BROWN/GREEN', NULL,NULL, NULL,NULL)
Please give some inputs. Thanks
March 16, 2012 at 2:47 am
March 18, 2012 at 5:38 pm
Probably You could use Import Wizard (Right-click on database in Object Explorer->Tasks->Import Data), and on the page "Specify Table Copy or Query" select option "Write a query to specify the data to transfer".
March 19, 2012 at 8:02 am
Usually for the amount of columns you have 13 I will just write an excel formula; now if you are dealing with a large amount of data in excel I definitely think using the DTS.exe command call from the run prompt (DTS Wizard) would be my second option, with the third option creating a full scale SSIS package (DTS for older SQL version.
The excel query you can use is
="INSERT INTO INVENTORY PART (PART_ID, PRODUCT_CODE, DESCRIPTIONS, TYPE, STANDARD, MASTER, DROPTESTED) VALUES " & "'" & RC[-6] & "'," & "'" & RC[-5] & "'," & "'" & RC[-4]& "'," & "'" & RC[-3] & "'," & "'" & RC[-2] & "'," & "'" & RC[-1] & "')"
You can drop that in column 7 then drag it down to 13 and it will generate your insert statements.
Hope that helps!
April 11, 2012 at 1:11 pm
A more safe way is to save the Excel file as a TAB separeted textfile
(Make a small macro activated when the Excelfile is opened or closed).
Import that textfile with SSIS or bulkinsert to a staging table and make the
where question between that table and the table you have as example.
Excel and SSIS are not always on speeking terms.
//Gosta
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply