CODEWITHSUNDEEP
sqlt-sql
S.Minchev
S.Minchev

Reputation: 83

TSQL copy table data to another table with custom values

I have 2 tables with identical columns expect one which is userId. I want to copy all the data from the first table into the second one and add custom userId to the column that is missing. Thus far without success.

The query is shown below. Insert Quality, Quantity, Supplier and UserId (which doesn't exist in the table im getting data)

`DECLARE @UserId int = 5611; -- add this to all the data from dbo.GasStation

INSERT INTO dbo.NewGasStation (Quality, Quantity, Supplier, UserId)
SELECT * FROM dbo.GasStation -- UserId doesn't exist in this table
-- Add somehow @UserId to all the data i'm getting from GasStation in order to satisfy the column UserId in the NewGasStation`

All the data i'm getting from dbo.GasStation to be inserted to dbo.NewGasStation adding this custom UserId to all the results.

Upvotes: 0

Views: 163

Answers (5)

nunoq
nunoq

Reputation: 550

You also don't need to discriminate the columns if you just want to copy them all. Just use the *.

Something like this:

DECLARE @UserId integer = 5611;

INSERT INTO [Target]
SELECT *, @UserId FROM [Source]

Upvotes: 2

RegBes
RegBes

Reputation: 569

Make sure the NewGasStation table does not exsist when running this

Select Quality, Quantity, Supplier,5611 as UserId
into NewGasStation
from GasStation

Upvotes: 0

Marco
Marco

Reputation: 1

INSERT INTO dbo.NewGasStation (Quality, Quantity, Supplier, UserId)
SELECT Quality, Quantity, Supplier, '5611' FROM dbo.GasStation

should do the trick (assuming column names are the same for both tables)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Just add @UserId to the SELECT list:

INSERT INTO dbo.NewGasStation (Quality, Quantity, Supplier, UserId)
    SELECT Quality, Quantity, Supplier, @UserId
    FROM dbo.GasStation ;

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

Instead of * list the columns in your SELECT explicitly and add the variable to that list.

DECLARE @userid integer = 5611;

INSERT INTO dbo.newgasstation
           (quality,
            quantity,
            supplier,
            userid)
           SELECT quality,
                  quantity,
                  supplier,
                  @userid
                  FROM dbo.gasstation;

Upvotes: 1

Related Questions