Insert with cross join

[former_SQLA_member1694869]

I have a table (tblCustomer) with three fields (customer_id, s_id, s_string) I want to fill in this table with two fixed values and customer_id from another table.

Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.

Customer_id taken from tblMainCustomer and s_id, s_string these fixed values that are equal for each customer.

Fixed values:

DECLARE @s_id_1 int
SET @s_id_1 = 100
DECLARE @s_string_1 nvarchar(35)
SET @s_string_1 = 'Gold'

DECLARE @s_id_2 int
SET @s_id_2 = 101
DECLARE @s_string_2 nvarchar(35)
SET @s_string_2 = 'Steel'

DECLARE @s_id_3 int
SET @s_id_3 = 102
DECLARE @s_string_3 nvarchar(35)
SET @s_string_3 = 'Super Copper'


CREATE TABLE tblCustomer
(
customer_id int,
s_id int,
s_string nvarchar(35)
);

CREATE TABLE tblMainCustomer
(
customer_id int,
customer_nr nvarchar(20),
customer_name nvarchar(40)
);

INSERT INTO tblMainCustomer ( customer_id, customer_nr, customer_name) VALUES
( 45,'P1432','Toyota' ), ( 34,'E4321','Volvo' ), ( 64,'P2342','Honda' ), ( 171,'P8312','Nissan' );


The result in tblCustomer should be like this:
customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
64-------------100-----Gold
64-------------101-----Steel
64-------------102-----Super Copper
171------------100-----Gold
171------------101-----Steel
171------------102-----Super Copper


I have a working solution for SQL server, but I needs a working solution for Sybase now.
insert into tblcustomer
select customer_id, cj.s_Id, cj.s_string
from tblMainCustomer
cross join (
    select * from (values
        (@s_Id_1, @s_string_1),
        (@s_Id_2, @s_string_2),
        (@s_Id_3, @s_string_3)
    ) vals(s_Id, s_string)) cj
where customer_nr like 'P%'

[Former Member · Accepted solution]

Somethink like this should work:

create table #temp ( s_id int, s_string nvarchar(35) );

insert into #temp values (100, 'Gold'); insert into #temp values (101, 'Steel'); insert into #temp values (102, 'Super Copper');

insert into tblcustomer select customer_id, s_Id, s_string from tblMainCustomer, #temp where customer_nr like 'P%';

commit;