CODEWITHSUNDEEP
sql-server
Amir
Amir

Reputation: 2052

SQL Server: Insert into table selecting from another table

I know how to insert by selecting from another table but my problem is little complicated. I have tables like below.

Table 1:

Col1 | Col2 | Col3 | ..... | Col20

Table 2:

Col1 | Col2 | Col3 | ..... | Col20

Both tables are identical. What I want to do is insert all the values from table 1 into table 2, but leaving one column (say Col20). I want to insert that column with my custom value. I can do this by mapping 1 to 1 column like below.

Insert into table1(Col1, Col2, ..., Col20) 
    select Col1, Col2, ..., @customvalue 
    from table2

I would have gone with this way but i have to use this query multiple time inside stored procedure which make that stored procedure lengthy. But I want to shorten this query. Is there any other, better way?

Upvotes: 1

Views: 889

Answers (2)

Karan
Karan

Reputation: 3338

Try if you can utilize the User-defined table types for the second table column set. Define all the required columns from the table2 into this new table type. And then use this table type object to use it in your queries to reduce the code required. You can built out the new UDF for its repeated usage and pass this new type as one of its parameters.

https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

I guess you would be able to reduce some repeated SQL code in your stored procedure. And it would make your code more modular and neat.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82534

...but i have to use this query multiple time inside stored procedure which make that stored procedure lengthy....

The simplest thing would be to create a stored procedure that will only do the insert, and then call it whenever you need to in your main stored procedure:

CREATE PROCEDURE CopyFromTable1ToTable2
(
    @CustomValue int -- or whatever data type you need
)
AS

INSERT INTO Table1 (Col1, Col2... Col120)
SELECT Col1, Col2,.... @CustomValue
FROM Table2

Then, in your main procedure, just do this:

EXEC CopyFromTable1ToTable2 1
...
EXEC CopyFromTable1ToTable2 3

and so on.

Upvotes: 1

Related Questions