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

[Halliarse]

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

 

[imex]

Hi,

Try something like this:

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]

 

[Halliarse]

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

 

[imex]

Try:

insert into STK_Location (LOC_Code, LOC_Stock_Code)
    select '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]

 

[Halliarse]

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!

 

[imex]

This table is new? If it is think you better check the Identity property of LOC_Primary column:
[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/ms186775.aspx[/url]

Hope this helps.

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

 

[Halliarse]

Hi

It's an existing table with about 40 fields!

 

[imex]

Try:

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]

 

[Halliarse]

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]

Try changing the line below:

set @Last_LOC_Primary = 
        coalesce((select top 1 LOC_Primary from STK_Location order by LOC_Primary desc), 0);

Hope this helps.

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

 

[Halliarse]

Imex, you are a legend, many thanks for your help!