Menu
In our db there is a table that has a little over 80 columns. It has a primary key and Identity insert is turned on. I'm looking for a way to insert into this table every column EXCEPT the primary key column from an identical table in a different DB.
Is this possible?
229
There are two ways of using INSERT INTO statement for inserting rows: Only values: First method is to specify only the value of data to be inserted without the column names. INSERT INTO table_name VALUES (value1, value2, value3,…); table_name: name of the table.
In order to insert default values to the columns, you just need exclude the default columns from the insert list with a SQL insert into statement.
As long as you have the right number of columns in your INSERT statement, and as long as all the values except KEYBOARD are some numeric data type, and as long as you have suitable permissions, this should work. INSERT INTO INVOICE VALUES( 1,1,'KEYBOARD',1,15,5,75);
It is also possible to only insert data in specific columns.
You can do this quite easily actually:
-- Select everything into temp table Select * Into #tmpBigTable From [YourBigTable] -- Drop the Primary Key Column from the temp table Alter Table #tmpBigTable Drop Column [PrimaryKeyColumn] -- Insert that into your other big table Insert Into [YourOtherBigTable] Select * From #tmpBigTable -- Drop the temp table you created Drop Table #tmpBigTable Provided you have Identity Insert On in "YourOtherBigTable" and columns are absolutely identical you will be okay.
190
You could query Information_Schema to get a list of all the columns and programatically generate the column names for your query. If you're doing this all in t-sql it would be cumbersome, but it could be done. If you're using some other client language, like C# to do the operation, it would be a little less cumbersome.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
