Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vinoth.n
Starting Member
3 Posts |
 Posted - 2011-05-26 : 05:22:47
|
| Hi,I need to create a new table with the same structure as of my source table. But, with out any constraints,keys,etc. of the source table.I tried the following,select * into new_t from old_t where 1=2but, this table copies the IDENTITY property of the source table.I don't want this identity property too.any help!!!Best Regards,VN |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-26 : 06:56:32
|
| In SQL Server Management Studio, in object explorer, find the source table, right click,Script Table as -> Create To -> New Query Editor Window. That will script the current table. Change the table name, remove whatever constraints you don't want, and then run the script to create the new table. |
 |
|
|
vinoth.n
Starting Member
3 Posts |
Posted - 2011-05-26 : 11:19:22
|
| @sunitabeck, I need to create the table with sql script. During runtime I need to check if the table exists in the target location if not I have to create it and then load the data.Best Regards,VN |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-26 : 12:37:30
|
| You can incorporate the script that you generate as I described earlier into your script. That assumes, of course, that you know the name of the table.If your goal is to have the query flexible enough that it should do that for a table that is known only at runtime, then I don't have a clean way of doing it. I suppose you could query INFORMATION_SCHEMA.columns based on the table name, find the ordinal positions, data types, widths etc. and construct a dynamic sql statement to create the table. But I would be very uncomfortable doing it that way because of the complexity and room for error.Alternatively, you can create the table with your current approach of "select * into new_t from old_t where 1=2". Then drop and add back the column that has the identity property. When you do it this way, the ordinal position would change unless the column with the identity property happened to be the last column in the source table.Yet another alternative might be to keep the identity column in the new table that you create with your current approach, and then, when you want to insert data into that table, and SET IDENTITY_INSERT to ON on that table, so you can insert the data you want. |
|
|
|
vinoth.n
Starting Member
3 Posts |
Posted - 2011-05-27 : 03:30:00
|
| @sunitabeck,1. I have started to generate the create statement dynamically using the information_schema. Found it little hard and wanna check if any other easy steps are available with the experts.2. with the current approach, I tried to drop and add the column but unfortunately this adds the column at the last which will not help in my case.3. I am also having an eye on using the identity_insert option. since, migrating the data to the target table is not one time job as this will happen every day. So, I need additional checks to see if the target table really has a identity column if yes, then set the property to ON and start pushing the data.Out of options 1 and 3, I would prefer 3 but am half way with the 1st approach and willing to complete it for future use.Thanks for your suggestions.!!! :)Best Regards,VN |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-27 : 08:27:03
|
| You are quite welcome. I just want to note that, in both 2 and 3, you will need to explicitly list the columns that you want to insert. Regardless, intuitively, I would prefer one of those. Also, I think if you set IDENTITY_INSERT to on for a table which does not have an identity column, SQL server would just ignore that. (I am guessing, have not tested that). |
|
|
|
|
|
|
|
|
Create a target table