August 8, 2011 at 1:25 am
Hi,
i have to import data from xl sheet into the database, in Xl sheet there is only 1 column of data (approx 2000 rows) to be imported into a single table in a Database say "DA". Lets call this table in DA as "X". X contains a list of 10-20 different fields within it however i need to add the list from XL sheet to field say "10" in table X of database DA.
This field "10" at the moment contains 20 records say from p1 to p20...
So thus i have to import those 2000+ rows in the field "10" and replace + add the existing recordset in Field "10".
Also while doing so, the other fields 1-9 and 11-20 needs to be updated too, though most of the fields are NULL but some 3-4 fields out of the 20 fields contain a value which ofcourse will be repeated for again for about 2000 records set as well (it has only 1 value in it for all the recordset.
How do i perform this whole operation and at the same time update all the other fields with its respective or NULL values ?
I have access to only SQL Studio Express and thats the only tool i have to work with.
regards
abhi
August 8, 2011 at 1:48 am
Hi
Its difficult to understand vague terms such as same table and other table etc. You might want to consider rewording the post so that we get a better idea of what your trying to achieve.
In your case for now I can mention a few different ways of getting this done.
You can use a staging table to preload the data from the excel and then have a proc to perform the updates.
You can also use BIDs to create a SSIS pacakge which will do the same for you.
August 8, 2011 at 5:26 pm
hi,
i have re edited my post for a more clear picture of what i am trying to do.. hope this suffice ?
regards
abhi
August 9, 2011 at 1:52 am
After reading your post I am still not sure where your getting the data to update the columns other than column 10 in the table.
But to summarize here's what i understood.
1) you have one excel sheet with one column populated with 2000 rows
2) you also have one table with approx 20 column of which one column (A) needs to be "merged" (insert + udpdate) with the data from the excel sheet
3) while performing the above step there are 3-4 additional column which will also be inserted or updated based on the data being populated from the excel sheet. (I DONT KNOW WHERE YOUR GETTING THE DATA FOR THESE COLUMNS.)
4) Once the above steps are complete the table should contain 2000 rows with Column A having the same data as the excel sheet and a few additional column being modified as well.
If the above is close to what you want to achieve here's what I suggest:
1)If this is a one time activity insert the data from the excel sheet into a staging table using the import data task in SSMS , else use a Bulk insert command in sql
2) Perform a inner join between the source table and the staging table you have just created on the column A.
-- This should give you a result set of all records where the data needs to be updated.
3) use the above query and perform and except with the data in the staging table
--this should give you all data that needs to be inserted.
4) validate if the data looks the way you want it to
5) Created an update statement using the sql you wrote in step2
6) create a insert statement using the sql you wrote in step 3
i might be able to help more If you can post the table definition and some sample data. with the expected output.
August 9, 2011 at 6:54 pm
Hi,
Ok... there are some changes to the initial query...
1. I have a SQL statement which lists me all those 2000 record set, so i do NOT need Excel sheet anymore.
2. Import these 2000 record set into another field of another table of another Database (all dB residing on the same server).
3. Current structure of the table where the data needs to be imported has 24 values in it as of now.
4. Need a SQL statement which wil import the 2000 records into this new field and subsequently that SQL statement also needs to add more values to the the remaining fields of the same table.
5. Other fields have a fixed value so we can specify those values in the Insert Statement.
6. I need a syntax on how to move the data (retrieved by SQL statement) into another database table.
SQL statement for 2000 rows is as follows (from ecm_demo-dbo..Asset) :-
SELECT APPLICATION.SoftwareTitleName, COUNT(*) AS GROUP_COUNT
FROM Asset AS ASSET LEFT OUTER JOIN
ComplianceComputer AS ASSET_COMPUTER ON ASSET.AssetID = ASSET_COMPUTER.AssetID LEFT OUTER JOIN
InstalledSoftware AS ASSET_APPLICATION ON
ASSET_COMPUTER.ComplianceComputerID = ASSET_APPLICATION.ComplianceComputerID LEFT OUTER JOIN
SoftwareTitle AS APPLICATION ON ASSET_APPLICATION.SoftwareTitleID = APPLICATION.SoftwareTitleID
GROUP BY APPLICATION.SoftwareTitleName
From above i just need the SoftwareTitleName field to be imported into the new Database field which is :-
in table
mdb-dbo.ca_owned_resource and field is - resource_name
there are 15 other fields as well in the table ca_owned_resource which needs to be updated as when i add 2000 records into it.
So please provide me with a proper syntax on doing the above..
hope this all makes sense now..
regards
abhi
August 10, 2011 at 12:20 am
well,
i have so far compiled the following sytax:-
INSERT INTO mdb-dbo.ca_owned_resource (resource_name)
SELECT SoftwareTitleName, (own_resource_uuid CAST(NEWID() AS BINARY(16))
FROM(SELECT APPLICATION.SoftwareTitleName
FROM Asset AS ASSET LEFT OUTER JOIN
ComplianceComputer AS ASSET_COMPUTER ON ASSET.AssetID = ASSET_COMPUTER.AssetID LEFT OUTER JOIN
InstalledSoftware AS ASSET_APPLICATION ON
ASSET_COMPUTER.ComplianceComputerID = ASSET_APPLICATION.ComplianceComputerID LEFT OUTER JOIN
SoftwareTitle AS APPLICATION ON ASSET_APPLICATION.SoftwareTitleID = APPLICATION.SoftwareTitleID
GROUP BY APPLICATION.SoftwareTitleName
)
But i am failing to provide the source Database>table name into the above query.
My select statement is running off from ecm_demo.asset table so where do i mention this in the above select statement ?
you would have noticed the field own_resource_uuid in the above syntax >> since this field is a primary key and holds binary key i thought of auto populating this? my syntax is incorrect over there so can you please post the correct syntax ?
regards
abhi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply