What function (if any) can I use to select information from one table from one database into another database?

Question

Ok... Here's what I got.

Table A - Holds product information, most relevant is the product description, we'll call this A_DESC - A_DESC contains several words per row, and about 2500 rows of information.

Table B - Holds nothing as of yet, but is to hold an individual word per row that is relevant to table A. Table B consists of B_ID (unique primary), B_WORD (the unique word from A_DESC) and B_SEED (the number of times that word can be found in TableA).

I was thinking of writing a vb script to select the information from table A into a recordset (or dataset as its called nowadays), then loop through each row, split the value by space to get each word individually, and then insert that word into table B only IF that word does not exist yet, and if it does exist, increase the B_SEED count by 1.

I then thought maybe this is possible directly from SQL Server via Management studio? Is there a function I could write to accomplish this without having vb as the middle man? This script will only need to run maybe 2-3 times a year to update table B with any new words in Table A. I feel as though writing this in VB would use more resources than I need to...

Hopefully I've established what I want to accomplish.

I'll try and explain it visually...

Table A _
A_DESC = "Type 4 felt"
A_DESC = "Type 6 felt"

-->

Table B _
B_ID = 1  |  B_WORD = 'Type'  |  B_SEED = 2
B_ID = 2  |  B_WORD = '4'     |  B_SEED = 1
B_ID = 3  |  B_WORD = 'felt'  |  B_SEED = 2
B_ID = 4  |  B_WORD = '6'     |  B_SEED = 1

Oh yeah... edit

I forgot to add, table A is in a different database than table B (on the same server though)

Second Edit

Tried @jhinkley's answer - and recieved the following errors:

Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER FUNCTION' does not allow specifying the database name as a prefix to the object name.
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@s".
Msg 1087, Level 15, State 2, Line 12
Must declare the table variable "@t".

with the following code:

CREATE FUNCTION ROOF.[dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE
    (
        val VARCHAR(MAX)
    )   
AS
    BEGIN
        DECLARE @xml XML
        SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

        INSERT INTO @t(val)
        SELECT r.value('.','VARCHAR(5)') as Item
        FROM @xml.nodes('//root/r') AS RECORDS(r)

        RETURN
    END

truncate table website.dbo.TEXT_WORDS

insert into website.dbo.TEXT_WORDS(T_WORD,T_SEED)
select val,count(val)
from ROOF.dbo.IV00101 
        cross apply ROOF.dbo.Split(' ', replace(ROOF.dbo.IV00101.ITEMDESC,'&','&amp;'))
group by val

did I miss something?

You can do this easily with a merge statement... what version of SQL are you using? — Eric J. Price, Mar 28 '13 at 17:28
Is A_DESC a single column? If so, the most difficult part will be separating the words. There are a few posts on here about splitting an SQL string by a delimiter, then you should be able to use the output from that in an insert statement relatively easily — Jaloopa, Mar 28 '13 at 17:35
SQL '08 A_DESC is a single column in a table with about 30 columns — Kizzelwhix, Mar 28 '13 at 20:52

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

here is a short script based off this function for splitting records T-SQL: Opposite to string concatenation - how to split string into multiple records

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE
    (
        val VARCHAR(MAX)
    )   
AS
    BEGIN
        DECLARE @xml XML
        SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

        INSERT INTO @t(val)
        SELECT r.value('.','VARCHAR(5)') as Item
        FROM @xml.nodes('//root/r') AS RECORDS(r)

        RETURN
    END

truncate table tableB

insert into tableB (B_WORD,B_SEED)
select val,count(val)
from tableA 
        cross apply dbo.Split(' ', replace(A_DESC,'&','&amp;'))
group by val

requires sql 2005 or higher. I'm assuming that B_ID column is not referenced as part of a foreign key, hence the truncate table command. It's much easier to drop all records and insert them again rather than do a update/merge.

edited May 23 '17 at 12:21

Community

1
1

answered Mar 28 '13 at 20:41

jhinkley

668
4
10

Thank you @jhinkley - Will try this tonight when I get home and tag appropriately if/when it works. – Kizzelwhix Mar 28 '13 at 20:54
I forgot to mention... tables are in seperate databases. Which database would I put this function in? the sending or recieving? – Kizzelwhix Mar 29 '13 at 00:21
@Kizzelwhix Either one. You'll have to give full path to tables and the function, ie. databaseA.dbo.tableA, databaseB.dbo.tableB, and databaseA.dbo.Split – jhinkley Mar 29 '13 at 13:21
I tried your answer, and updated my question with the code & errors that I recieved when I tried it. Any help is appreciated, thank you. – Kizzelwhix Mar 30 '13 at 20:12
You cannot do a cross-database CREATE FUNCTION, IIRC. You will need to script this with a `USE [ROOF]` before the CREATE FUNCTION and then another `USE` back to your home database. (and `GO`'s in-between, where appropiate.) – RBarryYoung Mar 30 '13 at 20:22

What function (if any) can I use to select information from one table from one database into another database?

1 Answers1