Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating / Inserting to an empty table using a 2nd table as a key 1

Status
Not open for further replies.

Halliarse

IS-IT--Management
Jan 8, 2007
213
GB
Good Afternoon

I am using MSSQL and have a puzzler!

I have a table, STK_Stock, which has 783 records, I have a second table, STK_Location which is empty. I need to be able to create a script that will create a record on STK_Location per STK_Stock record, however, I also need to set the values for every field on the STK_Location table as part of the insert / update! To complicate matters,, some of the data that needs to be included within the insert / update comes from a 3rd table, STK_Stock2!

The tables are linked by the STKCode key on the STK_Stock table, the LOC_Stock_Code on the STK_Location table and the STKCode2 on the STK_Stock2 table.

I hope that is clear if somewhat convoluted and any assistance will be gratefully received!

Kind regards

Steve
 
Hi,

Try something like this:

Code:
insert into STK_Location (LOC_Stock_Code, LOC_Column1, LOC_Column2, LOC_Column3, LOC_Column4)
    select s.STKCode, s.ColumnA, s2.ColumnA, 0, 'ABC'
    from STK_Stock as s
    inner join STK_Stock2 as s2
        on s2.STKCode2 = s.STKCode

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]
 
Imex

I tried that and cannot get it to work!

I've changed my mind about how I want to do this....I want to insert a new record into STK_Location for every record that exists within the STK_Stock table. Both files have different layout obviously! The only fields I want to populate within the STK_Location table are the LOC_Code which will always be 'MAIN' and the LOC_Stock_Code which will be the STKCode from the STK_Stock table

Any further advise please?

Many thanks

Steve
 
That's getting there I think, however the script fails on the column LOC_Primary which cannot contain a nulls, it needs to have a sequential number as the records are inserted!
 
Hi

It's an existing table with about 40 fields!
 
Try:

Code:
declare @Last_LOC_Primary int;
set @Last_LOC_Primary = (select top 1 LOC_Primary from STK_Location order by LOC_Primary desc);

insert into STK_Location (LOC_Primary, LOC_Code, LOC_Stock_Code)
    select 
        ROW_NUMBER() OVER(ORDER BY s.STKCode) + @Last_LOC_Primary, 
        'MAIN', 
        s.STKCode
    from STK_Stock as s
    where not exists (select 1 from STK_Location as l
                      where 
                          l.LOC_Code = 'MAIN' and 
                          l.LOC_Stock_Code = s.STKCode)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]
 
Hi imex, thanks for your continued help on this one!

I'm now getting the following error which doesn't make sense as you are calculating the LOC_Primary so not sure why it is null!

Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'LOC_PRIMARY', table 'ChannelTechnicalServices.dbo.STK_LOCATION'; column does not allow nulls. INSERT fails.
 
Imex, you are a legend, many thanks for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top