Joining 1 table with some external data 1

[globalstrata]

Hello,

I am looking to insert some values in a table that comes from several places:

1. Another table
2. Increasing variable (i++)
3. Manual Entries

Can I do this? For example

INSERT INTO [database].[dbo].[table]
           ([Field1],[field2],[field3],[field4], [field5]
     VALUES
           (Select [Table2.Field1],1,0,i++,[Table2.Field2])

Can I do this via store procedure or do I have to use DTS?

Thanks in advanced for your help,


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources

 

[rac2]

Well, external data is external data. Somehow you must put it into the database. So a first step might be to import the external data via DTS.

There are degrees of external data. Data may be in another database on the same MS SQL Server. In this case, the answer is as easy as fully qualified names such as you show,
[database].[dbo].

. This of course does not require DTS.

Data may be in a database on a different MS SQL Server. Here you may define a Linked Server, which can be referred to by name. There are limitations or at least inconveniences in retrieving data from a linked server, the import DTS Wizard is much easier to use, I feel. It may also depend on whether this must be a dynamic, always available import or a one-time-only.

Data may be in a different vendor database. Linking may be possible, but Im guessing DTS will be easier.

Data may be in a format which is not a relational database. DTS is probably the only solution here. Although MS may well have connectors to their Office applications which can serve the purpose. I dont know.

Data may come from an external application such as a web form. Use ODBC to store that data in the target database.

Incrementing (i++) is of two kinds. Arbitrary row identifiers which have no meaning other than to be used as keys in joining tables. These can be created automatically by defining an IDENTITY column. There can be only one of these per table. Do not list them in the INSERT. Otherwise, the incremental value is meaningful. This is a form of external data; it must be generated in the external source and stored in the database.

 

[globalstrata]

ok. This is what I am trying to do. I am trying to import some products from table1 to table2. However, the table1 already have some of the products that are also in the table2 so I want to eliminate duplicates. In addition, table1 has a field in that requires an incremental number (Product.id). Moreover, table1.field2 requires that I eliminate all blank spaces that are in table2.field2 before importing it into the table1.field2.

Phew ... Hopefully I did not loose you

So I was thinking:
1. Creating a view that contains the products that are duplicated. (No Sweat!)

2. Creating a store procedure that returned all non duplicated records and insert the records with the required format. - This is where I need help.

I keep trying to use Not IN or NOT Exists with my view or select and it keeps giving me errors:

SELECT *  FROM TABLE1 TABLE1.ID NOT IN View

or

SELECT *  FROM TABLE1 TABLE1.ID NOT IN select ID FROM table2 INNER JOIN TABLE 1 WHERE table2.ID = table1.ID

If I run the 2nd select, I get the right answer. However, now I want to get any records which ID is not in the subset and I have not had that to work. I guess after I am able to get this working, I will move on in trying to get the blank spaces removed as well as the increment ID.

BTW, does SQL 2005 has a setting that allows you to put a key field as an increment like Access does? If it does, then I would not have to worry about this incremented field.

Thanks for your help. Now I wish I would had paid more attention in my databases class.


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources

 

[rac2]

BTW, does SQL 2005 has a setting that allows you to put a key field as an increment like Access does? If it does, then I would not have to worry about this incremented field.

That is an IDENTITY column in MS SQL Server.

CREATE TABLE MyProducts (product_id INT IDENTITY)

See Books Online topic CREATE TABLE for more information.

In addition, table1 has a field in that requires an incremental number (Product.id).

Yes, you lost me on this. Possibly, you meant that table2 requires the Product.id . I say this because I think table2 is the target table which will have all of the correct data when we are finished; therefore, we dont care what table1 requires.

Furthermore, if the Product.id has already been established then the import must take the existing value from table1 and store it in table2. Therefore there is no need to generate an incrementing number.


Possibly you mistyped these.

SELECT *  FROM TABLE1 [COLOR=red]WHERE[/color] TABLE1.ID NOT IN [COLOR=red]([/color] SELECT ID FROM MyViewOfDuplicates [COLOR=red])[/color]

SELECT *  
FROM TABLE1 
WHERE TABLE1.ID 
    NOT IN ( select [COLOR=green]table2.[/color]ID 
             FROM table2 
             INNER JOIN table1 ON table2.ID = table1.ID )

That seems like it would find the rows in table1 which are not in table2.


Assuming we have a SELECT statement that yields exactly the rows we wish to INSERT into table2, the other issue is the the blank values. This can be handled with expressions that calculate the desired value.

If the blank values are NULL values then the ISNULL(column_name, substitute_value) function can be used.

SELECT ISNULL(colA, 'None'),
       ISNULL(colB, 0),
       ISNULL(colC, colA + colD)
FROM MyTable

Here I am substituting the value None in a VARCHAR column and the value 0 in a number column.

If the desired values should be calculated from the values in other columns then the substitute_value would be an expression.

If the blank values are empty strings or actual space characters, then a CASE expression can be used.

SELECT CASE
         WHEN colA = '' THEN 'None'
         WHEN colA = ' ' THEN 'None'
         ELSE colA
       END
FROM MyTable

 

[globalstrata]

Wow! Thank you for your the time that you took to answer. The code that I tried looked like:

SELECT *  
FROM TABLE1 
WHERE TABLE1.ID 
    NOT IN (select table2.ID 
             FROM table2 
             INNER JOIN table1 ON table1.ID = table2.ID)

But it does not work. It gives me no records at all.

While testing to see if the code is right, I used only the first two lines of the code above and it gives me over 4300 records. If I use only the second select it gives me around 300 records. If I use them together, I get nothing although I should be getting around 4000 records. Any other thoughts how to do this?

Regarding the blank spaces, sorry I did not explain myself better but what I meant tp day was that the content of the field looks like "My Product Title" and the contents that I need to enter in the final field is "MyProductTitle"

Thanks again,


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources

 

[rac2]

What happens with this query?

SELECT table1.ID, table2.ID  
FROM TABLE1
LEFT JOIN table2 ON table1.ID = table2.ID

Does it give you the 4300? And are the values for table2.ID mostly NULLs?


If so then the following query is equivalent to the one with the subquery using NOT IN.

SELECT table1.ID, table2.ID  
FROM TABLE1
LEFT JOIN table2 ON table1.ID = table2.ID
WHERE table2.ID IS NULL

Blanks can be eliminated from a string with this function, REPLACE(original_value, ' ', '') .

 

[globalstrata]

It worked!! You are Awesome!! I would give you 10 stars!!

Thanks


Gladys Rodriguez
GlobalStrata Solutions
Computer Repair, Website Design and Computer Consultant
Small Business Resources