September 1, 2010 at 3:42 pm
I sure this is probably the wrong forum and I apologize. However, I need some help.
I've been tasked to take updated data from an excel spreadsheet and put it into a SQL express table. There is one field that is exactly the same in both data sets so I'm sure I can key off that identifier.
However, I don't know how to do it. In my research, I see that I can use "update" but most examples don't really show how to match and replace with my data from Excel.
If I need to convert it to Access, that's no problem. Heck, if the whole thing was done in Access, this would have already been done. That is my limit for data import/export experience in database work.
So, our ID field is the same, starts with 19 and increases by 26 for 262 records (although one record has no change, so 261 records need to change). How can I do this efficiently? We do not have a DBA on staff, this is kind of thrown together for a web page where people put in their share of data for a report.
TIA
September 1, 2010 at 9:35 pm
Probably the easiest way would be for you to use the import wizard (right-click the database | Tasks | Import data). For the data source, select "Excel Source", and follow the wizard to import the data. You can add it to a new or existing table.
You can also import the data with T-SQL, and the OpenRowset / OpenDatasource functions. Just google the function and "Excel", and you will find how to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 3:33 am
Yeah go with what wayne suggested import it into a temp table then join this table with main table and update the data.....
September 2, 2010 at 9:05 am
WayneS (9/1/2010)
Probably the easiest way would be for you to use the import wizard (right-click the database | Tasks | Import data). For the data source, select "Excel Source", and follow the wizard to import the data. You can add it to a new or existing table.You can also import the data with T-SQL, and the OpenRowset / OpenDatasource functions. Just google the function and "Excel", and you will find how to do this.
I don't have an "Import" option under Tasks when I click on the database.
September 2, 2010 at 9:32 am
I was able to import the data by using the DTSWizard. It was on a different drive.
So, how do I link the tables so that the data can be transferred/updated?
I'm sorry that I don't know these simple procedures.
September 2, 2010 at 10:17 am
mfowler12 (9/2/2010)
I was able to import the data by using the DTSWizard. It was on a different drive.So, how do I link the tables so that the data can be transferred/updated?
I'm sorry that I don't know these simple procedures.
I think it would be best if you imported the data into a new table.
Then, in T-SQL, you would:
-- first, update any existing data
UPDATE MyTable
SET Col = x.Col --[, Col2=x.Col2, etc.]
FROM MyTable
JOIN ImportedExcelTable x
ON MyTable.PKCol = x.PKCol -- or whatever you need to match on is.
-- then insert data not in the table
INSERT INTO MyTable (ColumnList) -- all columns to be inserted into
SELECT (ColumnList) -- what columns they are coming from
FROM ImportedExcelTable x
LEFT JOIN MyTable
ON MyTable.PKCol = x.PKCol -- whatever you are matching on
WHERE MyTable.PKCol IS NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 10:34 am
WayneS (9/2/2010)
mfowler12 (9/2/2010)
I was able to import the data by using the DTSWizard. It was on a different drive.So, how do I link the tables so that the data can be transferred/updated?
I'm sorry that I don't know these simple procedures.
I think it would be best if you imported the data into a new table.
Then, in T-SQL, you would:
-- first, update any existing dataUPDATE MyTable
SET Col = x.Col --[, Col2=x.Col2, etc.]
FROM MyTable
JOIN ImportedExcelTable x
ON MyTable.PKCol = x.PKCol -- or whatever you need to match on is.
-- then insert data not in the table
INSERT INTO MyTable (ColumnList) -- all columns to be inserted into
SELECT (ColumnList) -- what columns they are coming from
FROM ImportedExcelTable x
LEFT JOIN MyTable
ON MyTable.PKCol = x.PKCol -- whatever you are matching on
WHERE MyTable.PKCol IS NULL
Let me write it with the column and database names I am using.
UPDATE 2010 Survey
SET Col = Sheet$.ID
FROM 2010 Survey
JOIN ImportedExcelTable Sheet$ -- this is the table name for the imported data from Excel
ON 2010 Survey.ID (matched column name) = sheet$.ID Is this right?
Now, the inserting part is where I'm confused.
I want it to update the rows where the ID number matches. You have listed columns, do I replace that with rows?
September 2, 2010 at 10:49 am
mfowler12 (9/2/2010)
WayneS (9/2/2010)
mfowler12 (9/2/2010)
I was able to import the data by using the DTSWizard. It was on a different drive.So, how do I link the tables so that the data can be transferred/updated?
I'm sorry that I don't know these simple procedures.
I think it would be best if you imported the data into a new table.
Then, in T-SQL, you would:
-- first, update any existing dataUPDATE MyTable
SET Col = x.Col --[, Col2=x.Col2, etc.]
FROM MyTable
JOIN ImportedExcelTable x
ON MyTable.PKCol = x.PKCol -- or whatever you need to match on is.
-- then insert data not in the table
INSERT INTO MyTable (ColumnList) -- all columns to be inserted into
SELECT (ColumnList) -- what columns they are coming from
FROM ImportedExcelTable x
LEFT JOIN MyTable
ON MyTable.PKCol = x.PKCol -- whatever you are matching on
WHERE MyTable.PKCol IS NULL
Let me write it with the column and database names I am using.
UPDATE 2010 SurveySET Col = Sheet$.ID
FROM 2010 Survey
JOIN ImportedExcelTable Sheet$ -- this is the table name for the imported data from Excel
ON 2010 Survey.ID (matched column name) = sheet$.ID Is this right?
Now, the inserting part is where I'm confused.
I want it to update the rows where the ID number matches. You have listed columns, do I replace that with rows?
You've got some things that just don't look right. How about doing this for me? Take a look at the first link in my signature, and then post the CREATE TABLE statements for the "2010 Survey" and "Sheet$" tables. Then indicate which column in the Sheet$ table goes to which column in the "2010 Survey" table. After we get that, we'll proceed with the next step.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 10:56 am
Thank you for your patience Wayne.
Primary Table, meaning this is where we want the imported data to go.
CREATE TABLE [dbo].[2010 Master Survey Results](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BENCHMARK] [float] NULL,
[YOUR_STATES_JOB_TITLE] [nvarchar](255) NULL,
[Number_of_INC] [float] NULL,
[AVG_SALARY] [money] NULL,
[PAY_RANGE_MIN] [money] NULL,
[PAY_RANGE_MIDPT] [money] NULL,
[PAY_RANGE_MAX] [money] NULL,
[PERCENTILE_25TH] [money] NULL,
[MEDIAN] [money] NULL,
[PERCENTILE_75TH] [money] NULL,
[MATCH_LEVEL] [nvarchar](255) NULL,
[CLASS_LEVEL] [nvarchar](255) NULL,
[STATE] [nvarchar](255) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [2010 Master Survey Results$ID] PRIMARY KEY CLUSTERED
(
Imported Excel Data
CREATE TABLE [dbo].[Sheet1$](
[ID] [float] NULL,
[BENCHMARK] [float] NULL,
[YOUR_STATES_JOB_TITLE] [nvarchar](255) NULL,
[Number_of_INC] [float] NULL,
[AVG_SALARY] [float] NULL,
[PAY_RANGE_MIN] [float] NULL,
[PAY_RANGE_MIDPT] [float] NULL,
[PAY_RANGE_MAX] [float] NULL,
[PERCENTILE_25TH] [nvarchar](255) NULL,
[MEDIAN] [nvarchar](255) NULL,
[PERCENTILE_75TH] [nvarchar](255) NULL,
[MATCH_LEVEL] [nvarchar](255) NULL,
[CLASS_LEVEL] [nvarchar](255) NULL,
[STATE] [nvarchar](255) NULL,
[AVG & MIN] [nvarchar](255) NULL,
[AVG & MAX] [nvarchar](255) NULL,
[MEDIAN & MIN] [nvarchar](255) NULL,
[MEDIAN & MAX] [nvarchar](255) NULL,
[MIN & MID] [nvarchar](255) NULL,
[MID & MAX] [nvarchar](255) NULL,
[MIN & MAX] [nvarchar](255) NULL
) ON [PRIMARY]
The [ID] columns are what we want to match.
Does that help?
Matt
September 2, 2010 at 11:30 am
This should handle your insert. Make sure you test it in a transaction first, take appropriate backups, etc.
-- update the existing entries based on the ID column.
UPDATE survey
SET [BENCHMARK] = xl.[BENCHMARK],
[YOUR_STATES_JOB_TITLE] = xl.[YOUR_STATES_JOB_TITLE],
[Number_of_INC] = xl.[Number_of_INC],
[AVG_SALARY] = xl.[AVG_SALARY],
[PAY_RANGE_MIN] = xl.[PAY_RANGE_MIN],
[PAY_RANGE_MIDPT] = xl.[PAY_RANGE_MIDPT],
[PAY_RANGE_MAX] = xl.[PAY_RANGE_MAX],
[PERCENTILE_25TH] = CONVERT(money, xl.[PERCENTILE_25TH]),
[MEDIAN] = CONVERT(money, xl.[MEDIAN]),
[PERCENTILE_75TH] = CONVERT(money, xl.[PERCENTILE_75TH]),
[MATCH_LEVEL] = xl.[MATCH_LEVEL],
[CLASS_LEVEL] = xl.[CLASS_LEVEL],
[STATE] = xl.[STATE],
[SSMA_TimeStamp] = GETDATE()
FROM dbo.[2010 Master Survey Results] survey
JOIN dbo.[Sheet1$] xl
ON survey.ID = xl.ID;
-- INSERT data not already in the table, based on the ID column.
-- if you want to NOT use the ID column in the spreadsheet:
-- 1. remark out both of the SET IDENTITY_INSERT statements.
-- 2. remove the ID column from the insert column list.
-- 3. remove the ID column from the select column list.
SET IDENTITY_INSERT dbo.[2010 Master Survey Results] ON;
INSERT INTO dbo.[2010 Master Survey Results] (
[ID], [BENCHMARK], [YOUR_STATES_JOB_TITLE],[Number_of_INC],
[AVG_SALARY], [PAY_RANGE_MIN], [PAY_RANGE_MIDPT],
[PAY_RANGE_MAX], [PERCENTILE_25TH], [MEDIAN],
[PERCENTILE_75TH], [MATCH_LEVEL], [CLASS_LEVEL],
[STATE], [SSMA_TimeStamp])
SELECT xl.[ID], xl.[BENCHMARK], xl.[YOUR_STATES_JOB_TITLE],
xl.[Number_of_INC], xl.[AVG_SALARY], xl.[PAY_RANGE_MIN],
xl.[PAY_RANGE_MIDPT], xl.[PAY_RANGE_MAX], xl.[PERCENTILE_25TH],
xl.[MEDIAN], xl.[PERCENTILE_75TH], xl.[MATCH_LEVEL],
xl.[CLASS_LEVEL], xl.[STATE], xl.[SSMA_TimeStamp]
FROM dbo.[Sheet1$] xl
LEFT JOIN dbo.[2010 Master Survey Results] survey
ON xl.ID = survey.ID
WHERE survey.ID IS NULL;
SET IDENTITY_INSERT dbo.[2010 Master Survey Results] OFF;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 12:22 pm
There are over 6800 records in the database, will this code cause any problems with the other records?
How can I copy this table and test on it?
September 2, 2010 at 12:44 pm
mfowler12 (9/2/2010)
There are over 6800 records in the database, will this code cause any problems with the other records?
This code will:
1. update any records in the main table that match on the ID column in the spreadsheet, and then
2. add any records in the spreadsheet not in the main table.
It will not change any other records.
(Now, if the spreadsheet has all the records in it, then it will.)
How can I copy this table and test on it?
SELECT *
INTO dbo.TestTable
FROM dbo.[2010 Master Survey Results]
This will NOT copy any indexes, etc. to the new table, just the table structure and data.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:00 pm
Awesome, I have the test table set up. I've updated the code and receive this error. I'm sure it is my fault but I could use some guidance on resolving it.
This is my error,
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ')'.
Revised code, the reason it was revised was because the Time Stamp was throwing back an error. I assumed because it wasn't in the imported excel data table. We don't use that column anyway since we never got around to it.
-- update the existing entries based on the ID column.
UPDATE survey
SET [BENCHMARK] = xl.[BENCHMARK],
[YOUR_STATES_JOB_TITLE] = xl.[YOUR_STATES_JOB_TITLE],
[Number_of_INC] = xl.[Number_of_INC],
[AVG_SALARY] = xl.[AVG_SALARY],
[PAY_RANGE_MIN] = xl.[PAY_RANGE_MIN],
[PAY_RANGE_MIDPT] = xl.[PAY_RANGE_MIDPT],
[PAY_RANGE_MAX] = xl.[PAY_RANGE_MAX],
[PERCENTILE_25TH] = CONVERT(money, xl.[PERCENTILE_25TH]),
[MEDIAN] = CONVERT(money, xl.[MEDIAN]),
[PERCENTILE_75TH] = CONVERT(money, xl.[PERCENTILE_75TH]),
[MATCH_LEVEL] = xl.[MATCH_LEVEL],
[CLASS_LEVEL] = xl.[CLASS_LEVEL],
[STATE] = xl.[STATE],
FROM dbo.TestTable survey
JOIN dbo.[Sheet1$] xl
ON survey.ID = xl.ID;
-- INSERT data not already in the table, based on the ID column.
-- if you want to NOT use the ID column in the spreadsheet:
-- 1. remark out both of the SET IDENTITY_INSERT statements.
-- 2. remove the ID column from the insert column list.
-- 3. remove the ID column from the select column list.
SET IDENTITY_INSERT dbo.TestTable ON;
INSERT INTO dbo.TestTable (
[ID], [BENCHMARK], [YOUR_STATES_JOB_TITLE],[Number_of_INC],
[AVG_SALARY], [PAY_RANGE_MIN], [PAY_RANGE_MIDPT],
[PAY_RANGE_MAX], [PERCENTILE_25TH], [MEDIAN],
[PERCENTILE_75TH], [MATCH_LEVEL], [CLASS_LEVEL],
[STATE],)
SELECT xl.[ID], xl.[BENCHMARK], xl.[YOUR_STATES_JOB_TITLE],
xl.[Number_of_INC], xl.[AVG_SALARY], xl.[PAY_RANGE_MIN],
xl.[PAY_RANGE_MIDPT], xl.[PAY_RANGE_MAX], xl.[PERCENTILE_25TH],
xl.[MEDIAN], xl.[PERCENTILE_75TH], xl.[MATCH_LEVEL],
xl.[CLASS_LEVEL], xl.[STATE]
FROM dbo.[Sheet1$] xl
LEFT JOIN dbo.TestTable survey
ON xl.ID = survey.ID
WHERE survey.ID IS NULL;
SET IDENTITY_INSERT dbo.TestTable OFF;
September 2, 2010 at 1:16 pm
Looks like you just have to verify everything is fine, then you're ready to go!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:50 pm
WayneS (9/2/2010)
Looks like you just have to verify everything is fine, then you're ready to go!
Except that I receiving some errors.
On Line 16, which is FROM dbo.TestTable it says, "Incorrect syntax near the keyward 'FROM'. Msg 156."
On Line 32, which is [STATE],) it says, "Incorrect syntax near ')'. Msg 102."
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply